Splitting queries across mysql servers

Hi,
(Now subscribed from my personal address)

We’re looking to scale our RT instance at $work, and make it more
fault tolerant at the same time. The obvious first step is to split
the DB out from the Mason servers (currently all on one box). At the
same time, we want to use mysql replication to get the data onto a
second DB server. In a perfect world we would have RT transparently be
able to do SELECTs on both DB boxes while sending UPDATE/INSERT
statements to the master DB.

I know I’m not the first person to attempt this. :slight_smile: Any pointers to
DBI multiplexers, tips, etc would be most appreciated.

Thanks,
Drew
Drew Taylor * Web development & consulting
Email: drew@drewtaylor.com * Site implementation & hosting
Web : www.drewtaylor.com * perl/mod_perl/DBI/mysql/postgres

I’m using circular replication in MySQL and LOVE it!
The idea is that each MySQL host points at the other as a slave, so all
updates go to both hosts. The problem with that in the past is that
tables using auto-increment columns could get whacked if both servers
added a row at the exact same time and contended for that next
incremented value.
MySQL now has values you can set in my.cnf that tell one server to use
odd numbers (e.g.) and the other evens.
Works like a charm, though there is no automatic failover. I run the DB
attached to the base IP for the server, as well as a virtual IP that can
swing over (by hand) to the other. Lastly, they share a crossover cable
(direct cable) interface to do the replication over GigE.
You can check out the Heartbeat project for IP failover, but I’ve not
had time to dig deep into that yet (one dragon to slay at a time,
please!).
HTH

Hi,
(Now subscribed from my personal address)

We’re looking to scale our RT instance at $work, and make it more fault
tolerant at the same time. The obvious first step is to split the DB out
from the Mason servers (currently all on one box). At the same time, we
want to use mysql replication to get the data onto a second DB server.
In a perfect world we would have RT transparently be able to do SELECTs
on both DB boxes while sending UPDATE/INSERT statements to the master
DB.

I know I’m not the first person to attempt this. :slight_smile: Any pointers to DBI
multiplexers, tips, etc would be most appreciated.

Thanks,
Drew
Drew Taylor * Web development & consulting
Email: drew@drewtaylor.com * Site implementation & hosting
Web : www.drewtaylor.com * perl/mod_perl/DBI/mysql/postgres
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
IMPORTANT: The contents are intended for the named recipient(s) only and may contain information that is
PRIVILEGED and/or CONFIDENTIAL.
If you have received this email in error, please destroy all copies of this message and it’s attachments and
then notify the system manager or the sender immediately. Do not disclose the contents to anyone or make
copies thereof.
*** eSafe scanned this email for viruses, vandals, and malicious content. ***

This sounds good
How many web interfaces (rt instances) are you running, how did you set
up $DatabaseHost in the RT config??
Roy

Parish, Brent wrote:

I’m running one instance, which is on a Sun v240 (4gb RAM, 2x1GHz CPU),
which also runs the database. The second server is a complete mirror of
the first, both in hardware and software.
$DatabaseHost is set to the MySQL virtual IP that I would swing back and
forth between servers as needed. The RT web interface (apache) is also
tied to a virtual IP, so that would swing over as well.
In the my.cnf, set the replicion values to something like this:
auto_increment_increment = 2
“auto_increment_offset = 2” on one server, and “= 1” on the other (to
make one auto increment using even numbers, the other odds)
master-host = (set this to the IP of the private interface/crossover
cable to the second server, so it uses the gigE, not the default network
interface)

BrentFrom: Roy El-Hames [mailto:rfh@pipex.net]
Sent: Thursday, September 07, 2006 5:03 AM
To: Parish, Brent
Cc: Drew Taylor; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Splitting queries across mysql servers

This sounds good
How many web interfaces (rt instances) are you running, how did you set
up $DatabaseHost in the RT config??
Roy

Parish, Brent wrote:

I’m using circular replication in MySQL and LOVE it!
The idea is that each MySQL host points at the other as a slave, so
all
updates go to both hosts. The problem with that in the past is that
tables using auto-increment columns could get whacked if both servers
added a row at the exact same time and contended for that next
incremented value.
MySQL now has values you can set in my.cnf that tell one server to use
odd numbers (e.g.) and the other evens.
Works like a charm, though there is no automatic failover. I run the
DB
attached to the base IP for the server, as well as a virtual IP that
can
swing over (by hand) to the other. Lastly, they share a crossover
cable
(direct cable) interface to do the replication over GigE.
You can check out the Heartbeat project for IP failover, but I’ve not
had time to dig deep into that yet (one dragon to slay at a time,
please!).
HTH

  • Brent Parish

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Drew
Taylor
Sent: Tuesday, September 05, 2006 5:24 PM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] Splitting queries across mysql servers

Hi,
(Now subscribed from my personal address)

We’re looking to scale our RT instance at $work, and make it more
fault
tolerant at the same time. The obvious first step is to split the DB
out
from the Mason servers (currently all on one box). At the same time,
we
want to use mysql replication to get the data onto a second DB server.
In a perfect world we would have RT transparently be able to do
SELECTs
on both DB boxes while sending UPDATE/INSERT statements to the master
DB.

