Essential performance tuning for Drupal web sites

Technologies: Drupal 5+, PHP 4+, MySQL (optional), Apache (optional)

Three changes to the PHP, MySQL, and Drupal default configurations improves Drupal web site performance considerably: (1) install a PHP script cache, (2) turn on MySQL's query cache, and (3) enable Drupal's page cache.

Introduction

The Drupal content management system is a good way to set up a multi-featured web site quickly. To get Drupal running you'll need an Apache web server, the PHP "engine" for running PHP programs, and the MySQL database. These are all free and easily installed with a few mouse clicks. But once you've got them running and you load your first Drupal Web page, it's slooooow. This is easily fixed by making three key changes to your software configuration...

Step 1. Install a PHP script cache

PHP is a programming language for building web pages, and Drupal is written using it. To run a PHP program, the PHP engine converts the programmer's instructions into simpler instructions that the computer can understand. For a program the size of Drupal, this conversion process, or compilation, can take a few seconds. When the compilation is done, the engine runs the Drupal program to generate a web page, taking another few seconds.  Depending upon the web site's complexity, the total time can be 7 or 8 seconds.  That's way too long.  Usability studies find that page load times need to be under one second for visitors to feel that a site is responsive, and under four seconds or visitors will leave.

By default, the PHP engine re-compiles Drupal every time you load a web page. To avoid this re-compilation time and reduce page load times, install a PHP "script cache." A script cache is a PHP engine extension that saves compiled programs into a reserved chunk of computer memory. On the first use of a Drupal web page, the engine compiles Drupal and saves it into the cache. From then on, every access to a Drupal page skips the compilation step and reuses the version saved in the cache. This dramatically improves Drupal's performance.

There are several PHP script caches available. Two of the most popular are free:

Both of these come with good installation instructions. For either one you'll drop an extension file into a special PHP directory and add a few lines to PHP's configuration file, "php.ini".

When you're done installing either script cache, restart your Apache web server. Load a Drupal web page, and it'll still be slow the first time. Do it again and it'll come back faster. From then on the PHP engine is using the cached version of Drupal and page loads go faster.

The table below shows file load times for our simple and complex test web sites (see Specifications for Drupal web site testing).  These times are only for the HTML part of the page and exclude CSS, images, and JavaScript.  We'll get back to those later.

Both PHP script caches reduce HTML file load times by half.

Test Simple site
Complex site
No script cache 2.145 sec
(100%)
7.860 sec
(100%)
eAccelerator 0.913 sec
(43%)
5.066 sec
(65%)
APC 0.915 sec
(43%)
5.085 sec
(65%)
HTML file load times
Page load times using a PHP script cache

Step 2. Turn on MySQL query caching

After installing a PHP script cache, HTML file load times dropped by half.  But a complex web site can still take a long time to load the page.  To improve performance further we need to reduce the amount of time Drupal spends preparing a web page.

Drupal uses a MySQL database to store a web site's content. When a site visitor asks for a web page, Drupal queries the database, get's the content, formats it, and sends it to the visitor's browser. For a typical web page, Drupal may do several dozen queries, and this takes time.  By default, Drupal and MySQL perform these same queries every time a page is loaded. If the site's content in the database isn't changing, then the queries return the same results every time. To save time and avoid repeated queries, enable MySQL's "query cache."

The "query cache" is a built-in feature of MySQL that saves the results of common queries in a reserved chunk of memory. The first time a query is issued, MySQL collects the data and saves it to the cache. From then on, each time the same query is issued, MySQL skips re-collecting the data and returns the results saved in it's cache. This significantly improves MySQL and Drupal performance.

By default, MySQL's query cache is disabled. To turn it on, use the MySQL Administrator program available free with MySQL:

  1. Start MySQL Administrator and log in as the database administrator.
  2. Enable the query cache:
    1. Click the "Options" button at the top of the window.
    2. Select "Performance" from the pull-down menu to show the performance settings.
    3. Click on the pencil icon beside "Query cache size" and enter a value, such as 32 Mbytes.
    4. Click the "Save" button at the bottom of the window to save the settings.
  3. Restart the database:
    1. Click the "Service" button at the top of the window.
    2. Click "Stop server" to stop the server.
    3. Click "Start server" to restart it, now using the new settings.
  4. Quit.

