How to adjust the performance after installing the MySQL server

Original post: article.pchome.netcontent-138561.html MySQL server after installation of how to adjust the performance CNET Chinese PChome.net Author: PeterZaitsev Source: www. mysqlperformanceblog. com20060929what-to-tune-in-mysql-server-after-installation ON THE SURFACE

Original post: http://article.pchome.net/content-138561.html MySQL server after the installation of how to adjust the performance CNET Chinese PChome.net Author: Peter Zaitsev Source: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation ON THE SURFACE

Original post: http://article.pchome.net/content-138561.html
How to adjust the performance after installing the MySQL server

CNET China-PChome. net

Author: Peter Zaitsev

Source: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation

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

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-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, 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_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_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 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.

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 the update transaction (or a statement other than each transaction) will be refreshed to the disk, which is quite resource-consuming, especially when there is no battery backup cache. 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 -- 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 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 appropriate to set it to 1024 (each thread needs to open the Table). If the number of connections is large, it will increase its value. I have seen a 100,000 error.

Thread_cache -- the overhead of thread creation and destruction may be high, 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.

Note: As you can see above, the global table volume is different based on hardware configuration 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 increase the sort_buffer_size value, even though you have 64 GB memory. Poor performance may be reduced.

I usually set session variables after analyzing the system load.

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.