This short article is essential reading for sites using MySQL replication. It covers the background problem, potential solutions, and risks of higher slave utilization
- Underused MySQL slave databases are a known Drupal 7 issue, for background see:
- http://drupal.org/node/1253352
- http://groups.drupal.org/node/27820
- http://drupal.org/node/310072 (Query Options Section)
- http://drupal.org/node/802514#comment-4906402 (extension to force SelectQuery's to use a slave)
- The major takeaways in these threads are:
- slave usage is barely perceptible
- slaves are only used when contrib and core have explicitly set an option in the db_query function
- there is a partial solution that can force a minority of queries to use a slave
- this is a design choice because slave database usage affects a minority of Drupal sites and a perfect solution is complicated and will incur an unnecessary performance overhead on every query (eg, regex on "^SELECT")
All of that is to say, if you have a highly trafficked site, MySQL replication isn't doing nearly as much as it could for you.
Fortunately, there's an amazing little module that solves everything for you with a few more lines in your settings.php. Autoslave defines a new database connection type (driver) and takes the stance that a regex and read-only slave DBs may not be the perfect solution, but they are a pragmatic one*.
The project page has sample config and the maintainer is VERY responsive and helpful. As far as configuration, there are only a couple of points worth noting: 1, you need a workaround for drush and 2, you need a workaround for update.php.
As far as risks go, one of my clients' websites wouldn't run without autoslave — the authenticated traffic load was crushing the master database. It should also be noted that there is no risk of data corruption, if your slaves are read-only; you could lose a write, but this would also trigger and log an error. Besides, a bit of testing is normal due diligence and should give you plenty of peace of mind.
* Also worth noting, this approach of inspecting the query is exactly the same used by the PHP PECL mysqlnd extension — http://www.php.net/manual/en/mysqlnd-ms.architecture.php.
