Performance tuning after default installation of MySQL server

When interviewing MySQL DBA or those planning to do MySQL performance optimization, my favorite question is: MySQL server in accordance with the default settings after installation, what should be done to adjust it?

To my surprise, how many people can not give a reasonable answer to this question, and how many servers are running under the default settings.

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

Key_buffer_size-This is very important for the MyISAM table. If you are using 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 uses the operating system's cache to cache the data, so you need to set aside some of the memory for them, and in many cases the data peso is much more. However, it is always necessary to check whether all the Key_buffer are exploited--. Myi files are only 1GB, and Key_buffer is set to 4GB is very rare. It's too wasteful to do so. If you rarely use MyISAM tables, then keep key_buffer_size below 16-32MB to accommodate the temporary table indexes that are given to the disk.

Innodb_buffer_pool_size-This is very important for the InnoDB table. InnoDB is more sensitive to buffering than MyISAM tables. MyISAM can be run under the default Key_buffer_size settings, however InnoDB is like a snail in the default innodb_buffer_pool_size settings. Because InnoDB caches data and indexes without leaving the operating system with too much memory, you can set it up to 70-80% usable memory if you only need to use InnoDB. Some of the rules that apply to Key_buffer are-if your data is small and not exploding, you don't have to set the innodb_buffer_pool_size too big.

Innodb_additional_pool_size-This option does not have much impact on performance, at least on an operating system with almost enough memory to allocate. But if you still want to set it to 20MB (or more), you need to look at the amount of memory other InnoDB need to allocate.

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 recovery time may be increased. I often set it to 64-512MB, depending on the size of the server.

Innodb_log_buffer_size default settings for medium-intensity write load and shorter transactions, server performance is also available. If there is a peak update operation or a large load, you should consider increasing its value. If its value is set too high, it may waste memory-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 smaller its value.

Is innodb_flush_logs_at_trx_commit a InnoDB 1000 times times slower than MyISAM? Maybe you forgot to change the parameter. The default value is 1, which means that each submitted update transaction (or statements outside of each transaction) is flushed to disk, which is quite resource-intensive, especially when there is no battery-standby cache. Many applications, especially those from MyISAM, set the value to 2, which means that the log is not flushed to disk, but only to the operating system's cache. Logs are still flushed to disk per second, so there is usually no loss of 1-2 updates per second. If set to 0 is much faster, but also relatively insecure-the MySQL server crashes will lose some transactions. Set to 2 command to lose that part of the transaction that is flushed to the operating system cache.

Table_cache-the overhead 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 be too frequent, so it's usually a lot more cache, so it's enough to cache open tables. It needs 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 if the number of connections is larger then increase its value. I have seen the case set to 100,000.

Thread_cache-The cost of creating and destroying threads can be significant because each thread needs to be connected/disconnected. I'm usually at least set to 16. If there is a large number of jumps and concurrent connections in the application and the value of threads_created is larger, then I will increase its value. It is designed to not create a new thread in the usual operation.

Query Cache-This is useful if your application has a large number of reads and no application-level caching. Don't set it too large, because it costs a lot to maintain it, which can cause MySQL to slow down. usually set to 32-512MB. After setting up, it's best to track for a while to see if it's working well. Under certain load pressure, if the cache hit rate is too low, enable it.

Note: As you can see, these global tables are different depending on the hardware configuration and different storage engines, but the session variables are usually set based on different workloads. If you have only a few simple queries, there is no need to increase the value of sort_buffer_size, although you have 64GB of memory. Bad luck may degrade performance.

I usually set the session variables after analyzing the system load.

The MySQL release already contains a variety of my.cnf sample files and can be used as a configuration template. This is usually a lot better than the default settings you use.