# Optimising MySQL

As per this post, it looks like I'm having some serious issues with MySQL optimisation.

Unfortunately, I have no idea where to start!

I'm running a DirectAdmin-based CentOS dedicated server. It's an Intel Quad Core 3Ghz with 8Gb RAM. I run a number of websites on it, but I believe only my main website gets any real traffic.

The website that I'm concerned about is hitting around 30,000 unique visitors/250,000 hits per week. It's a WordPress-based website.

My my.cnf is basically empty. I increased the max_connections to deal with the initial problem (website keeps hitting "Database max connections" errors as per post linked earlier) and that's it.

I've tried to run two scripts on the server to determine how I can optimise my settings. One of them didn't work, but the other suggested the following:

• Query cache is supported but not enabled; Perhaps you should set the query_cache_size
• You have had 11025 queries where a join could not use an index properly; You should enable "log-queries-not-using-indexes" then look for non indexed joins in the slow query log.
• You have a total of 834 tables; You have 528 open tables; Current table_cache hit rate is 8%, while 132% of your table cache is in use; You should probably increase your table_cache
• You should probably increase your table_definition_cache value.
• Current Lock Wait ratio = 1 : 529; You may benefit from selective use of InnoDB.
• Current max_heap_table_size = 16 M; Current tmp_table_size = 16 M; Of 107071 temp tables, 25% were created on disk; Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables
• You have 1290 out of 1145245 that take longer than 2.000000 sec. to complete
• Current max_connections = 500; Current threads_connected = 501; You should raise max_connections

The full source can be found here.

Unfortunately, I don't really know what a lot of this does, and I don't want to walk into it blindly.

Does anyone have any advice for optimising my MySQL installation?

• Is there an index on {post_name, post_type, post_date}? If not, add it and see if that fixes it. – RBarryYoung Jul 15 '12 at 18:37

Coming at this from the DB point of view, rather than WordPress.

# Executive Summary

For each recommendation your optimiser script has generated, check out the appropriate MySQL documentation to see if it will impact your setup. Then tweak your my.conf little by little.

Your overall objective is for MySQL to load as much as it can into memory and not hit the disk. So increasing various caches will probably help. As long as you don't exceed the physical memory in your server (you need to take into account other processes running on the server). Then, make sure your tables are indexed appropriately.

# Your Specific Issues

But to address each of the items you've listed:

Query cache is supported but not enabled; Perhaps you should set the query_cache_size

