Following up my previous post regarding server performance, I wanted to try and communicate my experiences with tuning specific applications that might run alongside (and under) Apache. MySQL has become amazingly popular during the last couple of years and its popularity is not undeserved. It performs well already out of the box and the fifth version is incredibly stable.

There are (of course! :D) a number of things you can do to improve your mySQL performance however, and here they are, in all their glory! :p

MySQL tuning:

One of the most surprising aspects of MySQLd is that it so often comes served with little to no cache in the main config. No query cache is especially devastating for servers that run applications with constant small database accesses (much like WordPress). To elaborate; Query-Cache is a buffer where MySQLd stores Query Results. When an incoming query comes from Apache, MySQLd checks its cache for matches before parsing. The query cache (obviously) has a timeout and never lives long enough to cause problems for rapidly updating content. For for example, if these two queries come in, subsequently:

SELECT tbl.posts FROM db.posts LIMIT 20
SELECT tbl.posts FROM db.posts LIMIT 20

Only the first one will actually be parsed and run through the database. The second one matches byte by byte and will hence get fetched from the cache (which is significantly faster). So, cache is good. There’s a couple of other types of cache, apart from Query cache which all can benefit your blog.. or… eCommerce site or whatever you want to improve :)

Start by grabbing a backup of your current configuration. Again, if you need a brush up with the text editor Vi, there’s a good one here:

cp /etc/my.cnf /etc/my.bak
vi /etc/my.cnf

Navigate your way to this section (normally is at the top)

[mysqld]

Without going in too deep on the subject, below follows a list of key settings for a well performing database server (note that some of these might be omitted from your configuration by default.):

• query-cache-type: (controls whether Qcache is on/off. Default is off)
• query-cache-size: (Specifies the size of your Qcache)
• key_buffer_size: (A cache mechanism to keep the most frequently accessed table blocks in memory)
• read_buffer_size: (Each request that performs a sequential scan of a table allocates a read buffer.)
• table_cache: (A combination of read buffer and key buffer, but only keeps critical data)
• wait_timeout: (The number of seconds the server waits for activity on a non-interactive connection before closing it)
• interactive_timeout: (I’ll let you take a stab at what this means ;))
• connect_timeout: (The number of seconds that the MySQLd server waits for a connect packet before sending a kill signal)
• skip-innodb: (Sets whether innodb is allowed or not. More on this below)

You could discuss the optimal setting for these indefinitely, so I’m just going to leave it with this: MySQLd is programmed to run in very low memory environments. If it is allowed more memory, it will run better. Memory also means cache, cache generally means improved speed. Most servers have well beyond the default memory cap that MySQLd takes up out of the box, and unless you are on a server with 128MB or less available memory, these following settings should results in a major performance boost for your database applications. Do note that if the setting doesn’t exist at all in your config file, you can just add it:

max_connections = 400
key_buffer = 16M
sort_buffer_size = 2M
read_buffer_size = 1M
table_cache = 1024
interactive_timeout = 15
connect_timeout = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
skip-innodb

It’s VERY IMPORTANT to notice that if you run innodb applications you need to take out that last line (for Plesk users: Plesk uses innodb for user management!) otherwise they will cease to function! Save and restart MySQLd;

/etc/init.d/mysqld stop
/etc/init.d/mysqld start

Table optimization:

Apart from configuration, another key issue to smooth SQL sailing is keeping your tables repaired and optimized. You can do this regularly through phpmyadmin, or you can set up a script to do it for you. Optimizing a table is most beneficial if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns, which is most of them). Deleted rows are maintained in a linked list and new INSERT queries reuse old row positions. This causes a slowdown in response time as the list grows larger. You can Optimize the table to kill the dead positions and to defragment the data file. This process will also free up storage space. On eCommerce sites or community sites or active blogs, Optimizing the tables will have a significant impact in the response time of your MySQLd.

Misc performance tips

Lastly, let me just touch briefly on table design. There’s an excellent article on database partitioning, something every MySQL developer should read. You can find it here. Here are a couple of quick hints on how to design and query your database for maximum performance;

• Don’t use DISTINCT when you could use GROUP BY
• Make similar queries identical/very similar so Qcache is used
• Avoid wildcards at the start of LIKE queries
• Don’t avoid table joins, they are faster than denormalization
• Use LOAD DATA instead of INSERT

In closing, don’t forget to optimize!