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.
Table of Contents
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.
- Start MySQL Administrator.
- Log in as your database server’s administrator (the database root account).
- Windows:
- Enable the cache:
- Click on Startup Variables in the left-hand list.
- Select the Performance tab at the top of the window.
- Check the Cache Size checkbox to enable the setting.
- Enter a cache size value, such as 32 Mbytes.
- Click the Apply changes button to save the settings.
- Restart the database server:
- Click on Service Control in the left-hand list.
- Click the Stop Service button to stop the server.
- Click the Start Service button to start the server again.
- Enable the cache:
- Mac:
- Enable the cache:
- Click on the Options icon at the top of the window.
- Select the Performance section from the pull-down menu at the top of the window.
- Click on the pencil icon beside Cache Size to enable the setting.
- Enter a cache size value, such as 32 Mbytes.
- Click the Save button to save the settings.
- Restart the database server:
- Click the Service button at the top of the window.
- Click the Stop server button to stop the server.
- Click the Start server button to start the server again.
- Enable the cache:
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:
- Start MySQL Administrator.
- Log in as your database server’s administrator (the database root account).
- Click on Health (left-hand list in Windows, and at the top on a Mac).
- Select Status Variables at the top of the window.
- 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%.
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
- Essential steps to speed up a Drupal web site.
- Enabling a MySQL query cache is one of several important steps to speed up a web site. Other steps include enabling Apache file compression, using a PHP script cache, and enabling Drupal’s page cache and CSS file aggregation.
- A Practical Look at the MySQL Query Cache.
The MySQL Query Cache.- MySQL’s Developer Zone has in-depth articles on the query cache (and a lot more).
- What to tune in MySQL Server after installation.
- Peter Zaitsev's article lists several of the most important MySQL parameters to tune. The rest of his site includes an active blog with MySQL performance tips, and a list of presentation slides for talks he’s given on MySQL performance tuning.
- Peter Zaitsev's article lists several of the most important MySQL parameters to tune. The rest of his site includes an active blog with MySQL performance tips, and a list of presentation slides for talks he’s given on MySQL performance tuning.
- Tuning MySQL for Drupal.
Tools, tips, and links on optimizing mysql.- While the query cache is one of the most important MySQL features to enable, there are a lot more tunable parameters. These articles provide brief advice on which parameters to set, and to what values for a Drupal site.
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.
- Windows:
- Start NotePad and edit “C:\Program Files\MySQL\MySQL Server 5.0\my.ini”.
- Add a line “query_cache_size=32M”.
- Save the file.
- Restart the database server. Use the MySQL System Tray Monitor:
- Select Shutdown Instance from the tray icon to stop the server.
- Select Start Instance from the tray icon to start the server again.
- Mac:
- Start TextEdit and edit “/etc/my.cnf”. Create the file if it doesn’t exist.
- Add a line “query_cache_size=32M”.
- Save the file.
- Restart the database server. Use the MySQL Preferences pane:
- Select System Preferences... from the apple menu.
- Select the MySQL preferences in the Other section.
- Click the Stop MySQL Server button to stop the server. Authorize the action by entering your account password.
- 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.
Post new comment