Optimizing MySQL

Tuning your MySQL server for increased performance is exceptionally easy to do, simply because you can see huge speed increases by getting your queries right. But this chapter isn't about traveling up the learning curve, it's about quick tips that will make things work better now. MySQL can do that, too, as long as your system has enough RAM.

The key is understanding how MySQL's buffers work there are buffers and caches for all sorts of things, and finding out how full they are is crucial to maximizing performance. MySQL performs best when it is making full use of its buffers, which in turn places a heavy demand on system RAM. Unless you have 4GB of RAM or more, you don't have enough capacity to set very high values for all your buffers. Picking and choosing is required.

Measuring Key Buffer Usage

When you add indexes to your data, it enables MySQL to find data faster. However, ideally you want to have these indexes stored in RAM for maximum speed, and the key_buffer_size variables define how much RAM MySQL can allocate for index key caching. If MySQL cannot store its indexes in RAM, you will experience serious performance problems. Fortunately, most databases have relatively small key buffer requirements, but you should measure your usage to see what work needs to be done.

To do this, log in to MySQL and type SHOW STATUS LINE '\key_read' ;. That returns all the status fields that describe the hit rate of your key buffer. You should get two rows back: Key_reads and Key_read_requests, which are the number of keys being read from disk and the number of keys being read from the key buffer. From these two numbers you can calculate the percentage of requests being filled from RAM and from disk, using this simple equation:

100   (Key_reads / Key_read_requests) x 100) 

That is, you divide Key_reads by Key_read_requests, multiply the result by 100, and then subtract the result from 100. Plug in some numbers and you can get a result. Suppose Key_reads is 1,000 and Key_read_requests is 100,000. Divide 1,000 by 100,000 to get 0.01. Move the decimal point two spaces to the right to multiply by 100 and get 1.0. 100 1 = 99; this is the percentage of requests being filled from RAM. Be happy.

Most people should be looking to get more than 95% of their requests served from RAM. The primary exception to this rule is if you update or delete rows often. It's hard to cache something that's always changing, and MySQL won't do it. If your database is largely read-only, the ratio should be closer to 98%. Lower figures mean you might need to bump up the size of your key buffer.

If you are seeing problems, the next step is to check how much of your current key buffer is being used. Use the SHOW VARIABLES command and look up the value of the key_buffer_size variable. It is probably something like 8388600, which is eight million bytes, or 8MB. Use the SHOW STATUS command to look up the value of Key_blocks_used.

You can now determine how much of your key buffer is being used by multiplying Key_blocks_used by 1,024, dividing by key_buffer_size, and multiplying by 100. So if Key_blocks_used is 8,000, you multiply that by 1,024 to get 8,192,000; dividing that by key_buffer_size (8388600) gets us to 0.97656. Multiplying that by 100 gives us a key buffer ratio of 97.656, or nearly 98% of your key buffer is being used.

Now to the important part. You have figured out that you are reading lots of keys from disk, and you also now know that the reason for reading from disk is almost certainly because you do not have enough RAM allocated to the key buffer. The general rule: Allocate whatever RAM you can spare to the key buffer, up to 25% of everything you have. This translates to about 128MB on a 512MB system. Beyond that, you will go the opposite way, slowing things down immeasurably if you have to access Swap for the key buffer.

Open /etc/my.cnf in your text editor and look for the line that contains key_buffer_size. If it is not there, create one under the line [mysqld]. When you set the new value, don't be arbitrary about it. Try doubling what you have there now (or 16MB if it's a new line); see how that goes. To set 16MB as the key buffer size, the line should look like this:

[mysqld] set variable = key_buffer_size=16M datadir=/var/lib/mysql 

Restart your MySQL server with rcmysql restart, and then go back into MySQL and run SHOW VARIABLES again to see the key_buffer_size. It should be 16773120 if you have it set to 16MB. Because MySQL just got reset, all its values for key hits and the like will also have been reset. You need to let it run for awhile so you can assess how much has changed. If you have a test system you can run, so much the better.

