[rt-users] Read only db for select (cluster on the cheap)

[Dmitri Tikhonov]

This is an interesting approach.  I was wondering if it would be
possible to do something like that without having to change higher-level
code.

For example (assuming we have two DB handles), all Set* operations would
use RW handle, while all Load* operations would use RO handle.  I
haven't investigated this yet, but if we are lucky, all these changes
may be inside RT::Record (or directly in DBIx::SearchBuilder::Record).

Of course, there's also the question of how fast replication actually
happens...

Thoughts?

  - Dmitri.

On Thu, 2006-10-26 at 16:45 +0100, Roy El-Hames wrote:
> Hi;
> RT 3.6.1 and and a pair of mysql 5 servers
> I 've setup my system so it talks to a replicated db pair, where the 
> slave for read only operations, now most of my select actions 
> (Mytickets, MyRequests, Results.html) uses the slave server , so normal 
> ticketing operations are not effected by long searches, I am putting the 
> changes below to you smart people to see if anyone can spot something I 
> missed that may cause problems down the line , I would appreciate any ideas.
> I have made the following changes:
> in RT_SiteConfig added the following variables:
> 
> # {{{ Read Only Database Configuration
> Set($RO_DatabaseHost   , 'slave db');
> #The name of the database user (inside the database)
> Set($RO_DatabaseUser , 'rt user');
> # Password the DatabaseUser should use to access the database
> Set($RO_DatabasePassword , 'rt password');
> # The name of the RT's database on your database server
> Set($RO_DatabaseName , 'rt3');
> 
> Changed in SearchBuilder.pm, the _Init function to :
> # {{{ sub _Init
> sub _Init  {
>     my $self = shift;
>     $RT::Logger->err("Getting to SearchBuilder.pm");
>     $self->{'user'} = shift;
>     my $ro_db = shift;
>     unless(defined($self->CurrentUser)) {
>         use Carp;
>         Carp::confess("$self was created without a CurrentUser");
>         $RT::Logger->err("$self was created without a CurrentUser");
>         return(0);
>     }
> #CHANGE => USED FOR 2nd DB FOR SEARCH ELEMENTS
>     if ($ro_db) {
>          $RT::Logger->err("Getting to SearchBuilder.pm and highlted ro_db");
>          my $handle = DBIx::SearchBuilder::Handle->new();
>          $handle->Connect( 'Driver'   => "mysql",
>                 'Database' => $RT::RO_DatabaseName,
>                 'Host'     => $RT::RO_DatabaseHost,
>                 'User'     => $RT::RO_DatabaseUser,
>                 'Password' => $RT::RO_DatabasePassword);
>         $self->SUPER::_Init( 'Handle' => $handle);
>         # Setup al;ternate DB handle in config and use tha tisntead
>      } else {
>         $self->SUPER::_Init( 'Handle' => $RT::Handle);
>      }
> ## END CHANGE
> }
> # }}}
> 
> And applied changes to where ever there are read only operations (ie 
> MyTickets as example ) changing :
> $MyTickets = new RT::Tickets ($session{'CurrentUser'});
> to:
> $MyTickets = RT::Tickets->new($session{'CurrentUser'},"+ro");
> 
> Roy
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com