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");You'd connect with ReplicationDriver this way
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 slavesand 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