Tuning MySQL Server.

[Alexey]

Lots of variables can be tuned up in MySQL Servers but only few of them are important for very common workload. Once you get these settings right other changes will most commonly offer only good performance improvements.

key_buffer_size - It's an important thing if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Correct size depends on the amount of indexes, workload and data size - remember MyISAM uses OS cache to cache the data so you need to leave memory for it , and in many cases data can be much larger than indexes. However if all of key_buffer is used over time - it's not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use some MyISAM tables you will want to keep it lower but still at least 16-32Mb so it's large to accommodate indexes for temporary tables which is created on disk.

innodb_buffer_pool_size This is also a very important variable to tune if you are using Innodb tables. Innodb tables are much sensitive to buffer size as compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size.Innodb buffer pool caches both data and index pages so you don't need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply - if you have small data set and it's not going to grow dramatically don't oversize innodb_buffer_pool_size you might find better use for memory available.

innodb_additional_mem_pool_size it doesn't really affect the performance much, at least on Operating System 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 is also important for writing an intensive workloads especially for e data sets. Larger sizes offer better performance but increase recovery times so be careful.We 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_log_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. 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.

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. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.

thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.

query_cache_size If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.

All the above mentioned variables depend on the hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. If you will do anything else you may decrease the overall performance.

Generally users leave every session variable tuning to next step after they can analyze workload.