MySQL performance Settings
- 2020-05-06 11:44:07
- OfStack
MySQL performance setting
Website traffic is more and more big, the MySQL natural become a bottleneck, so recently I have been studying MySQL optimization, the first step is naturally thought of MySQL system parameter optimization, as one of the sites with substantial traffic (day more than 200000 person-times) database system, can't count on MySQL default system parameters can let MySQL runs very smoothly.
by looking up information on the network and their own attempts, I think the following system parameters are more critical:
(1), back_log:
requires the number of connections available to MySQL . This works when the main MySQL thread gets a lot of connection requests in a very short time, and then the main thread takes some time (albeit a short time) to check the connection and start a new thread.
The back_log value indicates how many requests can be stored on the stack in the short time before MySQL temporarily stops answering new requests. Only if you expect to have many connections in a short time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limit on the queue size. attempts to set back_log higher than your operating system limit will be invalid.
when you look at your host process list, Found a large number of 264084 | unauthenticated user | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | The value of back_log is increased when the NULL process is connected. The default value is 50. I'll change it to 500.
(2), interactive_timeout:
The number of seconds an server waits for action on an interactive connection before shutting it down. An interactive customer is defined as one that USES the CLIENT_INTERACTIVE option for mysql_real_connect(). The default value of is 28800. I changed it to 7200.
(3), key_buffer_size:
The index block is cached and Shared by all threads. key_buffer_size is the buffer size for the index block, increase the number of indexes it can better handle (for all reads and overwrites) to as much as you can afford. If you make it too big, the system will start changing pages and really slow down. The default value is 8388600(8M). My MySQL host has 2GB memory, so I changed it to 402649088(400MB).
(4), max_connections:
Number of simultaneous customers allowed by . Increasing this value increases the number of file descriptors required by mysqld . This number should be increased, otherwise, you will often see Too many connections errors. The default value of is 100. I changed it to 1024 .
(5), record_buffer:
each thread doing a sequential scan allocates a buffer of this size for each table it scans. If you do a lot of sequential scans, you may want to increase the value. The default value is 131072(128K), which I changed to 16773120 (16M)
(6), sort_buffer:
each thread that needs to sort allocates a buffer of this size. Increase this value to speed up ORDER BY or GROUP BY operations. The default value is 2097144(2M), which I changed to 16777208 (16M).
(7), table_cache:
opens the number of tables for all threads. Increasing this value increases the number of file descriptors required by mysqld. MySQL requires two file descriptors for each uniquely open table. The default value is 64. I'll change it to 512.
(8), thread_cache_size:
can reuse the number of threads stored in it. If so, the new thread is retrieved from the cache, and if there is space when disconnected, the client's line is placed in the cache. If there are many new threads, this variable value can be used to improve performance. You can see this variable in action by comparing the state variables of Connections and Threads_created . I'm going to set it to 80.
(10), wait_timeout:
The number of seconds that an server waits for action on a connection before shutting it down. The default value of is 28800. I changed it to 7200.
Note: parameter adjustment can be achieved by modifying /etc/ my.cnf file and restarting MySQL . This is a careful work, the above results are only my opinion, you can further modify according to the hardware situation of your own host (especially the memory size).
Website traffic is more and more big, the MySQL natural become a bottleneck, so recently I have been studying MySQL optimization, the first step is naturally thought of MySQL system parameter optimization, as one of the sites with substantial traffic (day more than 200000 person-times) database system, can't count on MySQL default system parameters can let MySQL runs very smoothly.
by looking up information on the network and their own attempts, I think the following system parameters are more critical:
(1), back_log:
requires the number of connections available to MySQL . This works when the main MySQL thread gets a lot of connection requests in a very short time, and then the main thread takes some time (albeit a short time) to check the connection and start a new thread.
The back_log value indicates how many requests can be stored on the stack in the short time before MySQL temporarily stops answering new requests. Only if you expect to have many connections in a short time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limit on the queue size. attempts to set back_log higher than your operating system limit will be invalid.
when you look at your host process list, Found a large number of 264084 | unauthenticated user | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | The value of back_log is increased when the NULL process is connected. The default value is 50. I'll change it to 500.
(2), interactive_timeout:
The number of seconds an server waits for action on an interactive connection before shutting it down. An interactive customer is defined as one that USES the CLIENT_INTERACTIVE option for mysql_real_connect(). The default value of is 28800. I changed it to 7200.
(3), key_buffer_size:
The index block is cached and Shared by all threads. key_buffer_size is the buffer size for the index block, increase the number of indexes it can better handle (for all reads and overwrites) to as much as you can afford. If you make it too big, the system will start changing pages and really slow down. The default value is 8388600(8M). My MySQL host has 2GB memory, so I changed it to 402649088(400MB).
(4), max_connections:
Number of simultaneous customers allowed by . Increasing this value increases the number of file descriptors required by mysqld . This number should be increased, otherwise, you will often see Too many connections errors. The default value of is 100. I changed it to 1024 .
(5), record_buffer:
each thread doing a sequential scan allocates a buffer of this size for each table it scans. If you do a lot of sequential scans, you may want to increase the value. The default value is 131072(128K), which I changed to 16773120 (16M)
(6), sort_buffer:
each thread that needs to sort allocates a buffer of this size. Increase this value to speed up ORDER BY or GROUP BY operations. The default value is 2097144(2M), which I changed to 16777208 (16M).
(7), table_cache:
opens the number of tables for all threads. Increasing this value increases the number of file descriptors required by mysqld. MySQL requires two file descriptors for each uniquely open table. The default value is 64. I'll change it to 512.
(8), thread_cache_size:
can reuse the number of threads stored in it. If so, the new thread is retrieved from the cache, and if there is space when disconnected, the client's line is placed in the cache. If there are many new threads, this variable value can be used to improve performance. You can see this variable in action by comparing the state variables of Connections and Threads_created . I'm going to set it to 80.
(10), wait_timeout:
The number of seconds that an server waits for action on a connection before shutting it down. The default value of is 28800. I changed it to 7200.
Note: parameter adjustment can be achieved by modifying /etc/ my.cnf file and restarting MySQL . This is a careful work, the above results are only my opinion, you can further modify according to the hardware situation of your own host (especially the memory size).