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
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
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
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:
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??
No, different databases. Two databases, two handles – one read-write,
the other read-only.
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.
Yes, that’s what I was wondering – would it be OK to read from the
read-only slave right after updating read-write master? If not, what
are the cases, and can we abstract it in such manner that GUI code does
not have to be modified?
Ahh I see what you mean; Did n’t look into that? but possibly a question
here is whats a better approach? in changing from the GUI you get
flexibility deciding which element read from which DB,apart from the
replication issues it may also be extended to a third/forth db instances
where certain Elements may call non RT related tables ??
Roy