After your database has been accessed with normal usage for a short while (if you get frequent accesses, this might be only a few minutes), recalculate how much of the key buffer is being used. If you get another high score, double the size again, restart, and retest. Keep trying until your key buffer usage is below 50%, or you find out you don't have enough RAM to do this properly. Again, never use more than 25% of your physical RAM for the key buffer.

Using the Query Cache

Newer versions of MySQL allow you to cache the results of queries so that if new queries come in that use the same SQL, the result can be served from RAM. In some ways, the query cache is quite intelligent. If, for example, part of the result changes because of another query, the cached results are thrown away and recalculated next time. However, in other ways it is very simple. For example, it uses cached results only if the new query is the same as a cached query, even down to the capitalization of the SQL.

The query cache works well in most scenarios. If your site has an equal mix of reading and writing, the query cache will do its best but will not be optimal. If your site is mostly reading with few writes, more queries will be cached (and for longer), thus improving overall performance.

You need to find out whether you have the query cache enabled before we try anything else. To do this, use SHOW VARIABLES and look up the value of have_query_cache. If all is well, you should get YES back, meaning the query cache is enabled. Look for the values of query_cache_size (the amount of RAM allocated to the query cache) and query_cache_limit (the maximum result size that should be cached). These should be set at something like 8388608 (8MB) and 1048576 (1MB).

An 8MB query cache should be enough for most people, but if you are storing an especially large amount of data, consider doubling that. You should never need a query cache of more than 32MB, though.

Next, type SHOW STATUS LIKE 'Qcache%'; to see all the status information about the query cache. You should see values for eight variables from which we can determine the current use of the query cache.

Add the totals of Qcache_hits, Qcache_inserts, and Qcache_not_cached. This gives you the total number of queries received. Dividing Qcache_hits by the total queries and multiplying the result by 100 gives you the percentage of queries being served from the query cache. The higher the number, the better off you are. If the gods smile upon you, it will be in the mid-to-high 90s.

Using the query cache does not incur much of a performance hit. When MySQL calculates the result of a query normally, it simply throws it away when the connection closes. With the query cache, it hangs on to those queries, so there's no extra work being done.

Miscellaneous Tweaks

If you have tuned your key buffer and optimized your query cache and still find your site struggling, there are a handful of smaller changes you can make that will add speed to your database.

When reading tables, MySQL has to open the file that stores the table data. How many files it keeps open at a time is defined by the table_cache setting, which is set to 64 by default. You can increase this setting if you have more than 64 tables, but be aware that there are limits on how many files can be open at a time. Think things out before exceeding 64 tables in a database.

The other thing that is tweakable is the size of the read buffer, which is controlled by read_buffer_size and read_buffer_rnd_size. Both of these are allocated per connection, meaning you need to exercise care to have large numbers. Whatever you choose, read_buffer_rnd_size should be three to four times the size of read_buffer_size. If read_buffer_size is 1MB (a good size for a very large database), read_buffer_rnd_size should be 4MB.

Query Optimization

The biggest speed-ups can be seen by reprogramming your SQL statements so they are more efficient. If you follow these steps, your server will thank you.

• Select as little data as possible only the fields you need.

• If you only need a few fields, use LIMIT to select the number you need.

• Declare fields as NOT NULL when creating tables to save space and increase speed.

• Provide default values for fields, and use them where you can.

• Be very careful with table joins because they are the easiest way to write inefficient queries.

• If you must use joins, be sure you join on fields that are indexed.

• Find and fix slow queries. Add log-long-format and log-slow-queries = /var/loog/slow-queries.log to your /etc/my.cnf file, under [mysqld], and MySQL will tell you about queries that took a long time to complete.

• Use OPTIMIZE TABLE tablename to defragment tables and refresh the indexes.