I am having trouble getting Drupal 7.7 to use a MySQL slave database. The goal is for Drupal to do all reads from slave, and any changes on master.
My settings.php is as follows:
$databases['default']['default'] = array(
'driver' => 'mysql',
'database' => 'my_db',
'username' => 'dbuser',
'password' => 'dbpw',
'host' => 'db-ip-address'
);
$databases['default']['slave'][] = array(
'driver' => 'mysql',
'database' => 'my_db',
'username' => 'dbuser',
'password' => 'dbpw',
'host' => '127.0.0.1'
);
Replication itself is working great. When I add new content to the site it quickly is replicated on the slave.
Looking at tcpdump though, I never see a call to the slave database.
Is there anything I'm missing to enable Drupal to use the slave?
Comments
You're not missing something.
Nope, there are only very small number of queries that ever go to the slave database. The modules search and aggregator are the ones I've found in core that do. I'm still working to determine why this is and what can be done.
Thanks Elliot! I enabled
Thanks Elliot!
I enabled search as a test and sure enough it hits my slave. It's too bad reads from more core modules aren't taking advantage of the slave.
Alter the db_ functions?
Drupal has an assortment of db_ functions, db_insert, db_query, db_update, etc. What I don't understand, since the type of work is already segmented by SQL query type, why these functions can't be set to use a specific database, regardless of the module that uses them. Is there something I am missing where db_insert() is not going to need write, or db_select() might need it?
Yes, if everything just shunts to db_query(), you're in a bind, but the functions are there and perhaps could be taken advantage of. There's also the possibility of making db_query() a lot smarter about how it calls the database.
Take a look at this to see
Take a look at this to see how to route all queries implemented with SelectQuery to the slave db.