Keeping the eAccelerator PHP script cache installed from step 1, MySQL's query cache reduces file load times by half again.  For a complex site that requires lots of database queries per page, the load time reduction can be even more dramatic.  Remember, though, that for now we are ignoring the time to load CSS, images, and JavaScript for the page.  Those things take time too, but we'll get to them later.

Test Simple site
Complex site
No query cache 0.913 sec
(100%)
5.066 sec
(100%)
32M query cache
0.517 sec
(57%)
1.696 sec
(34%)
HTML file load times
Page load times using a MySQL query cache

Step 3. Turn on Drupal page caching

With PHP script caching and MySQL query caching, HTML file load times are now about 1/2 second.  That's better, but that is just the time to build the web page.  Factor in web download times plus time for images, JavaScript, and CSS features of a page, and the overall load times are still poor.  To improve performance further, we need to do more to reduce Drupal's page building time.

Drupal uses the results of MySQL queries to build a Web page to return to each site visitor. By default, Drupal builds a new page for every site visitor. If two visitors ask for the same page, Drupal builds the page twice. To avoid repeated page builds, enable Drupal's "page cache".

The "page cache" is a built-in feature of Drupal that saves frequently requested pages. The first time a page is requested by a visitor, Drupal issues MySQL queries, builds the page, and saves it to the cache. From then on, each time the same page is needed, Drupal skips the page building step and returns the page saved in the cache. This dramatically improves Drupal's performance.

Caveat: Drupal's page cache only works for anonymous visitors.  The cache is disabled for visitors that log in. Once a visitor logs in, Drupal has to maintain unique information about them, such as their shopping cart's status for an e-commerce site, or the user's preferences for the site's language, theme, or reported time zone. Drupal uses this information to create custom web pages tailored to each user. Since no two logged-in visitors get the exact same page, the page cache can't be used.

Enable page cache

By default, Drupal's page cache is disabled. To enable it:

  1. Log in to your site as the administrator.
  2. Click on the "Administer" menu choice.
  3. Select the "Performance" page under "Site configuration."
  4. Choose either "Normal" or "Aggressive" page caching.
  5. Save the configuration.

Normal and aggressive page caching both cache pages in the same way. Aggressive page caching also disables some optional features inside Drupal, giving a slight performance boost. Those features are only needed by a few modules. The performance settings page will tell you if you have any modules installed that will be a problem with aggressive page caching.

Keeping eAccelerator installed from step 1, and MySQL's query cache enabled from step 2, Drupal's page cache reduces file load times by another 90-95%.  The simple and complex test sites now take about the same time to deliver a cached page, and both do so in under 1/10th of a second.

Test Simple site
Complex site
No page cache 0.517 sec
(100%)
1.696 sec
(100%)
Normal page cache
0.049 sec
(10%)
0.082 sec
(5%)
Aggressive page cache
0.044 sec
(9%)
0.047 sec
(3%)
HTML file load times
Page load times using a Drupal page cache

Conclusions

The combination of a PHP script cache, MySQL's query cache, and Drupal's page cache drastically reduces HTML file load times by 98-99% overall. For the simple test web site, load times dropped from 2 seconds to under 1/10th of a second.  For the complex test site, load times dropped from nearly 8 seconds to under 1/10th of a second.


Page load times overall

However, the news isn't quite that good.  The load times measured above are only for the HTML page and do not include the time to load each of the CSS, JavaScript, and image files associated with the page.  Those load times can be significant.  Also, these performance tests were done on an ideal network connection.  If your site is served over a cable modem or DSL connection, slower network speeds will mean slower load times for your site's visitors.

The table below shows page load times for the test site home pages - including CSS, JavaScript, and images this time.  These tests also throttle down the network speed to the 64 Kbytes/sec typical for sites served over a cable modem or DSL connection.  Though the optimizations above drastically reduced the time to build an HTML page, the time to send it and its CSS, JavaScript, and images now dominates.  Load times to the site visitor are still 6-10 seconds, which is way too slow.  More optimizations are necessary to make these  times reasonable.

Test Simple site Complex site
Original 10.473 sec 20.676 sec
Enable script cache 9.412 sec 18.060 sec
Enable query cache
8.709 sec 14.748 sec
Enable page cache 6.725 sec 10.527 sec

What to do next

There's more you can do:

Nadeau software consulting
Nadeau software consulting