Tuesday January 17, 2006

To scale the SQL query load on a database, it's a common practice to do writes to the master but query replication slaves for reads. If you're not sure what that's about and you have a pressing need to scale your MySQL query load, then stop what you're doing and buy Jeremy Zawodny's book High Performance MySQL.

When the state of the readOnly flag is flipped on the ReplicationConnection, it changes the connection accordingly. It will even load balance across multiple slaves. Where a normal JDBC connection to a MySQL database might look like this

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.
    getConnection("jdbc:mysql://localhost/test", "scott", "tiger");

ReplicationDriver driver = new ReplicationDriver();
Connection conn =
    driver.connect("jdbc:mysql://master,slave1,slave2,slave3/test", props);
conn.setReadOnly(false);
// do stuff on the master
conn.setReadOnly(true);
// now do SELECTs on the slaves

I know of similar efforts in Perl like DBD::Multiplex and Class::DBI::Replication but I haven't had time or opportunity. Brad Fitzpatrick has discussed how LiveJournal handles connection management (there was a slide mentioning this at OSCON last August). LiveJournal definitely takes advantage of using MySQL as a distributed database but I haven't dug into LJ's code looking for it either. In the ebb and flow of my use Perl, it is definitely ebbing these days.

mysql database replication jdbc perl DBI