I wrote this back in June when I was knee deep in attempting to optimize a Drupal 5 website that was receiving a lot of traffic and showing very poor performance. This article is incomplete and I don't really come up with any solid solutions here, but thought this may be of some help nonetheless.

Performance of any site is a concern when you start getting actual traffic. Drupal performance can be even trickier, as it often likes to hog up server resources especially when you have a lot of modules enabled.

Often the issue lies with mySQL. While I like mySQL, configuring for optimal performance doesn't always come easy. One issue that can often occur is a large number of temporary tables being written to disk. What we want is for these tables to be written to memory. A good article which discusses the overhead of mySQL writing to disk can be found here at http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caus...

Essentially, it says MEMORY temporary tables can be 10-100 times faster than disk based MyISAM tables . That's quite a difference.

I started two or three weeks ago tweaking our mySQL configuration and performance on a site that was getting 85% or more of it's temp tables written to disk. Today, we're getting between 6-10%. There's still work to do, but the increase has been awesome and very noticeable in the speed of the site.

To check the status of your temp tables, in a mySQL prompt, type show status. This will show you all the variables mySQL has set, including:


| Created_tmp_disk_tables | 141880 |
| Created_tmp_files | 464 |
| Created_tmp_tables | 1304743 |

As you can see here, we still have quite a lot of our temporary tables writing to disk.

You may also want to use something like this script. Just put it on your server and run it with sh tuning-primer.sh and it'll give you some great tips overall, as well as tell you more about your temp tables.

The first method to stop this from happening is to increase your table_cache. A formula often used is:


table_cache = opened table / max_used_connection

Getting this up to a good number is key. Obviously, you can't just keep raising this, though. This value, and most others within mySQL, are going to be limited by your server's RAM. 

Other values you need to look at are tmp_table_size, max_heap_table_size, key_buffer, sort_buffer_size, read_buffer_size, myisam_sort_buffer_size. 

One problem you may encounter, though, is that blob and text column types are always written to disk. There are a few Drupal patches I ended up testing, benchmarking, and applying in order to fix some of these issues.

http://drupal.org/node/109513 converts our temp tables to the HEAP engine type over MyISAM. This alone was a huge improvement.

You can also turn on mySQL slow query log. To do so, simply add these lines to your my.cnf:


log-slow-queries=/var/lib/mysqllogs/slow-log
long_query_time=2
log-queries-not-using-indexes

The long_query_time is the queries you want to log longer than 2 seconds, or whatever value you might want to use. I'd increase this to start with to track down the worst queries. We started it at 5 seconds.

The log-queries-not-using-indexes will also log queries not using indexes, of course.

By using our slow query log, we found a few queries which were desperately in need of optimization. A very handy tool in defining whether a query is causing issues is by using the EXPLAIN command. This can be used by simply typing this into your mySQL command line prompt:


EXPLAIN SELECT DISTINCT(n.nid), e.event_start FROM node n INNER JOIN event e ON n.nid = e.nid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'content_access_rid'))) AND ((n.moderate != 1)) AND ( n.status = 1 AND ((e.event_start >= 457516800 AND e.event_start = 457516800 AND e.event_end = 460195199)) ) ORDER BY event_start;

And we were given results that look like this:


+----+-------------+-------+--------+-------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------
| 1 | SIMPLE | na | ALL | PRIMARY | NULL | NULL | NULL | 49571 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | n | ref | PRIMARY,status,node_moderate,node_status_type,nid | nid | 4 | brenda2_06_02_08.na.nid | 1 | Using where |
| 1 | SIMPLE | e | eq_ref | PRIMARY,event_start | PRIMARY | 4 | brenda2_06_02_08.na.nid | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------

Notice the Using where; Using temporary; Using filesort part. This tells us right there it's using a temporary table.

You can also use show processlist to get a list of what's currently going on.

For example gives me this row:


| 1237 | XXX | localhost | database | Query | 1 | Copying to tmp table | SELECT DISTINCT(node.nid), node.created AS node_created_created, users.name AS users_name, users.ui |

This will only show you things currently happening, though.

Now that we know this query is bad, we can work on rewriting it.