Getting cache tables out of DB (or into different DB)?
Hi All,
What (if any) best practices are there for achieving a configuration where the cache tables aren't writing to the default DB?
My specific concern is related to http://groups.drupal.org/node/12890 (MySQL Binary Logs of Death). Would like to write the cache mutator statements to a different DB so they aren't included in the binary log.
We're running 6.8. Using APC for opcode cache.
It seems like the options are:
- set different DB/tables in place of cache.inc. Specifics?
- use Cache Router. Specifics?
- use Memcache.
I suspect that Memcache is the answer but also requires the most setup. At any rate I appreciate any advice/direction on this topic.
Thanks!
Groups:
Login to post comments
Simple simple
If all you wanna do is put them in a different mysql database, you can use table prefixing for that. E.g.
<?php$db_prefix = array(
'default' => '',
'cache' => 'cachedb.',
'cache_page' => 'cachedb.',
'cache_format' => 'cachedb.',
'cache_views' => 'cachedb.',
);
?>
Thanks to mysql's flexibility here, you can use the cache databasename with a period at the end to tell it to look at that table. Just be sure the same mysql user has access to both databases, and you should be good to go.
Of course, if you want to speed things up as well, memcache or other options are also good, but requires minimal additional work. Just create your cache database, add perms for the drupal mysql user, and you're set. :)
http://www.chapterthree.com | http://www.outlandishjosh.com
In settings.php
I should say, that bit of code goes in your settings.php file. For more on table prefixing, see:
http://drupal.org/node/2622
http://www.chapterthree.com | http://www.outlandishjosh.com
That was simple simple
That was pretty easy to setup, thx!
I assume that if you have this in your settings.php at install time Drupal will create the tables for you.
In my case I just mysqldump -d drupal cache > cache.sql from the existing and mysql cache < cache.sql into cache DB.
I also setup memcache module which wasn't that difficult to do either but entails additional operational concerns that I don't want to be burdened with just yet.
Cheers
Further testing shows this doesn't work
Unfortunately further testing shows that this doesn't work - the statements are still written to the binary log.
Drupal is indeed using the cache DB tables. I truncated previous drupal cache tables and can see that Drupal is indeed using the new DB/tables BUT statements are still being written to the log, e.g.:
UPDATE cache.cache SET data = 'a:328:{s:9:\"anonymous...
[ginormous SQL statement]
I have tried:
binlog-do-db = drupal
and
binlog-do-db = drupal
binlog-ignore-db = cache
and
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = cache
All result in events written to the binary log.
If anyone has been down this path before would love to hear from you!
Thanks!
Cheers
Additional test results
I went one step further and tested with Drupal out of the picture.
I took the MySQL world DB dataset and created a world DB and a planet DB.
my.cnf:
binlog-do-db = world
Made an update to a table in world, statement is written to the binary log.
Made an update to a table in planet, statement is NOT written to the binary log.
So, it is something that the Drupal is doing or I am missing something obvious in the evaluation rules for what does or doesn't get written to the binary log.
BTW, this is mysql-server-5.0.45-7.el5
Cheers
Drupal doesn't have any
Drupal doesn't have any control over what gets written to the binary log (or that would be a major security issue in MySQL). Just out of curiosity, did you restart MySQL after each change of the conf file? I know I have pulled my hair out before because I forgot something so simple.
I would say the simplest solution is going ahead with cacherouter, especially if you got memcache already set up. You can use it just to handle the actual Drupal caching, but not use page fastcache (that is where most people end up having issues) by simply altering your settings.php file but not enabling the actual module. Cacherouter with memcached runs very well. We been running it on a high traffic site for a few months now with no issues.
MySQL restart
Thanks for your post intoxination.
I did indeed restart MySQL after each edit to my.cnf.
I don't know how to explain the results, I just know that this particular avenue doesn't seem to be an option.
I will investigate memcache more fully...
Cheers
Memcache module looks like the way to go
I installed memcached and memcache module and get the desired result.
Installing latest libevent/memcached is non-trivial on CentOS/RHEL but at least I can lessen the bloat in the binary logs and hopefully give the site a speed boost as well.
Cheers