What to do if your site's too slow

Submitted by mvc on Fri, 2009-10-16 11:14

The following is the text of a presentation given at Drupal Camp Montreal in October 2009. A video recording is available here.

This past summer we launched a new version of an online newspaper which had been running since 1998. The site was fairly large (80,000 nodes in a 1.7 GB database, plus 1.5 GB of images) and the migration and cut-over went smoothly. We had also done a round of benchmarking with Apache benchmark from the local machine (to test speed without network lag as an issue). Since we weren't expecting heavy load, the server was not particularly fast (dual core AMD Opteron 2.5GHz with just 1G RAM, running Debian GNU/Linux). The site is based on Drupal 5 (since Views wasn't out for Drupal 6 when we started development) using the PHP/MySQL ad server Open X.

The problem was that the previous website hosting company had provided inaccurate usage information, so we were completely unprepared for the load we had to handle. When we set up awstats, we found out the site received 5,000 visits per day, resulting in 25,000 page views and over 120,000 webserver hits (1.4 per second). Apache was pushing 1.4 GB a day. As a result, our site, which had been fine in testing, was far too slow and the single dedicated server was completely overloaded. We managed to fix this within a day or two after the site was live, however. Here's what we did. The exact setup is specific to our needs, but the process of trial and error should apply to any site.

Client-Side Optimization

First of all, when a website seems too slow, remember that server-side fixes are only half of the story. When a server clicks on a link, their browser generally has to do a significant amount of processing as well. This can make any site seem slow, regardless of server power or load, and is the first place to start optimizing your site. For most websites, only client-side optimization is really important.

How long does each image take to load? To check, use Yahoo's YSlow or the profiler which comes with Firebug profiler (the "Net" tab). By default, Drupal handles missing (404) content with a hit to index.php, which will slow down the page load time, so make sure you have no broken images.

Firebug profiler

You should enable CSS & Javascript aggregation & compression, and consider using JSmin to strip whitespace from your Javascript. Most HTML content in Drupal is dynamic and is cached internally where possible, but you should also set expiration headers for non-HTML content so it will be cached by each visitor's browser. (Details for configuring these are given below.)

There are other tricks. For example, instead of providing two images for a button (for default and hover states), you can provide just one that contains both images, and use CSS to reposition which half is visible as appropriate. You can also use JS/CSS to (for example) let users switch to another internal tab without a new page load (like the Views administration pages), or use something like active menus to expand navigation menus with fewer page loads. And of course don't use Flash unless you really need it!

Server-Side Optimization

If your site's still too slow, the server settings themselves will have to be changed. First, you'll have to find out what's limiting the speed of your site: RAM, CPU, bandwidth, or disk I/O? For a typical website, Apache is bandwidth limited, PHP is CPU limited, and MySQL is memory limited and disk I/O bound.

To check which is your most pressing issue, use top from the command line to check how much RAM or CPU is being used by the webserver or database server. The program vmstat will report if processes are blocking while waiting to read or write to disk. For bandwidth, check how much capacity you're paying for, then check how much you're using via awstats. If you're running at your maximum bandwidth, your site would probably be faster if you purchased more or moved to a hosting facility which provided more. If your site is blocking on disk I/O, try to cache more in memory, especially with MySQL, or buy extra disks in a RAID array or other arrangement.

Reducing CPU Usage

First, start by turning off modules you don't need. Next, try installing a PHP opcode caching system. This will keep the parsed version of each PHP file in memory, saving PHP the need to load and interpret each file again. There are several options:

These options tend to come out fairly close in comparative reviews. Personally, I tried APC first because Dries recommended it once, and I've been happy with it. By default it uses 30 MB of RAM to cache opcodes, which is enough on this site for a 98.7% cache hit rate, a significant performance boost. Note that all PHP opcode caches can make Apache crash occasionally. Of course, on any important website, you should be running some service which will alert you if Apache goes down anyways, or better yet restart it as well. I personally use monit to do this, again because I tried it first on the basis of a few reviews and I've been happy with it, but there are many options.

APC

Apache Optimization

Of course, the first thing we did was install some more RAM (1 GB to 4 GB). Next, we checked how much RAM each Apache process was using via top. We gave MySQL as much RAM as we could, since the database seemed to be our slowest link, then set Apache's MaxClients directive to roughly the amount of RAM left over divided by the amount of RAM each Apache process required. We also set MaxRequestsPerChild to 400 instead of the default of 0 so that after serving 400 requests, each child would exit and respawn. This requires some overhead but deals with the fact that Apache processes tend to consume more RAM the longer they live.

