MySQL Performance Tuning Tips and Tricks

A few of the basic settings to get a better performance out of your MySQL server.

We are assuming, of course, that the server role is limited to just running MySQL and that is has sufficient memory and disk to meet the application needs. The example in this document uses 16G of RAM.

Monitoring Performance

MySQL as some logging features to identify slow queries. This configuration logs queries that take longer that 2 seconds to complete.

log_slow_queries  = /var/log/mysql/mysql-slow.log
long_query_time   = 2
log-queries-not-using-indexes

Then use the tools to evaluate the logs. Using the above configuration the logs will show both slow and non-indexed queries which may be noise.

mysqldumpslow /var/log/mysql/mysql-slow.log 

Count: 1  Time=0.42s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), debian-sys-maint[debian-sys-maint]@localhost
  select count(*) into @discard from `information_schema`.`PARTITIONS`
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), debian-sys-maint[debian-sys-maint]@localhost
  select count(*) into @discard from `information_schema`.`TRIGGERS`

Additionally, external tools should be used to measure system resource consumption (RAM, CPU, HDD, NET, etc).

Increase Memory Allocations

Generally setting the key_buffer to 40% system ram, it's shared across whole server. Increase query caches, to keep them around a bit longer. Per connection buffers set to comfortable values, too large can drag a machine down when connection count is high.

The use of the SHOW STATUS and/or SHOW FULL PROCESSLIST.

We like to give lots of space to MySQL.

key_buffer_size         = 6553M
innodb_buffer_pool_size = 6553M

See the key_buffer_size and innodb_buffer_pool_size docs for more information.

query_cache_limit = 2M
query_cache_size  = 64M
query_cache_type  = 1

sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=1M

thread_concurrency  = #cores *2

Table Cache

According to the MySQL team:

table_cache is related to max_connections. For example, for 200 concurrent running connections, you should have a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.

For our an example system that has about 164 concurrent connections and the maximum join count of eight our setting becomes

# 164 * 8 + 20% = 1574
table_cache = 1600

To find the value for thread_concurrency use the following shell snippet.

~ # echo $(( $(grep processor /proc/cpuinfo |wc -l) * 2 ))
16