A sysadmin I know would like to direct all reads to the slave DB and all writes to the master DB. I did some research on this last night. Of course, in drupal 7, setting up the config settings for master and slave is easy. But, by default, that only means a handful of reads (in node, stats, search and some other modules) are sent to slave first. hook_query_alter seemed like the most obvious solution; alas, that allows you to alter the query -- but not the connection target. And hacking every SELECT in core, custom, and contrib seems, of course, like a nightmare.
His solution? Go to the source. Hack db_query(). The pseudo-code:
<?php
function db_query($query, array $args = array(), array $options = array()) {
if (QUERY IS SELECT) {
$options['target'] = 'slave';
}
else {
$options['target'] = 'master';
}
return Database::getConnection($options['target'])->query($query, $args, $options);
}
?>Now, I'm OK with sometimes hacking core, when a tiny, inconsequential change allows you to sleep at night. But this seems pretty radical -- and replete with unintended consequences.
Anyone else tried to solve this problem? And/or want to give me a sanity check on hacking db_query()?
Comments
I think this is going to be a
I think this is going to be a bad idea. What if your slave is delayed by a second or two (or heaven forbid a minute or two)? If you send all reads to the slave then if you create new content you won't be able to see it until you refresh the page. This is why only certain queries in core are allowed to go to the slave. There may however be more queries in core that are slave safe.
An area for bigger gains is likely with Views. Chances are many of your Views queries are slave safe (sidebars, landing pages, etc.). These are also going to be some of the heftier and more common queries.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
I agree with Dalin, not a good idea
I second Dalin's opinion, this will be problematic and cause weird, hard to debug problems. Things like cache rebuilding stampedes, when something is inserted on the master but many other server threads don't see it in a read on the slave and try to insert it again.
However, the basic instinct is correct. The vast majority of select queries do not go to the slave but should. It is just hard to automatically identify the ones that are slave-safe and the ones that are not, you have to actually think about the logic that is happening.
Instead, first make sure that all your views that can be, are sent to the slave. On Drupal 7 with views 3 there is an option for this, and here is a patch that makes that option available for views 2 and Drupal 6 (pressflow) : https://drupal.org/node/900860
However, I think you are likely to find that all the really tough queries are well-protected by caching layers, and some more agressive approach might be needed.
One thing I started looking at, but haven't written any code on, is making the devel query logger indicate which queries went to the Master and which the Slave, so a developer could optimize code more easily.
The devel query log already
The devel query log already reports the target for each query (master or slave or whatever). See the right most column.
There is a way to alter
There is a way to alter connection without hacking core. See http://drupal.org/node/802514
Thanks!
Thanks, Moshe. This is definitely helpful. There's obviously lots of angles here -- but if we do go ahead with this, it's good to know that core doesn't need to be hacked.
Read-write splitting
Is it really a best practice to send all reads to the slave, even if it didn't have replication lag?
If the server has not many writes, then the master will be mostly idle. Sending all the reads to the slave won't be much different, performance-wise, than using only one db. It will just be more complicated and it introduces the replication lag, which can have both obvious and subtle consequences.
If the server has lots of writes, then yes, the master will be busy, but SO WILL THE SLAVE - it has to write everything the master does anyway and will be under similar load as the master. (Replication is not "free", it's just redoing the writes on a different db.) So sending all the reads to the slave is, again, not much different from running with a single db.
If you're going to use master-slave replication, for performance it's best to either randomly split your reads evenly between master and slave, or to more selectively send up to half consistently to the slave, focusing on ones less sensitive to lag.
(Before even going the replication route, I'd take every other possible step - most of which are frequently discussed around here - to both speed up the db and to reduce the number of queries that hit it in the first place, then see if replication is still really necessary.)
Assuming you've done all the
Assuming you've done all the work to move cache, session tables into memcached or similar and out of the database then this isn't a horrible requirement. You don't have to hack core if you simply copy the inc file to a new name and reference that in your settings.php as the database type but essentially you still create an update task to check whether it still works correctly.
In Pressflow 6 there is an optional module that on a form post a user session is marked as having to work on the master database which removes some of the problems with reading from slaves during the lag period. This would be a sensible approach. It does not remove all of the problems. One possible example is a form that gets posted that causes an alteration to the menu tables. When the next page request comes in it is from another user so the session isn't marked to be read from the server so you could build some sort of inconsistent state.
Is galera an option for you?
Full Fat Things ( http://fullfatthings.com ), my Drupal consultancy that makes sites fast.
Reference
Might want to check out the Pressflow issue here:
https://bugs.launchpad.net/pressflow/+bug/627247
Some good discussion and links, both for Pressflow and another fork of it.