I've noticed over the past few weeks that a Drupal installation that I help maintain has started to grow gigantic. The main culprit is the cache table - it's currently increased an ungodly 5.6GB, adding roughly 500-700MB per day! The table has just about 3,000,000 rows and doesn't seem to be shrinking at all. Given what I can gather from mysql, it appears as though things should have an expiration, but nothing does.
[root@jim]# ls -lh | grep G
total 10G
-rw-rw---- 1 mysql mysql 1.2G Aug 12 10:45 accesslog.MYD
-rw-rw---- 1 mysql mysql 5.6G Aug 12 11:10 cache.MYD
[root@jim]# mysql -u root -p
mysql> describe cache;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| cid | varchar(255) | NO | PRI | | |
| data | longblob | YES | | NULL | |
| expire | int(11) | NO | MUL | 0 | |
| created | int(11) | NO | | 0 | |
| headers | text | YES | | NULL | |
| serialized | smallint(6) | NO | | 0 | |
+------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> select count(*) from cache;
+----------+
| count(*) |
+----------+
| 2966905 |
+----------+
1 row in set (0.00 sec)
mysql> select * from cache limit 1;
+-------------------------+--------+--------+------------+---------+------------+
| cid | data | expire | created | headers | serialized |
+-------------------------+--------+--------+------------+---------+------------+
| mollom:delete_form_list | a:0:{} | 0 | 1279298445 | | 1 |
+-------------------------+--------+--------+------------+---------+------------+
1 row in set (0.02 sec)
mysql> select count(*) from cache where expire > 0;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from cache where expire = 0;
+----------+
| count(*) |
+----------+
| 2967682 |
+----------+
1 row in set (2.24 sec)
The size is becoming unmanageable and also makes backing up the database much harder (mysqlhotcopy takes much, much longer than it used to - which makes sense when it's locking and then copying 10GB worth of data compared to 3-4GB).
What do I need to do/look at to correct the constant growing of the database in such an extreme manner?
Comments
=-=
cache table can be emptied at any time.
cache table doesn't have to be backed up
cache table holds cache, which increases speed for anon users
page cache can be disabled in administer -> performance
Well I don't want to disable
Well I don't want to disable the cache completely, because we definitely need the performance boost. However the cache itself doesn't appear to be emptying, ever. So I just don't want to disable the cache, I want to set a maximum time (which would then theoretically fill in the expires column with a future epoch time and delete entries as necessary).
And by backing up the database via mysqlhotcopy, you back up the entire thing (and don't choose which tables you wish to have backed up - so the cache table does in fact have to be backed up)
Backup and Migrate
The entire database doesn't "have to" get backed up. Use the backup and migrate module. The default settings will backup just what you need.
A list of some of the Drupal sites I have designed and/or developed can be viewed at motioncity.com
Using a built in module to
Using a built in module to backup your data is not an efficient way to backup large databases. It locks tables for extended periods of time that render the server unusable while backing up data (While the cache table is 6GB or so, the entire database is roughly 12GB - so it's not a small database by any means). mysqlhotcopy is easily much, much more efficient and cleaner at backing up a large database (and as such needs to backup the entire database and not just individual tables).
With mysqlhotcopy, I have a live copy of the entire database that I can plop back on any server (along with my mirror images of the web directories) and restore it like nothing ever happened. No need to reinstall *anything*.
The bigger issue that's being glanced over is that the cache table is not setting an expire timeout causing the table to grow indefinitely. And the solution to this isn't just dumping the cache table whenever. The solution is using the "expire" column of the table to expire the mysql cache. So... where would I modify that?
=-=
"mysqlhotcopy"
never heard of it. When I back up I choose which tables to backup and export the tables needed using PHP myadmin.
administer -> performance allows on to set a min and max cache lifetime that is removed during cron runs I think.