The query cache is like a big hash table of Select-Statement -> Result-Table. MySQL checks to see if the same query exists in the cache and returns the cached result without re-running the query. If you enable it, run SHOW VARIABLES to see how well utilised it is (having a huge cache which isn't used is just a waste of memory). My experience of the query cache is that it seems really good in theory, but didn't provide much help in practice.

You have had 11025 queries where a join could not use an index properly; You should enable "log-queries-not-using-indexes" then look for non indexed joins in the slow query log.

Indexes are what makes or breaks a database. Enable the slow query log and use EXPLAIN to see what queries are not using indexes. Fix the slow ones first.

You have a total of 834 tables; You have 528 open tables; Current table_cache hit rate is 8%, while 132% of your table cache is in use; You should probably increase your table_cache

MySQL ISAM databases exist as pairs (I think) of files on disk, but MySQL doesn't keep them open all the time, only ones used recently. The table_cache setting controls how many files it will keep open. This appears to be related to the number of connections, so be careful setting this too high, but it seems you have stacks of memory available so increase this until all tables are cached.

Current Lock Wait ratio = 1 : 529; You may benefit from selective use of InnoDB.

MyISAM tables are great for reading, but whenever you UPDATE, INSERT or DELETE from them MySQL locks the whole table. So if, for example, you have a Page table with a HitCount field which is incremented whenever the page is loaded, the entire Page table is locked and no other connections can read from it. I've seen some particular nasty combinations of read / write queries which would lock tables for minutes or even hours! Effectively killing the site.

InnoDB isn't as fast at reading, but supports more granular write operations (only locking the one record being updated). So is a better fit for tables which are written to more frequently. Converting tables with large numbers of UPDATE, INSERT and DELETE operations to InnoDB may decrease locking and increase performance. Many apps which use MySQL default to InnoDB across the board for this very reason.

I think you need to drop the old MyISAM tables and re-create them as InnoDB, so there will be downtime involved.

Apparently an ALTER TABLE statement is all you require to change the engine type. Although full table locks will be required, so you'll have some time when you can't run queries.

I don't know if WordPress assumes InnoDB or MyISAM tables. Please check WordPress before altering the tables' engine.

Current max_heap_table_size = 16 M; Current tmp_table_size = 16 M; Of 107071 temp tables, 25% were created on disk; Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables

MySQL requires memory to do sorting (ORDER BY), JOINs and other operations involving large chunks of data, but only up to a certain limit, after that limit the operation spills out onto disk (so that one giant ORDER BY doesn't use up gigabytes of memory which could better be spent doing other things). Increasing max_heap_table_size and tmp_table_size means less operations run on the slow disk and more in fast memory (a discussion about these variables). 64M should be large enough for most cases, and making these too big means you're just wasting memory.

You have 1290 out of 1145245 that take longer than 2.000000 sec. to complete

Use the slow query log to figure out what these slow queries are. Although that ratio (0.11%) is pretty low, there may be a few really slow queries which are causing bigger problems.

Current max_connections = 500; Current threads_connected = 501; You should raise max_connections

Once you have more threads_connected than max_connections new connections are rejected. Increase max_connections (as you already have done).

As you can see, its not always obvious what to do without knowing what sorts of queries WordPress is generating.

• -1 Wow! Minus 1 for saying: "I think you need to drop the old MyISAM tables and re-create them as InnoDB..." You do NOT drop tables to change engines. A simple "ALTER table ENGINE=InnoDB" will do! – randomx Jul 19 '12 at 15:21
• @randymelder - I stand corrected! – ligos Jul 20 '12 at 1:04

Cashe is your friend! the more tables you can hold in cashe, the less you have to hit the hard drive for.

Your main performance problem is that people hitting your databases, have to wait for the hard drive to retreave them instead of the tables being in Cashe. This is ok in most situations, but when you have multiple people connected and retreaving tables, the I/O queue starts to get backed up and that is when you get your slowdown.

I'm more interested in the code that accesses the data. It sounds like your application is doing a bad job of handling connections, to the point where you reach the max allowed.

It may also be that you are missing Indexes and certain queries just take too long to execute. The delayed execution may cause 500 concurrent connections easily.

However 500 concurrent connections to be occurring so often based on your traffic, sounds like a bottleneck (and the two mentions above are the most common).

Wordpress does not specify a table engine, so it will use the default or whatever you have set as your default in your my.cnf file. If you have no setting in your my.cnf, then your default will be MyISAM for MySQL < 5.5 and InnoDB for MySQL >= 5.5.

The Generic (yet valid) Advice: 90% of your performance gain (from tuning) will come from adjusting your default engine's index/paging buffer. I recommend reading: http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

If you are running < MySQL 5.5, raise your key_buffer to 80% free RAM. If you are running >= MySQL 5.5, raise your innodb_buffer_pool to 80% of free RAM.

If you are 99% reads, then Query_cache will play a role. Something like:

query_cache_size = 32M
query_cache_type = 1
query_cache_limit= 512K


If you are balanced reads and writes, just turn query cache off because it will slow you down because the query cached is invalidated with every write.

query_cache_type = 0


However, if you've got queries that aren't hitting indexes, that's a code review issue. You have to weigh the cost/benefit of adding an index to speed up a query that may happen once in a while or happen every second. Your analysis will tell the tale there.