If you want your server to perform optimally the best solution is to get tons of memory and big, fast drives. However, in most situations, these brute-force techniques won’t be an option. Given that we operate under less-than-ideal conditions, getting a handle on some subtler techniques for optimizing server performance makes more sense. Accordingly, this section gives a brief overview of some of the major things you can do to fine-tune your server.
MySQL exposes a large number of variables whose values can be modified to meet custom requirements. Some of these variables can be set at the time of starting the MySQL server, and others can be set while the server is running.
When it comes to tuning server variables for maximum performance, the MySQL manual recommends that you first look at the key_buffer_size and table_cache variables.
- The key_buffer_size variable controls the amount of memory available for the MySQL index buffer. The higher this value, the more memory available for indexes and the better the performance. Typically, you would want to keep this value near 25 to 30 percent of the total available memory on the server.
- The table_cache variable controls the amount of memory available for the table cache, and thus the total number of tables MySQL can hold open at any given time. For busy servers with many databases and tables, this value should be increased so that MySQL can serve all requests reliably. Also relevant here is the max connections variable, because the MySQL manual recommends setting the table_cache value using the formula (table_cache = max_connections x N), where N is the number of tables in a typical join.
These values can be changed using the SET command, as in the example shown:
mysql> SET GLOBAL table_cache=200; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@table_cache; +---------------+ | @@table_cache | +---------------+ | 200 | +---------------+ 1 row in set (0.01 sec)
Note that once you change a global server variable, it remains in effect until the server is shut down. This means if you find a beneficial setting, you need to reset it on startup every time. Because this is cumbersome, it’s useful to know a way of making your changes permanent. This can be accomplished by setting a variable in an option file.
Once you’ve got your table cache and index buffer set up the way you want them, you can turn your attention to the various other memory buffers MySQL uses.
- You can speed up queries that use the ORDER BY or GROUP BY clause to sort the result set by increasing the value of MySQL’s sort buffer, controlled via the sort_buffer variable. Also consider increasing the read_rnd_buffer_size variable to speed up reading of the sorted rows.
- You can speed up SELECT queries that scan the table sequentially by increasing the size of MySQL’s read buffer via the read_buffer_size variable.
- When performing a transaction, MySQL stores the statements that make up the transaction in a cache until it receives instructions to write them to the binary log and commit them to the database. For long or complex transactions, the size of this cache should be increased to obtain better performance via the binlog_cache_size variable.
- If you’re planning on so-called “bulk inserts” (that is, inserting multiple records using a single INSERT command), you can speed things up by increasing the value of the bulk_insert_buffer_size variable. However, this only works with the MyISAM table type.
- If you’re anticipating a lot of new connections to the server, it’s a good idea to increase the value of the thread_cache_size variable. This variable controls the size of the cache where server threads go when the client they’re servicing disconnects. Threads from this cache are then reused to service new connections. The higher this value, the more threads can be cached, and the better the response time to new connection requests.