Drupal 6 + MySQL Split Read & Write

By Swampcritter on 12 Aug 2008 at 13:17 UTC

Here's the low-down. We are starting a new project, using Drupal 6 and PHP 5.2.3 as a base. The real requirement for the project is that we need a solution for splitting up MySQL reads and writes for our MySQL master/slave database configuration. We were considering using memcached, but we have heard from our in-house Drupal developers that there is some sort of module or configuration change that needs to be done to allow for Drupal to do split reads and writes for the MySQL database layout.

However, when I have been looking through the Drupal.org site via search, I keep seeing instances of how it was done under Drupal 4.x, 5.x and the upcoming Drupal 7.x, but nothing about it in Drupal 6 if its part of the core, a module, etc. Can anyone point me to where it is documented on how-to implement Drupal 6 + MySQL split read/writes upon a MySQL master/slave database configuration.

These are the articles I have found so far...

Buy more hardware or patch to Master (Write), Slave (Read), Slave (back-up)
http://groups.drupal.org/node/2147

Log of /contributions/sandbox/crackerjm/replication/README.txt
http://cvs.drupal.org/viewvc.py/drupal/contributions/sandbox/crackerjm/r...

Provide support for database replication
http://drupal.org/node/147160

Master/slave replication, load balancing?
http://drupal.org/node/274879

Comments

In the same position

jove4015 commented 1 March 2009 at 10:48

I'm switching things over to Drupal and one definite must is replication support. Right now we do this with ADODB and a customized driver (they're freely available).

One thing I noticed was that prior discussion on read/write splitting seemed to make this false assumption that you need to check for "SELECT" to know if it's a read query - this is tricky because "SELECT" can be included in so many kinds of queries that do write. What you want to do is check for all the other main tokens - UPDATE, REPLACE, INSERT, ALTER, TRUNCATE, DROP, CREATE. It's not a big list and a lot faster than using regular expressions... we also added some names of stored procedures to our list (theoretically one could make this configurable through an interface, to a degree). It works very well for us. I would honestly prefer not to switch then to the code they were suggesting on the patch in the 3rd link there.

The other thing the code needs is a way to force a query to the master. Sometimes, for reasons that are often very arcane, your particular sequence of queries will fail quite bizzarely and you'll realize that you need to check the master to avoid lag in some arbitrary spot. We do it like this

$dbObject->forceMaster();
$dbObject->Execute();

The Last_Insert_ID() function includes this for example. Forcemaster tells the DB object essentially "the very next query you process, send it to the master no matter what". Very handy.

Really, I'd rather get ADODB working with Drupal because of its flexibility (and so much other code I have using it) rather than support MySQL replication natively in Drupal. People seem to be generally against altering the DB layer to do anything useful in high availability situations unless you want to use MySQL Cluster (which... is not an option for so many). I don't get it. But I'm going to work on getting ADODB integrated into version 6 for us, we'll see if what we come up with works well enough to post for others. It can do everything the mysqli driver can do (I'm using that driver, for starters) so it can't be impossible. Can we just add another include like database.adodb.inc?

I know it's 6 months later but it seriously seems like no one's talked about this in 6 months from the perspective of Google. Just fishing around to see if anyone has suggestions.

I also need to know how to do

apersaud commented 20 May 2009 at 20:26

I also need to know how to do this. I found the patch you have on Sourceforge: http://sourceforge.net/projects/msrwd

I applied this patch, but now whenever I try to create new content (e.g. Story) I get this error message "Post could not be saved" and it's not saved. Also, I'm not able to edit any nodes (the changes aren't saved). Any suggestions?

I've also posted this question here: http://drupal.org/node/466212