Enable mod_expires so that non-dynamic content (CSS, images) will be cached by your visitors' browsers. Drupal's .htaccess file already includes configuration details for this so you should normally just have to turn it on. mod_deflate will compress text files before sending them, which will make them load faster for visitors. This requires adding the following line to .htaccess: AddOutputFilterByType DEFLATE text/html text/plain text/xml (in Debian and Ubuntu, this is done for you). (For Apache 1.x, use mod_gzip.)

You can also move .htaccess settings to your Apache virtual host configuration files and set AllowOverride None for the directory so it doesn't have to keep parsing them (of course, you'll have to restart Apache after any further changes).

After repeated trial and error, here's what we eventually came up with for our site:

  <IfModule mpm_prefork_module>
      StartServers          5
      MinSpareServers       5
      MaxSpareServers      10
      MaxClients          100
      MaxRequestsPerChild 400
  </IfModule>

MySQL Optimization

There are two main ways to improve MySQL performance: give your database server more RAM, and write better queries in custom modules.

To improve your queries, turn on the slow query log:

  log-slow-queries
  long_query_time = 10
  log-slow-queries = /var/log/mysql/example-slow.log

Once you have a list of queries which take more than 10 seconds, run them from the MySQL command line with the keyword EXPLAIN at the beginning of the line. This will tell you, for example, if MySQL had to create a temporary table to resolve complex GROUP BY and ORDER BY statements, or worse, if it had to write data to disk in order to correctly sort the output of a nested JOIN. Details of how to interpret these results are available in the MySQL manual. Often, these problems can be resolved by adding an index to tables you've created, or re-writing queries to avoid subqueries and joins. It's usually faster to do complex sorting in SQL, but occasionally doing it in PHP will be faster. When you've finished either fixing these queries or storing them in a custom cache table (see Drupal optimizations, below) set long_query_time to 5 seconds and check again.

mysql> EXPLAIN SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.sticky,
     r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data
     FROM node n INNER JOIN visource_dev_shared_drupal_5.users u ON u.uid = n.uid
     INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = 78864;
+----+-------------+-------+--------+---------------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table | type   | possible_keys       | key     | key_len | ref                         | rows | Extra       |
+----+-------------+-------+--------+---------------------+---------+---------+-----------------------------+------+-------------+
|  1 | SIMPLE      | n     | ref    | PRIMARY,vid,uid,nid | PRIMARY | 4       | const                       |    1 |             | 
|  1 | SIMPLE      | u     | eq_ref | PRIMARY             | PRIMARY | 4       | visource_dev_drupal_5.n.uid |    1 | Using where | 
|  1 | SIMPLE      | r     | eq_ref | PRIMARY             | PRIMARY | 4       | visource_dev_drupal_5.n.vid |    1 |             | 
+----+-------------+-------+--------+---------------------+---------+---------+-----------------------------+------+-------------+

