How to tune performance after MySQL server is installed

The original Peter Zaitsev
Original source: Http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation
Translator: Ye Jinlong, reprint please specify the translator and source, and can not be used for commercial purposes, offenders must investigate.

My favorite question during interview for people to work as MySQL DBAs or is involved with MySQL performance in some I s to ask them what should is tuned in MySQL Server straight after installation, assuming it is installed with default set Tings.

When interviewing MySQL DBAs or those who are planning to do MySQL performance optimization, my favorite question is: What adjustments should be made after the MySQL server is installed with the default settings?

I ' m surprised how many people fail to provide any reasonable answer to this question, and how many servers is where in WI LD which is running with default settings.

I was surprised how many people couldn't give a reasonable answer to the 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 is really important for most common W Orkload. After your get these settings right other changes would most commonly offer only incremental performance improvements.

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

Key_buffer_size –very Important If you use MyISAM tables. Set up to 30-40% of available memory if your use MyISAM tables exclusively. Right size depends in amount of indexes, data size and Workload–remember MyISAM uses OS cache to cache the data so you n Eed to leave memory for it as well, and data can is much larger than indexes in many cases. Check However if all of key_buffer are used over time–it are not rare to see Key_buffer being set to 4G while combined siz E of. MYI files is just 1GB. This would is just a waste. If You use few MyISAM tables you'll want to keep it lower but still at least 16-32MB so it's large enough to accommodate indexes for temporary tables which is created on disk.

Key_buffer_size – This is important for the MyISAM table. If you use only the MyISAM table, you can set it to the 30-40% of available memory. A reasonable value depends on the size of the index, the amount of data, and the load-remember, the MyISAM table caches the data using the operating system's cache, so you need to set aside some memory for them, and in many cases the data peso is mostly. Nonetheless, it is always necessary to check that all key_buffer are exploited-. MYI files are only 1GB, while Key_buffer is set to 4GB is very rare. This is a waste of time. If you rarely use the MyISAM table, you also keep the key_buffer_size below 16-32MB to accommodate the temporary table index that is given to the disk.

Innodb_buffer_pool_size this is very important variable to tune if you ' re using innodb tables. Innodb tables is much more sensitive to buffer size compared to MyISAM. MyISAM kind of OK with default key_buffer_size even with large data set but it'll crawl with default Innodb_buf Fer_pool_size. Also 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 the small data set and it is not going to grow dramatically does not oversize I Nnodb_buffer_pool_size you might find better use for memory available.

innodb_buffer_pool_size– This is very important for the InnoDB table. InnoDB is more sensitive to buffering than the MyISAM table. MyISAM can be run under the default Key_buffer_size settings, but InnoDB is snail-like under the default Innodb_buffer_pool_size settings. Since InnoDB caches both data and indexes, there is no need to leave too much memory on the operating system, so if you only need to use InnoDB, you can set it up to 70-80% of usable memory. Some of the rules that apply to Key_buffer are-if you have a small amount of data and do not burst, you do not need to set the innodb_buffer_pool_size too large.

Innodb_additional_pool_size This one does not really affect performance too much, at least in OS with decent memory alloca Tors. Still you might want to has it 20MB (sometimes larger) so what can see how much memory Innodb allocates for misc needs.

innodb_additional_pool_size– This option has little impact on performance, at least on operating systems that have almost enough memory to allocate. But if you still want to set it to 20MB (or larger), you need to look at how much memory InnoDB other needs to allocate.

Innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance and increase recovery times so be careful. I normally use the values 64m-512m depending on server size.

Innodb_log_file_size is important in the case of high write loads, especially large data sets. The larger the value, the higher the performance, but note that the recovery time may be increased. I often set it to 64-512MB, depending on the size of the server.

Innodb_log_buffer_size Default for the kind of OK for many workloads with medium write load and shorter transactio Ns. If you have an update activity spikes however or work with BLOBs a lot of might want to increase it. Do not set the it too high however as it would be waste of memory–it are flushed every 1 sec anyway so do not need space For more than 1 sec worth of updates. 8MB-16MB is typically enough. Smaller installations should use Smaller values.

