vBulletin Performance Tuning and Optimization: MySQL

Originally this was going to be an article covering all aspects of vBulletin performance tuning, but as so much of this is simply about general operating system and web optimization, this article was in danger of becoming huge. In fact, you easily could write a whole book on general web performance - which is just what I did. So rather than risk plagiarizing myself, or doing myself out of a job (I do a lot of performance consulting, particularly for clients running vBulletin), I decided to stick to a single topic: MySQL, the MyISAM engine, and how they affect vBulletin performance (isn't that 3 topics?)


Introduction

Users love vBulletin for its easy to use and feature-rich interface; sys admins hate it because it eats system resources like nothing else (oh the iron of vbulletin.com describing Apache as a resource hog).

I've been working with vBulletin for the past decade now, including some very big clients here in the UK, and the issue they always have is performance. In fairness, vBulletin isn't marketed as a mean, lean forum - if you want that go for something like SMF - rather its strengths are its power and rich features (if you've ever developed plugins/mods for phpBB, you'll appreciate how elegant vBulletin's plugin system is). Still, that doesn't mean there isn't plenty of scope for performance tweaking, and this page looks at MySQL tuning, specifically for MyISAM and vBulletin.

The folks on vbulletin.com have quite a dogmatic and oversimplified attitude to MySQL tuning (whoops, there goes my chance of a back-link). If you go there asking for help with performance/optimization they'll ask you how much RAM your server has, and from that give you values for what the size of your MyISAM key buffer etc should be set to. Although these are usually reasonably good starting points, MySQL tuning is too server- and workload-specific for this to result in an optimal configuration ... and it some situations it will be way out.

The first thing to consider is whether you have a dedicated machine for MySQL, or whether it sits on the same machine running the web server. In the former, you can dedicate a lot more resources to MySQL; in the latter it's a juggling act to keep both MySQL and (usually) Apache happy. Of course, your machine might also be acting as a mail server (watch out for SpamAssassin eating resources), which further complicates things. Most people start out with a single all-in-one server, then move MySQL to its own server when they have outgrown this. This is generally sound advice, the main downside being an increase in latency when the PHP code issues database queries. If the MySQL server is in the same datacentre as the web server, this latency should be insignificant (although remember that this latency will be added to every query, and a typical page may contain a dozen or so queries).

Regardless of whether you're running a dedicated MySQL server, or sharing it with Apache, the first rule (and remember, there are no rules) is that you want to keep as much of your dataset in memory as possible: accessing data stored in memory is always a lot faster than reading it from disk. This is particularly important if your disks are slow, or if your machine is frequently IO-bound. MySQL doesn't actually offer a way to control caching of data blocks - it leaves that to the OS - but Linux (and other UNIX flavours; not sure about Windows) generally does a good job of using free memory to cache disk IO. If you find that MySQL is hammering your disks (check with iotop), it may be worth thinking about a memory upgrade (but be aware that the activity could be down to other factors too).

MySQL Tuning Primer

For the reasons just outlined, I'm not a huge fan of performance tuning script for MySQL - they make too many assumptions, and the user often treats their output as gospel, rather than as merely suggestions.

That said, I quite like Matthew Montgomery's MySQL Performance Tuning Primer, even if it is a couple of years since it was last updated. For me, the power of this tool is not the performance suggestions it makes, but the fact that it compiles a lot of the information you'll need into a single, easy to read digest. Sometimes (especially for InnoDB, which isn't covered here), you'll need to dig into the MySQL system stats for the full story, but the MySQL Tuning Primer usually gives you what you need to get started. I'll reference it's output throughout this article

MyISAM vs InnoDB

vBulletin uses the MyISAM storage engine for most tables (but take a look at this blog entry and this one on vbulletin.com. Kudos to Mike Anders -it's so nice to see someone who genuinely knows what they are talking about, and who doesn't patronise the general vbulletin webmaster by hiding the technical stuff), so the main tuning parameter here is the key_buffer, a cache for MyISAM table indexes...

The Key Buffer

A good starting point is to set this (via my.cnf) large enough to hold your entire table indexes, cutting down on disk IO; but that isn't always possible (because of memory constraints) or desirable: with a large cache, you end up caching data that is rarely accessed, when the memory might be better used elsewhere. In addition, MySQL still has the OS's disk caching to fall back on; so accessing an index block not held in the key cache doesn't necessarily result in a disk read. Still, if you have lots of memory and a modest index size, set the key buffer high: when the OS caches disk IO, you have no way of telling it that some data is more important than others, and these pages may be written out. With the key cache you can force the data to stay in memory.

So, if setting the key buffer is a juggling act, how do you find an acceptable compromise? A popular approach is to look at the key hit ratio - that is, the ratio of cache hits to cache misses. It seems logical: a high ratio means the cache is working well, and MySQL rarely has to trouble the disk; a low ratio means lots of delays while MySQL reads the index from disk. Unfortunately, even this isn't completely effective, for several reasons:

People like ratios because it eliminates the need to think too hard. How much easier life is when you can say follow a rule like "this miss to hit ratio should be lower than 1:1000".

The following example illustrates some of these points. Here's a snippet from the output of the MySQL Performance Tuning Primer on a machine with 16GB RAM:

Current MyISAM index space = 7.63 G
Current key_buffer_size = 2.34 G
Key cache miss rate is 1 : 38150
Key buffer free ratio = 0 %

The machine runs a busy vBulletin forum, two development versions of the forum, an old Joomla site (they dumped Joomla when vBulletin introduced a CMS to vb4, but still keep the site behind a restricted area, in case they ever need to reference old articles), and a few other old, rarely used sites. Because of all these, the index space is quite large. Roughly, each vBulletin instance has around 2GB of indexes, with the remaining 1.6 GB used by Joomla and the other sites.

In this situation it's clear that a key buffer of 7.6GB isn't necessary or desirable (especially given the machine has only 16GB of memory). The vast majority of hits on the key cache are for indexes on the live forum tables - the dev forums and Joomla often go months without being accessed. Caching 5GB of indexes for tables that are rarely accessed is wasting memory that could be used elsewhere. Of course, this does mean that when the dev forums are accessed, useful data is pushed out of the key buffer, causing a temporary dip in performance; but the LRU algorithm means that this data is quickly removed once it is no longer being used.

Finally, the cache miss ratio: 1:38150, or 0.0026%. Even though the buffer is full, and is less than a third of the overall index space, this is still a very healthy ratio (not withstanding what we've just said about the dangers of reading too much into this ratio).

Now do you see why saying "I have xxx GB of RAM, therefore my key_buffer should be set to xxx" is such a huge oversimplification?

MySQL Thread Cache

The thread cache is an attribute of MySQL itself rather than a particular storage engine. When a client connects to MySQL, a new thread is created to handle the request, something which involves a fair amount of overhead. By keeping a pool of threads in memory, we can eliminate this overhead.

Some sources suggest setting the thread_cache size to the number of CPU cores in the machine, but there is no correlation between the two; rather you should aim to keep a healthy (low) ratio between the number of threads created and the number of connections - in other words, set the thread_cache large enough that very few connections result in a new thread being created. Yes, I know what we've said about ratios, but this is slightly different for a couple of reasons:

But again, don't forget that a ratio doesn't take into account magnitudes. A thread cache miss ratio of 1:1000 is nothing if it translates to one miss per hour; if it translates to one miss per second, it's a different story.

MySQL Tuning Primer doesn't give all the information we need here, but we can fetch the stats manually:

mysql> SHOW STATUS LIKE 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 12    |
| Threads_connected | 6     |
| Threads_created   | 22596 |
| Threads_running   | 1     |
+-------------------+-------+

mysql> SHOW GLOBAL STATUS LIKE 'Connections';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Connections   | 12218046 |
+---------------+----------+

This machine has been up for 14 days, during which time there have been 12,218,046 connections. On 22,596 occasions, a new thread has been created; the rest of the time, a thread has been pulled from the cache. That's a miss ratio of around 0.002%. Another way of looking the figures is to say that MySQL is averaging around 14,000 connections per hour (12218046/(14 * 24)) and 67 thread misses per hour. One thread miss per minute is unlikely to be hurting much (but remember these are just averages - we could be seeing several misses per minute at peak times), but we may as well raise the thread_cache a little.

The Query Cache

Another non-MyISAM specific cache, the QC caches the results of SELECT queries, offering the potential for a huge performance boost. There are a few caveats though, that reduce the efficiency of the cache:

For instance, consider the following PHP code:

$sql = "SELECT userid FROM user WHERE lastvisit >" . (time() - 60 * 60 * 24);

This pulls a list of users who have been active in the last 24 hours. It's the sort of thing you'll see in many vBulletin plugins.

The first time the code is executed, the query is cached (along with the response). A second later, though, and the timestamp has changed, so there'll be no cache hit. You can test the logic of this easily enough:

mysql> SELECT count(userid) FROM user WHERE lastvisit > 100;
+---------------+
| count(userid) |
+---------------+
|        351083 |
+---------------+
1 row in set (0.29 sec)

mysql> SELECT count(userid) FROM user WHERE lastvisit > 100;
+---------------+
| count(userid) |
+---------------+
|        351083 |
+---------------+
1 row in set (0.00 sec)

The second time, the query is served from the cache, and returns in no time (quite literally, if you believe MySQL).

The solution to this, of course, is to ensure that the timestamp changes less frequently in non-critical queries such as this (it's probably OK for a stats plugin not to be accurate down to the second) - eg use mktime to return the timestamp for rounded to the nearest hour. You won't see many opportunities for this in vBulletin itself, but many plugins fail to utilize the QC like this. Of course, you could go one step further and implement the caching elsewhere, perhaps using a cron to update periodically; but a quick hack of a query to better utilize the QC is still a good boost.

The next problem with the query cache is invalidation. Whenever a table is written to, MySQL purges any QC entries relating to this table. This is to avoid the possibility of the QC returning stale data, but MySQL invalidates entries even if the query result hasn't been affected by the table update. There's nothing much you can do about this, other than be aware of it. You'll also find that many vBulletin tables are written to regularly (eg when a user views a thread, the views column is updated), so the QC will never be as efficient as you might hope.

It's easy to assume that bigger is better when it comes to the QC, but this isn't the case. A larger QC means larger management overheads: when a query arrives MySQL has to scan the QC for any matches, when a table is updated MySQL has to invalidate entries, and when the QC is full MySQL uses LRU to remove old entries; this all takes time. As we've seen, it's also quite likely that the QC will contain a number of queries that will never be matched again (eg because they use a timestamp in the WHERE clause); so with a large QC you can often end up with a lot of useless caching.

In some situations it can make sense to turn the QC off completely. If you know that the vast majority of queries will never be able to utilize the cache, you save the overhead of even troubling the QC. This isn't the case with vBulletin though, and a cache size in the region of 16 to 96M is generally a good ballpark figure.

On the previously mentioned machine (16GB RAM, 6 CPU cores), the cache is set at 48M, and after 14 days is around 77% full:

Current query_cache_size = 48 M
Current query_cache_used = 37 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 77.08 %
Current query_cache_min_res_unit = 4 K

Max Connections

It's tempting to set max_connections high, because the last thing you want is database connection errors if your forum has a sudden flood of visitors. Max_connections is there for a reason though: it's protecting MySQL from a flood of queries that could overload the CPU and exhaust physical memory, sending the server into a downward spiral of disk swapping. Don't be unrealistic here; there's only so much work your server can handle.

One strategy for setting max_connections is to look at your Apache settings. How many concurrent requests is Apache set to handle? If Apache will only handle 100 concurrent requests, you shouldn't be seeing more than 100 simultaneous MySQL connections (except for the odd connection from a cron job, an admin, perhaps a mail server that uses MySQL for storing authentication details etc).

The other strategy is to pluck a figure out of the air, then wait a few days and see how MySQL is coping. If the number of connections at any time has got near to your maximum, consider raising max_connections a little. If the server has been really struggling when the number of connections has been high, consider lowering it a little. Here's another example:

Current max_connections = 300
Current threads_connected = 5
Historic max_used_connections = 157
The number of used connections is 52% of the configured maximum.
Your max_connections variable seems to be fine.

This MySQL server has been up for almost 6 months, so it's probably safe to say that we'll rarely go over 157 connections. If anything max_connections here is probably too high. 200 would probably be enough to allow for some leeway.

Table Cache

MySQL maintains a cache of open tables to minimize the overhead when a query needs to access a table. As usual, big is often better, but not always ....

At first you might think that the table cache would only need to be as high as the number of tables in your databases, but this isn't the case. In fact each connecting client gets its own private copy of the table to work with (if multiple clients were executing writes on the same table simultaneously, things could get messy). Since a client query might span several tables (eg via JOIN), the most tables that will ever be open simultaneously is given by: max_connections * highest number of tables in a JOIN. So if you have max_connections set at 150, and the web code only every joins across a maximum of 3 tables, the most tables you'll ever have open at once will be 450. I haven't checked through the vBulletin code exhaustively, but you probably won't see many/any joins across more than say 5 or 6 tables. Most of the time it will only be across 3 or 4 tables.

So, we set table_open_cache and table_definition_cache to this figure, right? For vBulletin (based on the number of tables in creates, its usage of JOINS, and the fact that it uses MyISAM), the answer is most likely yes. If you can afford to set the table cache large enough to hold the maximum theoretical number of open tables (assume a worst case scenario where max_connections clients are connected, and each is running that big JOIN across lots of tables), then do it.

If memory is really limited, and you can't afford to set the TC quite as high as you'd like, this is where problems can occur. As the TC grows in size, the cost of managing it significantly increases. If the TC is big enough to hold all tables, you won't see this, but if it is not quite big enough, MySQL will need to remove tables from the cache (flushing them back to disk, after first using an LRU algorithm to determine which tables can go) and read in other tables. This can hurt performance, although it's only usually a big problem under quite artificial situations (for instance, where you are randomly accessing tens of thousands of tables). You shouldn't experience this with vBulletin; if you don't have enough memory for your TC, consider a RAM upgrade anyway.

Read, Sort, and Join buffers

These buffers are allocated per-connection, so a small change in size has the potential for a large change in memory usage (with 200 max connections and a 1MB read buffer, the maximum memory that can be allocated is 200 MB. With a 10MB buffer, that limit is 2GB). The purpose of these buffers is to provide temporary memory for reading, sorting, and joining queries; if a query needs more memory than has been set, MySQL falls back on using the disk, which is naturally undesirable.

Now, vBulletin often issues queries which return a lot of data (for instance, pulling all the posts for a given thread (or 1 page worth of posts, anyway)), but before you go rushing to raise these buffers, there is another gotcha. For buffer sizes of 256KB or less, MySQL uses malloc; over 256KB and it uses mmap(), which is a lot slower (but this is relative: memory allocation is still a lot faster than say accessing the disk). Again it's a balancing act. With a large read/sort/join buffer, you'll give big queries a significant boost at the expensive of a slight slowdown on other queries.

Either way, these buffers default to 128KB, but you might as well raise them to 256KB. The increase in potentially memory usage is unlikely to be an issue, and the performance benefits are generally worth it:

sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
join_buffer_size = 256K

... but think carefully because you increase these higher.

Temporary Tables

Temporary tables themselves aren't a problem, but when they are created on disk rather than in memory, performance suffers. The first thing to check is max_heap_table_size and tmp_table_size; if a temporary table is large than these it will be created on disk rather than in memory; but simply raising these values won't necessarily eliminate on-disk temporary tables

In-memory temporary tables use the MEMORY engine; disk-based temporary tables use MyISAM. If a temporary table uses TEXT or BLOB columns, it will always be created on disk (because the MEMORY engine doesn't support these). There isn't much you can do about this other than be aware of it, and realise that MySQL will always create some disk-based temporary tables for vBulletin queries, no matter how large you set the temp table size. Around 32MB tends to be a good figure for these two variables:

max_heap_table_size = 32M
tmp_table_size = 32M

Low Priority Updates

The MyISAM engine (used by most vBulletin tables, but see earlier for a link on how to change to InnoDB) uses table-level locking. If a table is being written to (UPDATE/REPLACE/DELETE/INSERT), no other clients can read from the table until this write has completed. With vBulletin, you can no doubt see how this can affect performance: one user makes a post, resulting in a write to the posts table, and no other users can view threads until the write is complete. Usually this isn't a huge problem (although it can be noticeable), but MySQL offers a possible work-around: low priority updates. With this option enabled, writes to MyISAM tables (and MEMORY too, actually, since it also uses table-level locking) are given a lower priority than SELECTs.

In theory this sounds ideal, but on heavily accessed forums, the update priority can end up being too low: when the user makes a post, it seems to take a lifetime for the page to reload. Impatient users often end up hitting the 'post' button multiple times, resulting in duplicate posts. You may argue that low priority updates are a good thing - it seems worth penalising less common INSERTs to speed up the much more common SELECTs - but user perception plays a big part here. Users don't notice a slight delay when viewing a thread, but they'll notice (and moan) if there's a significant delays in posting.

INSERT DELAYED

If you're familiar with the INSERT DELAYED syntax, you may think this offers the solution. When you INSERT with the DELAYED keyword, MySQL returns control back to the client immediately, without waiting for the write to actually take place. This also has a performance boost as multiple INSERTs can be grouped together and written in one batch.

The problem is that vBulletin often wants to know the LAST_INSERT_ID(), which INSERT DELAYED cannot provide (since the query may not even have been inserted yet). Also, a delayed insert is naturally not visible to SELECTs until it has been written. It would be a bit confusing to a user if he made a post, was redirected back to the thread, but his post wasn't yet visible.

All things considered, it's probably best to set low_priority_updates to zero, disabling it.

Delayed Key Writes

One thing you can delay safely is the writing of keys. Usually MySQL will flush a MyISAM index file to disk every time it is written to (in practice, this means every time a table is written to), which isn't good for applications like vBulletin that do a lot of table writes/updates. By setting delay_key_write = 1 in your my.cnf, changes to the index file won't be flushed to disk until the table is closed

This increase the possibility of table corruption - if the power fails on your server, you'll lose any changes to the index file that are still held in memory - but that isn't as bad as it sounds. For a start, it's just the index that will be corrupt, not the data itself. The index can easily be rebuild (although it can be time consuming). Secondly, even with delay_key_writes off, corruption can still occur if the power fails during a write to disk. There aren't degrees of corruption with an index file: it's either corrupt (and needs to be rebuilt from scratch), or it isn't.

The bottom line? Turn delayed key writes on. Your disks will thank you.

Concurrent Inserts

Remember what we said about MySQL locking tables from reading while a write is going on? In some situations, there's a way around this. Providing the table does not have deleted rows in the middle of it, an INSERT can be executed at the same time as a SELECT. Note that this only applies to INSERTs, not other forms of table writes (UPDATE/REPLACE/DELETE), and only if the INSERT would occur at the end of the table (because there are no gaps in the table). You can enable this by setting concurrent_inserts = 1 in my.cnf.

Conclusion

A lot of this article has been about MySQL tuning and the MyISAM engine in general, but I've tried to make it as vBulletin-specific as possible. If you've only learnt one thing from it (perhaps because your wife is sitting next to you, wittering away, and you can't concentrate), I hope it's that MySQL tuning is an area where it's hard to make generalisations. Hopefully this guide has helped you towards making informed choices, but ultimately it's a case of what works well for you: for your hardware, for your dataset, for your workload.

Don't be afraid to play around with MySQL settings. Make some changes, monitor MySQL for a few days (munin is a great tool for monitoring MySQL and general system performance), then revise your settings if necessary. But also remember that there is only so much tuning you can do - sometimes you just have to accept the fact that you need to upgrade your hardware.

Finally, another shameless plug for my book on website performance, which devotes a couple of chapters to MySQL and looks in more detail at topics covered here, and solutions for MySQL scaling such as replication, partitioning and sharding. And another plug for my Linux/web consultancy, which you can read about by following the menu links on the right. If you have performance problems with vBulletin, or Linux system in general, I can almost certainly help. Give me a shout.

Linux Services

Books

Code

vBulletin

Fun Stuff

Blog

Pete's Shed




linux support email pete@linuxbox.co.uk
(+44) 07890 592198