Speed up a Drupal web site by enabling MySQL query caching

Technologies: Drupal 5+, MySQL 5+

Speed up page load times by saving the results of frequent database queries in MySQL’s query cache. Cached queries are handled quicker, enabling Drupal to build pages faster for site visitors.

This article is part of the Essential steps to speed up a Drupal web site article series.

How to enable MySQL query caching

MySQL’s query cache is disabled by default. Enable it to speed up page load times by 5-15%. Use the MySQL Administrator application, available as a free download from MySQL.org.

  1. Start MySQL Administrator.
  2. Log in as your database server’s administrator (the database root account).
  3. Windows:
    1. Enable the cache:
      1. Click on Startup Variables in the left-hand list.
      2. Select the Performance tab at the top of the window.
      3. Check the Cache Size checkbox to enable the setting.
      4. Enter a cache size value, such as 32 Mbytes.
      5. Click the Apply changes button to save the settings.
    2. Restart the database server:
      1. Click on Service Control in the left-hand list.
      2. Click the Stop Service button to stop the server.
      3. Click the Start Service button to start the server again.
  4. Mac:
    1. Enable the cache:
      1. Click on the Options icon at the top of the window.
      2. Select the Performance section from the pull-down menu at the top of the window.
      3. Click on the pencil icon beside Cache Size to enable the setting.
      4. Enter a cache size value, such as 32 Mbytes.
      5. Click the Save button to save the settings.
    2. Restart the database server:
      1. Click the Service button at the top of the window.
      2. Click the Stop server button to stop the server.
      3. Click the Start server button to start the server again.

The Appendix at the end of this article shows an alternate way of enabling query caching by editing MySQL’s configuration file.

What does it do?

Drupal uses a MySQL database to store almost everything about a web site. There are database tables listing users, nodes (pages, stories, blog posts, etc.), comments, taxonomy terms, module settings, theme settings, and more. To build a page, Drupal issues queries to the database to get this information, which Drupal then formats and returns to the site visitor. A simple site may require 40-50 queries to build a page. A complex site may require many more. You can see a list of queries involved in building a page by installing and enabling the Devel (developer) module for Drupal.

Every query takes time to process. Much of that time is spent with MySQL scanning through its tables of data to find the information Drupal has asked for. A query cache saves the results of a query so that the next time Drupal asks for the same information, MySQL can return the saved results instead of scanning through its tables again. This saves time, speeding up the site.

The query cache is empty when it is first enabled. As Drupal issues queries to the database, MySQL finds the requested data and saves it in the cache. After awhile, much of the data Drupal needs from page to page is quickly found in the cache.

MySQL automatically updates cached query results if the site’s content changes, such as when nodes, comments, or forum posts are added or deleted.

The query cache can be any size. 32 Mbytes is a starting point. A bigger cache saves more queries, but requires more memory on the server computer. If the server has a lot of memory, the site is big, or the server is serving lots of sites, try making the query cache bigger.

You can use MySQL Administrator to see how well the query cache is being used:

  1. Start MySQL Administrator.
  2. Log in as your database server’s administrator (the database root account).
  3. Click on Health (left-hand list in Windows, and at the top on a Mac).
  4. Select Status Variables at the top of the window.
  5. Select Query cache in the left-hand list. This presents a list of useful status variables in the center of the window.

Let the site run for awhile then look at the query cache status variables. The Qcache_free_memory variable shows the amount of cache memory not in use. If this is low, make the cache bigger. If it is high, make the cache smaller.

The other status variables are also useful. Qcache_queries_in_cache shows the number of saved queries, and Qcache_lowmem_prunes the number of saved queries that had to be deleted to make room for new saved queries.

How well does it work?

I benchmarked the effect of MySQL query caching on two representative test sites: a simple Drupal site with just the basic modules, and a complex site with lots of modules (see my Specifications for Drupal web site testing). Both sites have no other performance improvements (e.g., no file compression, no PHP script cache, and no Drupal page cache or CSS file aggregation). Load times are for each site’s home page, including HTML, CSS, JavaScript, and images. Tests simulated a web page uploaded over a 64Kbps cable modem, such as that used to serve small web sites from a home or small business.

With MySQL query caching, page load times speed up by 5-15%.


Total page load time

Both test sites include a list of recent posts on their home pages. The complex site adds blocks to show recent blog, forum, and comment posts, who’s logged in, popular taxonomy terms, and more. This added complexity requires many more database queries to get the page content. The query cache speeds up these additional queries, giving a more significant improvement for the complex site than for the simple site with its smaller number of queries.

When doesn’t it work?

MySQL query caching works well. Once enabled, look at it every once in awhile to be sure the cache is big enough.

Conclusions

MySQL’s query cache improves performance. The effect is more dramatic for complex web sites that use a lot of blocks, views, and other database query-intensive page elements. Enabling the query cache is a useful step in improving a Drupal web site’s performance.

Further reading

Appendix: Enable query caching by editing MySQL’s configuration file

The MySQL Administrator application changes a configuration file that you can edit directly using a text editor if you prefer.

  1. Windows:
    1. Start NotePad and edit “C:\Program Files\MySQL\MySQL Server 5.0\my.ini”.
    2. Add a line “query_cache_size=32M”.
    3. Save the file.
    4. Restart the database server. Use the MySQL System Tray Monitor:
      1. Select Shutdown Instance from the tray icon to stop the server.
      2. Select Start Instance from the tray icon to start the server again.
  2. Mac:
    1. Start TextEdit and edit “/etc/my.cnf”. Create the file if it doesn’t exist.
    2. Add a line “query_cache_size=32M”.
    3. Save the file.
    4. Restart the database server. Use the MySQL Preferences pane:
      1. Select System Preferences... from the apple menu.
      2. Select the MySQL preferences in the Other section.
      3. Click the Stop MySQL Server button to stop the server. Authorize the action by entering your account password.
      4. Click the Start MySQL Server button to start the server again.

There are also command-line programs for starting and stopping the server on Windows PCs and Macs, if you prefer. Look at mysqld, mysql_safe, and mysqladmin.

Comments

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically.

More information about formatting options

Nadeau software consulting
Nadeau software consulting