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

[Roy El-Hames]

Not sure what you mean;I did not need to touch RT::Record .. do you mean 
2 handles same db .. It'll have little or no benefit??
I understand what you mean about the speed of replication, in our case 
its set to near enough real time, however with Create.html a/Update.html 
and also Display.html I kept them reading from RW db in case of any 
delay in replication, so basically anything that require to see the 
changes immediately it will read from RW db.
Roy


Dmitri Tikhonov wrote:
> 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
>>     
>
>
>