Author: Arnold (see Links)
The company's website visits are getting bigger and bigger. MySQL naturally becomes a bottleneck, so I have been studying Mysql optimization, the first step is to optimize the mysql system parameters, as a large number of visitors (200,000 days The above) database system, it is impossible to expect MYSQL default system parameters to make MySQL run very smooth.
I think the following system parameters is a comparison: the following system parameters are comparison:
(1), Back_LOG: Requires the number of connections available in Mysql. When the primary mysql thread gets a lot of connection requests in a short period of time, this works, then the main thread takes some time (despite short) check the connection and start a new thread. The back_log value indicates how many requests can be present in the stack in the short time before mySQL temporarily stops answering the new request. Only if you expect to have a lot of connections in a short period of connection, you need to add it, in other words, this value is the size of the listening queue that comes to the TCP / IP connection. Your operating system has its own restrictions on this queue. Trying to set the restriction of Back_LOG higher than your operating system will be invalid. When you observe your host process list, find a large number of 264084 | Unauthenticated user | xxx.xxx.xxx.xxx | Null | Connect | NULL | Login | Null When the process is connected, it is necessary to increase the value of Back_log. The default value is 50, I change it to 500.
(2), interactive_timeout: The server is waiting for a second number of seconds to move on an interactive connection before turning off it. A interactive customer is defined as a customer who uses the client_interactive option for mysql_real_connect (). The default value is 28800, I change it to 7200.
(3), key_buffer_size: Index block is buffered and shared by all threads. Key_buffer_size is a buffer size for index blocks, increasing it to get a better index (written to all readings and more), and you can afford to afford. If you make it too big, the system will start switching and really slowing down. The default value is 8388600 (8m), my MySQL host has 2GB of memory, so I change it to 402649088 (400MB).
(4), Max_Connections: The number of customers allowed. Increase this value increases the number of file descriptors required by mysqld. This number should increase, otherwise you will often see the Too Many Connections error. The default value is 100, I change it to 1024.
(5), RECORD_BUFFER: The thread that performs a sequential scan is assigned a buffer for each table of its scan. If you do a lot of order scans, you may want to add this value. The default value is 131072 (128K), I change it to 16773120 (16M)
(6), sort_buffer: The thread that needs to be sorted allocates a buffer of the size. Add this value to accelerate the ORDER BY or Group BY operation. The default value is 2097144 (2M), I change it to 16777208 (16M).
(7), Table_Cache: Top the number of tables for all threads. Increase this value to increase the number of file descriptors required by mysqld. MySQL requires two file descriptors for each unique open table. The default value is 64, I change it to 512.
(8), thread_cache_size: The number of saved threads can be multiplexed. If there is, the new thread gets from the cache. If there is space when disconnecting, the customer's wiring is in the cache. If there are many new threads, in order to improve performance, this variable can be improved. This variable can be seen by comparing the variables of the Connections and Threads_created status. I set it to 80. (10), wait_timeout: The server is waiting for the number of seconds to act on a connection before turning off. The default value is 28800, I change it to 7200.
Note: The adjustment of the parameters can be implemented by modifying the /etc/my.cnf file and restarting MySQL. This is a more cautious work. The above results are only some of my opinions, you can further modify according to your own horses (especially memory size)
