How to keep the size of the database reduced?

Question

I found out a very simple D7 site with less than 150 nodes had database dumps of 65M (gzipped). Which caused my entire server to become slow during the nightly database dumps.

After some searching I found this useful query:

SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "DRUPAL_DATABASENAME_GOES_HERE";

With that I immediately found the culprit: the sessions table.

After a TRUNCATE sessions my dump is now <1M (gzipped).

How can I make sure this site's dump doesn't blow up again in the future? I found periodic purging of watchdog and sessions tables? on drupal.org, but it's from 2007.

Answer 1

Try the modules

• Optimize Database, and
• Database maintenance

The differences are also explained in the this fixed issue Compare with DB Maintenance in the Optimize Database issue tracker.

The first can also run cron (so, optimize at midnight).

Answer 2

Don't dump session, cache_*, watchdog table content (make sure to keep the structure).

For watchdog there's syslog replacement.

If you don't need the access logging in Drupal disable that module and dump that table as well.

For huge content driven websites with lots of edits (and revisions) clearing the revision tables from time to time might help too. (e.g. with node revision delete).

Answer 3

If you find you have too many rows in the sessions table, you can adjust your PHP session settings in settings.php

/**
 * Set session lifetime (in seconds), i.e. the time from the user's last visit
 * to the active session may be deleted by the session garbage collector. When
 * a session is deleted, authenticated users are logged out, and the contents
 * of the user's $_SESSION variable is discarded.
 */
ini_set('session.gc_maxlifetime', 200000);

/**
 * Set session cookie lifetime (in seconds), i.e. the time from the session is
 * created to the cookie expires, i.e. when the browser is expected to discard
 * the cookie. The value 0 means "until the browser is closed".
 */
ini_set('session.cookie_lifetime', 2000000);

For example, you could reduce session.gc_maxlifetime and session.cookie_lifetime to purge the sessions table more frequently.

More info on session configuration:

PHP: Runtime Configuration: Session

Although not directly related to the large sessions table, using an alternative cache handler, such as Memcache can help reduce DB size by moving cache tables into Memcache instead.

Answer 4

Apart from some of the tables mentioned in some other answers already, there are plenty of other tables in Drupal 7 that you can either clear (to reduce the database size) or safely exclude when performing backups. Such as various search*-, devel*-, ctools*- or flood tables.

For a pretty long list (inventory) of those tables, have a look at my answer to "Which tables are safe to clear?"

Answer 5

Try redis

This will allow you to completely remove the cache tables and speed up the site in the process