How to adjust the performance after installing the MySQL server

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 shoshould be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

When interviewing MySQL DBA or those who intend to optimize MySQL performance, my favorite question is: what adjustments should I make after the MySQL server is installed according to the 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.

I was surprised how many people could not give a reasonable answer to this question, and how many servers were running under the 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.

Although you can adjust the variables on many MySQL servers, only a few variables are really important under most common workloads. If you set these variables correctly, modifying other variables can only improve the system performance.

Key_buffer_size-Very important if you use MyISAM tables. set up to 30-40% of available memory if you use MyISAM tables exclusively. right size depends on amount of indexes, data size and workload-remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in container cases. check however if all of key_buffer is used over time-it is not rare to see key_buffer being set to 4G while combined size. MYI files is just 1 GB. this wocould be just a waste. if you use few MyISAM tables you'll want to keep it lower but still at least 16-32 Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.

Key_buffer_size-This is very important for MyISAM tables. If you only use the MyISAM table, you can set it to 30-40% of the available memory. A reasonable value depends on the index size, data volume, and load. Remember, the MyISAM Table uses the operating system cache to cache data. Therefore, you need to leave some memory for them, in many cases, the data is much larger than the index. However, it is always necessary to check whether all key_buffer files are used -- The. MYI file only has 1 GB, while the key_buffer is set to 4 GB. This is a waste. If you seldom use the MyISAM table, keep the key_buffer_size smaller than 16-32 MB to meet the requirements for temporary table indexes on the disk.

Innodb_buffer_pool_sizeThis is very important variable to tune if you're using Innodb tables. innodb tables are much more sensitive to buffer size 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. also Innodb buffer pool caches both data and index pages so you do not 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 is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

Innodb_buffer_pool_size-This is very important for Innodb tables. Compared with MyISAM tables, Innodb is more sensitive to buffering. MyISAM can run in the default key_buffer_size setting. However, Innodb is similar to snail bait in the default innodb_buffer_pool_size setting. Because Innodb caches data and indexes, there is no need to leave too much memory for the operating system. Therefore, if you only need Innodb, you can set it to up to 70-80% of available memory. Some rules apply to key_buffer: If your data volume is small and does not increase rapidly, you do not need to set innodb_buffer_pool_size too large.

Innodb_additional_pool_sizeThis one does not really affect performance too much, at least on OS with decent memory allocators. still you might want to have it 20 MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

Innodb_additional_pool_size-This option does not affect performance much, at least on an operating system with almost enough memory to allocate. However, if you still want to set it to 20 MB (or larger), you need to check the other Innodb memory to be allocated.

Innodb_log_file_sizeVery 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 64 M-512 M depending on server size.

Innodb_log_file_sizeIt is important to write data at a high load, especially for large datasets. The larger the value, the higher the performance, but note that the recovery time may increase. I often set it to 64-512 MB, which is different from the server size.

Innodb_log_buffer_sizeDefault for this one is kind of OK for processing 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 wocould 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. 8 MB-16 MB are typically enough. smaller installations shocould use smaller values.

Innodb_log_buffer_sizeBy default, the server performance is acceptable when the write load is moderate and the transaction is short. If there is a peak update operation or a large load, you should consider increasing the value. If its value is set too high, memory may be wasted-it will refresh once every second, so you do not need to set the memory space more than 1 second. Usually 8-16 MB is enough. The smaller the system, the smaller its value.

Innodb_flush_logs_at_trx_commitCrying 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. 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 wocould 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.

Innodb_flush_logs_at_trx_commitIs it because Innodb is 1000 times slower than MyISAM? Maybe you forgot to modify this parameter. The default value is 1, which means that each commit of an update transaction or a statement other than each transaction will be refreshed to the disk, which is quite resource-consuming, especially when no battery backup cache is available. Many applications, especially those transformed from MyISAM, set the value to 2, that is, do not refresh the log to the disk, instead, it is only refreshed to the operating system cache. Logs are still refreshed to the disk every second, so the consumption of 1-2 updates per second is usually not lost. If it is set to 0, it will be much faster, but it is relatively insecure-some transactions will be lost when the MySQL server crashes. Set to 2 to direct the part of the transaction that is lost and refreshed to the operating system cache.

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 attached connections or joined tables increase it larger. I 've seen values over 100.000 used.

Table_cache-- Opening a table may have a high overhead. For example, MyISAM marks the MYI file header that the table is in use. You certainly do not want this operation to be too frequent. Therefore, you usually need to increase the number of caches so that the opened tables can be cached to the maximum extent. It requires the resources and memory of the operating system, which is of course not a problem for the current hardware configuration. If you have more than 200 tables, it may be better to set it to 1024 for each thread to open the Table). If the number of connections is large, increase the value. I have seen a 100,000 error.

Thread_cacheThread 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
Threads_CreatedVariable I boost it higher. The goal is not to have threads created in normal operation.

Thread_cache-- The overhead of thread creation and destruction may be large because connection/disconnection is required for each thread. I usually set at least 16. If the application has a large number of skip concurrent connections andThreads_CreatedThe value is also relatively large, so I will increase its value. It does not need to create a new thread in common operations.

Query_cacheIf 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 32 M to 512 M 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 wocould justify having it enabled.

Query_cache-- This is useful if your application has a large number of reads without application-level caching. Don't set it too large, because it also requires a lot of overhead to maintain it, which causes MySQL to slow down. Usually set to 32-512 Mb. After setting, it is best to track for a period of time to check whether the operation is good. If the cache hit rate is too low under a certain load, enable it.

Note:As you can see all of these are global variables. these variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. if you have simple queries there is no reason to increaseSort_buffer_sizeEven if you have 64 GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.

Note:As you can see, the global table volumes are different based on hardware configurations and different storage engines, but session variables are usually set based on different loads. If you only have some simple queries, you do not need to addSort_buffer_sizeEven if you have 64 GB memory. Poor performance may be reduced.
I usually set session variables after analyzing the system load.

P.S Note MySQL distribution contains bunch of sample my. cnf files which may be great templates to use. Typically they wocould already be much better than defaults if you chose correct one.

P.S. The MySQL release contains various my. cnf sample files, which can be used as configuration templates. Generally, this is much better than the default setting.