Innodb_log_buffer_size default settings in the case of moderate-strength write loads and shorter transactions, server performance is also possible. If there is a peak update operation or a large load, you should consider increasing its value. If its value is set too high, memory may be wasted-it refreshes every second, so there is no need to set the required memory space for more than 1 seconds. Usually the 8-16MB is enough. The smaller the system, the less its value.

Innodb_flush_logs_at_trx_commit crying about InnoDB being slower than MyISAM? You probably forgot to adjust this value. Default value of 1 would mean each update transaction commit (or each statement outside of transaction) would need to flush Log to the disk which are rather expensive, especially if you don't have a Battery backed up cache. Many applications, especially those moved from MyISAM tables is OK with value 2 which means does not flush log to the disk But only flush it to OS cache. The log is still flushed to the disk each second so do 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. The Value 2 is cause data loss with full OS crash.

Innodb_flush_logs_at_trx_commit is InnoDB 1000 times times slower than MyISAM and head big? You may have forgotten to modify this parameter. The default value is 1, which means that each committed update transaction (or statements outside of each transaction) is flushed to disk, which is quite resource intensive, especially when there is no battery backup cache. Many applications, especially those transformed from MyISAM, set its value to 2, which is to not flush the log to disk, but only to the operating system's cache. Logs are still flushed to disk every second, so there is usually no loss of 1-2 updates per second. If you set it to 0, it's a lot faster, but it's also relatively insecure. When the-mysql server crashes, some transactions are lost. Set to 2 command to lose the part of the transaction that was flushed to the operating system cache.

Table_cache–opening tables can be expensive. For example MyISAM tables Mark MYI the header to the Mark table as currently in use. Want this to happen so frequently and it's typically best to size your caches so it's large enough to keep MOS T of your tables open. It uses some OS resources and some memory but for modern hardware it's typically not the problem. 1024x768 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.

Table_cache-the cost of opening a table can be significant. For example MyISAM the Myi file header flag that the table is in use. You certainly don't want this to happen too often, so it's often necessary to increase the number of caches to maximize the cache of open tables. It needs to use the operating system's resources and memory, which is certainly not a problem for the current hardware configuration. If you have more than 200 tables, it might be appropriate to set it to 1024 (each thread needs to open the table) and increase its value if the number of connections is larger. I've seen cases set to 100,000.

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 have large jumps in amount of concurrent connections and I see fast growth of
threads_created variable I boost it higher. The goal is does not and has threads created in normal operation.

The cost of creating and destroying thread_cache-threads can be significant, as each thread is required to connect/disconnect. I usually set it at least to 16. If there is a large number of hops concurrent connections in the application and the value of threads_created is larger, then I will increase its value. It is intended to create no new threads in the usual operation.

Query_cache If Your application is the read intensive and you does not has application level caches the can be great help. Do not set it too large as it is 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 the well used. For certain workloads cache hits ratio is lower than would justify have it enabled.

Query_cache-This is useful if your application has a lot of read and no application-level caching. Do not set it too big, because it also requires a lot of overhead to maintain it, which can cause MySQL to become slower. Typically set to 32-512MB. It's a good idea to track a period of time to see if it's working well. Under a certain load pressure, if the cache hit rate is too low, enable it.

Note:as you can see all of these is global variables. These variables depend on hardware and mix of storage engines, while per session variables is typically workload specific . If you have the simple queries there are no reason to increase sort_buffer_size even if you have 64GB 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, these global tables are different depending on the hardware configuration and different storage engines, but session variables are usually set according to different loads. If you have only a few simple queries, then you don't need to increase the value of sort_buffer_size, although you have 64GB of memory. Maybe it will degrade performance.
I usually set session variables after analyzing the system load.

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

P.s,mysql's distribution already contains various MY.CNF sample files that can be used as configuration templates. Usually this is much better than the default settings you use.

How to tune performance after MySQL server is installed