MySQL database memory tuning practices

The following articles mainly describe the MySQL database memory tuning. We all know that there are sometimes some bad things in the actual operations in the MySQL database. In order to improve its practical application, today, we specifically talk about the actual operations of MySQL database memory optimization.

Key_buffer_size-this is very important for the MyISAM table. 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, MyISAM tables use the operating system cache to cache data. Therefore, you need to leave some MySQL database memory for them. In many cases, the data is much larger than the index. However, you need to always check whether all key_buffer is used.

The. MYI file only has 1 GB, but 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_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 MySQL database of the operating system. Therefore, if you only need Innodb, you can set up to 70-80% of available memory. 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_size-this option has little impact on performance, 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_size is very important for high write loads, 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_size is set by default to enable server performance 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 too high, the MySQL database memory may be wasted. It refreshes every second, so you do not need to set the memory space that exceeds 1 second. Usually 8-16 MB is enough. The smaller the system, the smaller its value.

Is innodb_flush_logs_at_trx_commit 1000 times slower than MyISAM than Innodb? 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 are 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-the overhead of opening a table may be high. 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 operating system resources and MySQL database memory, 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_cache-the overhead of thread creation and destruction may be large because connection/disconnection of each thread is required. I usually set at least 16. If the application has a large number of skip concurrent connections and the value of Threads_Created is large, I will increase the value. It does not need to create a new thread in common operations.

Query_cache-this is useful if your application has a large number of reads without application-level caches. 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.