How to optimize Drupal database usage

This article discusses how to optimize Drupal database usage and help prevent databases from becoming too large.

Database usage in Drupal

Drupal's caching and logging features can cause the database to grow very large, particularly when a site uses the default configuration settings. When the database becomes too large, site and server performance can be affected.

You can optimize Drupal's database usage and improve site performance by:

• Configuring Drupal's cron routine. Drupal's cron routine checks for system updates, maintains database logging information, and more.
  If you have not already set up Drupal's cron routine, please see this article for information about how to do so.
• Clearing Drupal's caches. You can do this manually, and you can also set up a dedicated cron job so caches are cleared automatically. This article demonstrates how to do this.
• Configuring how much information Drupal's database logging (dblog) module stores in the database. This article explains how to do this.

Clearing the cache manually

Drupal maintains multiple caches, and over time they can grow quite large. When this occurs, your site's performance can decline.

You can manually clear Drupal's caches using the administration interface or the Drush command-line tool. Use whichever method you prefer:

Method #1: Using the Drupal administration interface

• To clear all caches in Drupal 6 using the administration interface, follow these steps:
  1. Log in to Drupal.
  2. In the left pane, click Administer, and then click Site configuration.
  3. Click Performance.
  4. Scroll down the page, and then click Clear cached data.

• To clear all caches in Drupal 7 using the administration interface, follow these steps:
  1. Log in to Drupal.
  2. On the top menu bar, click Configuration.
  3. Under DEVELOPMENT, click Performance.
  4. Click Clear all caches.

Method #2: Using Drush

To clear Drupal's caches from the command line using Drush, follow these steps:

1. Log in to your account using SSH.
2. At the command prompt, change to the directory where Drupal is installed. For example, type cd ~/public_html.
3. Type the following command:

   drush cache-clear all

Configuring a cron job to clear the cache automatically

The previous section explains how to clear Drupal's caches manually. A more long-term solution to keeping the database size manageable, however, is to set up a cron job that clears Drupal's caches at fixed intervals. (This cron job only clears the caches, and is separate from the Drupal cron routine in the cron.php file.)

You can set up this cron job from the command line, or use cPanel. Use whichever method you prefer:

Method #1: Using the command line

To use the command line to set up a cron job that clears the caches, follow these steps:

1. Log in to your account using SSH.
2. At the command prompt, type the following command to access the cron configuration file:

   crontab -e

3. To enter insert mode, type i.

4. Add the following lines to the cron configuration file. You can change the date and time to whatever you want:
   

   # Clear the cache once a week at 2:30 in the morning on Sunday:
   30 02 * * Sun     cd ${HOME}/public_html; drush cache-clear all >/dev/null 2>&1

   • You can modify the cron job intervals to clear the cache more frequently if you want. For example, if you have a busy Drupal site, you may want to clear the caches more than once a week.
   • If you installed Drupal in a subdirectory beneath the public_html directory, modify the cd command to change to that directory instead.

5. To save your changes and exit the editor, press ESC, type :wq and then press Enter. You see the following message:
   

   crontab: installing new crontab

6. To verify the cron configuration, type the following command:
   

   crontab -l

Method #2: Using cPanel

To use cPanel to set up a cron job that clears the caches, all you need to do is specify the interval (for example, weekly, monthly, etc.) and the command to run.

To clear Drupal's caches using Drush, specify the following command:

cd ${HOME}/public_html; drush cache-clear all >/dev/null 2>&1

For detailed information about how to set up a cron job using cPanel, please see this article.

Configuring the database logging (dblog) module

The database logging (dblog) module saves system events in the database. These events include usage and performance data, errors, warnings, and operational information.

You should view the dblog report periodically to make sure your site is running normally. Additionally, you should optimize the dblog module settings to prevent the database from growing too large.

Drupal 6

To view the dblog report on Drupal 6, follow these steps:

1. Log in to Drupal.
2. In the left pane, click Administer, and then click Reports.
3. Click Recent log entries. A list of recent log entries appears.

To configure the row limit for database logging, follow these steps:

1. In the left pane, click Administer, and then click Site configuration.
2. Click Logging and alerts, and then click Database logging.
3. In the list box, select 100. This setting reduces the number of log entries (rows) to maintain, and helps control the database size.
4. Click Save configuration.

Drupal 7

To view the dblog report on Drupal 7, follow these steps:

1. Log in to Drupal.
2. On the top menu bar, click Reports.
3. Click Recent log messages. A list of recent log entries appears.

To configure the row limit for database logging, follow these steps:

1. On the top menu bar, click Configuration.
2. Under DEVELOPMENT, click Logging and errors.
3. Under Database log messages to keep, select 100. This setting reduces the number of log entries (rows) to maintain, and helps control the database size
4. Click Save configuration.