I know I’m not the first person to attempt this. :slight_smile: Any pointers to
DBI
multiplexers, tips, etc would be most appreciated.

Thanks,
Drew


Drew Taylor * Web development & consulting
Email: drew@drewtaylor.com * Site implementation & hosting
Web : www.drewtaylor.com * perl/mod_perl/DBI/mysql/postgres


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

IMPORTANT: The contents are intended for the named recipient(s) only
and may contain information that is
PRIVILEGED and/or CONFIDENTIAL.
If you have received this email in error, please destroy all copies of
this message and it’s attachments and
then notify the system manager or the sender immediately. Do not
disclose the contents to anyone or make
copies thereof.
*** eSafe scanned this email for viruses, vandals, and malicious
content. ***


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

IMPORTANT: The contents are intended for the named recipient(s) only and may contain information that is
PRIVILEGED and/or CONFIDENTIAL.
If you have received this email in error, please destroy all copies of this message and it’s attachments and
then notify the system manager or the sender immediately. Do not disclose the contents to anyone or make
copies thereof.
*** eSafe scanned this email for viruses, vandals, and malicious content. ***

Whoops. Forgot to mention:
In the past, I’ve set up three on the web interfaces and put them behind
an Alteon VIP for load balancing. I had to edit
share/html/Elements/SetupSessionCookie:

diff SetupSessionCookie SetupSessionCookie.orig
71,78c71
< if ( !$cookies{RTSESSION} ) {
< my $sessioncookie = new CGI::Cookie(
< -name => ‘RTSESSION’,
< -value => ‘0A20115B’,
< -path => ‘/’
< );
< $r->headers_out->add(‘Set-Cookie’, $sessioncookie->as_string);
< } elsif ( !$cookies{$cookiename} ) {

if ( !$cookies{$cookiename} ) {
82c75
< -path => ‘/’
-path => ‘/’,
84c77
< $r->headers_out->add(‘Set-Cookie’, $cookie->as_string);
$r->header_out->(‘Set-Cookie’, $cookie);

Bear in mind this was on 3.4.2 and SetupSessionCookie has changed since
then. The value I set into the cookie was a hex representation of the
IP of that web server, and was therefore unique on each web host. The
alteon was set to check this cookie value and keep the session
persistent, to avoid potential issues with a user session being stored
on one server and they get load balanced onto another one. Not even
sure if RT works like that, but it was easy enough to set up as a “just
in case”.

This sounds good
How many web interfaces (rt instances) are you running, how did you set
up $DatabaseHost in the RT config??
Roy

Parish, Brent wrote:

I’m using circular replication in MySQL and LOVE it!
The idea is that each MySQL host points at the other as a slave, so
all
updates go to both hosts. The problem with that in the past is that
tables using auto-increment columns could get whacked if both servers
added a row at the exact same time and contended for that next
incremented value.
MySQL now has values you can set in my.cnf that tell one server to use
odd numbers (e.g.) and the other evens.
Works like a charm, though there is no automatic failover. I run the
DB
attached to the base IP for the server, as well as a virtual IP that
can
swing over (by hand) to the other. Lastly, they share a crossover
cable
(direct cable) interface to do the replication over GigE.
You can check out the Heartbeat project for IP failover, but I’ve not
had time to dig deep into that yet (one dragon to slay at a time,
please!).
HTH

  • Brent Parish

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Drew
Taylor
Sent: Tuesday, September 05, 2006 5:24 PM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] Splitting queries across mysql servers

Hi,
(Now subscribed from my personal address)

We’re looking to scale our RT instance at $work, and make it more
fault
tolerant at the same time. The obvious first step is to split the DB
out
from the Mason servers (currently all on one box). At the same time,
we
want to use mysql replication to get the data onto a second DB server.
In a perfect world we would have RT transparently be able to do
SELECTs
on both DB boxes while sending UPDATE/INSERT statements to the master
DB.

I know I’m not the first person to attempt this. :slight_smile: Any pointers to
DBI
multiplexers, tips, etc would be most appreciated.

Thanks,
Drew


Drew Taylor * Web development & consulting
Email: drew@drewtaylor.com * Site implementation & hosting
Web : www.drewtaylor.com * perl/mod_perl/DBI/mysql/postgres


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

IMPORTANT: The contents are intended for the named recipient(s) only
and may contain information that is
PRIVILEGED and/or CONFIDENTIAL.
If you have received this email in error, please destroy all copies of
this message and it’s attachments and
then notify the system manager or the sender immediately. Do not
disclose the contents to anyone or make
copies thereof.
*** eSafe scanned this email for viruses, vandals, and malicious
content. ***


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

IMPORTANT: The contents are intended for the named recipient(s) only and may contain information that is
PRIVILEGED and/or CONFIDENTIAL.
If you have received this email in error, please destroy all copies of this message and it’s attachments and
then notify the system manager or the sender immediately. Do not disclose the contents to anyone or make
copies thereof.
*** eSafe scanned this email for viruses, vandals, and malicious content. ***