mysql> explain SELECT DISTINCT(node.nid), node_data_field_date.field_date_value AS node_data_field_date_field_date_value,
     node.title AS node_title, node.changed AS node_changed, node_data_field_date.field_date_value2
     AS node_data_field_date_field_date_value2, node.type FROM node node LEFT JOIN node_access domain_access
     ON node.nid = domain_access.nid AND domain_access.realm = 'domain_id' LEFT JOIN content_type_event node_data_field_date
     ON node.vid = node_data_field_date.vid WHERE (node.status = '1') AND (node.type IN ('event')) AND (domain_access.gid IN ('2'))
     AND ((STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T') >= '2009-10-01 00:00:00' AND 
     STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T') <= '2009-10-31 23:59:59')) ORDER BY
    node_data_field_date_field_date_value ASC LIMIT 0, 999;
+----+-------------+----------------------+--------+---------------------------------------------------+-----------+---------+--------------------------------------------+------+----------------------------------------------+
| id | select_type | table                | type   | possible_keys                                     | key       | key_len | ref                                        | rows | Extra                                        |
+----+-------------+----------------------+--------+---------------------------------------------------+-----------+---------+--------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | node                 | ref    | PRIMARY,vid,node_type,status,node_status_type,nid | node_type | 14      | const                                      |  282 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | node_data_field_date | eq_ref | PRIMARY                                           | PRIMARY   | 4       | visource_dev_drupal_5.node.vid             |    1 | Using where                                  | 
|  1 | SIMPLE      | domain_access        | eq_ref | PRIMARY                                           | PRIMARY   | 775     | visource_dev_drupal_5.node.nid,const,const |    1 | Using where; Using index; Distinct           | 
+----+-------------+----------------------+--------+---------------------------------------------------+-----------+---------+--------------------------------------------+------+----------------------------------------------+

MySQL comes with sample my.cnf files for various amounts of available RAM. Use these as a starting point, then check how often various internal caches are being used and reallocate memory as necessary. The simplest way to do this is to let your server run for a day or two, then run tuning-primer.sh.

This is what we ended up using after some trial and error:

  key_buffer_size = 300M
  table_cache = 1500
  max_heap_table_size = 64M
  read_buffer_size = 256K
  tmp_table_size = 64M
  query_cache_limit = 2M
  query_cache_size = 20M

MyISAM vs. InnoDB

The InnoDB storage engine is generally considered to perform better under heavy load. However, by default, Drupal 6 and earlier use the MyISAM storage engine, mostly because it was better supported in earlier versions of MySQL. Today, even inexpensive shared hosting like Dreamhost provides support for InnoDB, and so Drupal 7 will use this by default.

The primary advantage is that InnoDB does row-level locking, which means that unlike with MyISAM the database can insert a new role while updating another one in another process, which is crucial for a large, busy site. InnoDB uses more RAM, in part because it stores more data about each table than just keys.

Busy sites will normally see a significant performance gain when converting to InnoDB. The standard advice is to convert everything except search_index. Note that on some systems (eg. Debian, Ubuntu) InnoDB support is disabled by default to save RAM, so it must be enabled by commenting out the line skip-innodb in my.cnf.

To tune InnoDB specific settings for MySQL, start with the script mysqlreport. The most important setting is innodb_buffer_pool_size which we currently have set to 512 MB.

For Drupal 5.x, note that Drupal core locks some tables in order to safely update them with MyISAM. To see the full performance benefit of InnoDB you will need to patch core as described in issue 55516, comment 192. This isn't necessary in Drupal 6.

Drupal Optimization

Of course, first make sure cron is running! If not, tables such as watchdog will grow out of control. Next, turn on the cache, setting the minimum expiration time if needed.

If you've created a custom module, you may need to cache & expire your own database calls. To do this, create a new database table with the same schema as the existing cache_* tables. Then, in each function which generates output using a series of lengthy queries, do something like this:

  // generate a unique cache id
  $cid = $attribute1 . $attribute2 . $attribute3;
  // try to retrieve this from the cache
  $cached = cache_get($cid, 'cache_my_custom_module');
  if ($cached && $cached->expire > time()) {
    // return cached value since it's not expired
    return $cached->data;
  }

  // do lengthy series of queries here
  ...

  // save this data with a time to live of 15 minutes
  cache_set($cid, 'cache_my_module', $output, time() + (60*15));
  return $output;

For multilingual data, you should either include the current language in $cid, or cache untranslated data and pass it through tt() after either retrieving it from the cache or generating it from scratch.

In addition to using Drupal's cache, enable CSS & Javascript aggregation at admin/settings/performance. With Drupal 5, this requires the Javascript aggregator module. In Drupal 5, you can also improve search performance by switching from core search to views_fastsearch with a new database index to get rid of duplicates in the search_index table: ALTER IGNORE TABLE search_index ADD UNIQUE INDEX (sid, word, type, fromsid); and then letting Drupal know you've done this at admin/settings/search.

views_fastsearch

The devel module will list slow queries too.

Next Steps

By the time we went through all these steps, our site was running well. Load was rarely over 0.5, MySQL stayed at about 20% of RAM (850 MB) and each Apache process used about 24 MB of RAM. Individual page loads were under one second even at peak load.

There are many further steps we could have taken. We could have profiled custom code in a debugger. We could have used the Boost module to cache pages as flat html for anonymous users using mod_rewrite. (For example, Development Seed used it for a United Nations site which got 117 million distinct visitors using four load-balanced Apache servers and one MySQL server.) We could have moved to lighttpd, which is generally considered to be less configurable but faster than Apache. And of course, we have could have bought better hardware, like more RAM or a separate database server. But this was enough for our client's needs, so we stopped there.

References

Tools suggested by presentation attendees

In addition to top, other system profilers include atop (for Apache processes), mytop (for MySQL processes, although you could also use the MySQL command processlist), and htop (like top but with better presentation).

The cache router module can pass different cache tables off to different caching mechanisms, including APC, eAccelerator, memcache, memcached, the database, and the file system.

Jet Profiler will also report on slow SQL queries.

The block cache module will cache modules per user to improve performance for logged in users. This was moved into core in Drupal 6.

Pressflow is an alternative packaging of Drupal core which includes backported performance patches from Drupal 7. Among other improvements, this allows for setting intelligent headers which allow use of caching proxies like Squid or Varnish.