What's That Noise?! [Ian Kallen's Weblog]

Main | Next day (Jan 18, 2006) »

20060117 Tuesday January 17, 2006

Transparent Division of JDBC Reads and Writes in MySQL

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.

If you've used MySQL replication and written application code that dispatches INSERTS, UPDATES and DELETES to the master while sending SELECTS to the slaves (exception for transactional operations where those have to go to the master), you know how it can add another wrinkle of complexity. Well, apparently there's been a little help in the MySQL JDBC driver for a while and I'm just learning of it now. The ReplicationConnection class in the MySQL Connector/J jar (as of v3.1.7) provides the dispatching pretty transparently.

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

Connection conn = DriverManager.
    getConnection("jdbc:mysql://localhost/test", "scott", "tiger");
You'd connect with ReplicationDriver this way
ReplicationDriver driver = new ReplicationDriver();
Connection conn =
    driver.connect("jdbc:mysql://master,slave1,slave2,slave3/test", props);
// do stuff on the master
// now do SELECTs on the slaves
and ReplicationDriver handles all of the magic of dispatching. The full deal is in the Connector/J docs, I was just pleased to finally find it!

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.

( Jan 17 2006, 11:42:09 PM PST ) Permalink