My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.
I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.
Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.
table_cache
Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. Use the tuning-primer.sh (http://www.day32.com/MySQL/) script to evaluate the current value.
query_cache_size
If your application is read intensive and you do not have application level caches this can be great help. Use the tuning-primer script to evaluate the size after you enable it. A good starting point may be 8M.
The query_cache_size will be aligned to the nearest 1024 byte block. The value reported may therefore be different from the value that you set.
If the query cache size is greater than 0, then query_cache_type variable influences how it works. This variable can be set to the following values:
query_cache_type
* A value of 0 or OFF prevents caching or retrieval of cached results.
* A value of 1 or ON allows caching except of those statements that begin with SELECT SQL_NO_CACHE.
* A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.
thread_cache
mysql> SHOW STATUS LIKE ‘Thread%’;
mysq> show status like ‘Connections’;Threads_created details the number of threads that have been created since the MySQL server started, and Connections is the total number of client connections to the MySQL server since startup. To work out the thread cache hit ratio, we use this calculation:100 – ((Threads_created / Connections) * 100)
100 – ((10 / 78298) * 100) = ~99.987 Thread cache hit ratioThe ideal situation is to get Threads_created as close as possible to thread_cache_size – no new connections having to wait for new thread allocation – staying as close to around a 99% hit ratio as you can.
mysql> show status like ‘Select_full_join’;
The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
ratio of disk tmp tables vrs in memory tmp tables (tmp_table_size)
Handler_read_rnd
mysql> show status like ‘Handler_read_rnd%’;
The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that don’t use keys properly.
Handler_read_rnd_next
The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
key_buffer_size
Very important if you use MyISAM tables. key_buffer_size is important for MyISAM temporary tables performance to avoid OS writes, see this page for more detail. Again, evaluate the value using the tuning-primer.sh script.
innodb_buffer_pool_size
This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM.
innodb_additional_mem_pool_size
This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.
innodb_log_file_size
Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.
innodb_log_buffer_size
Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.
innodb_flush_logs_at_trx_commit
Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.
