MySQL Optimization Analysis

When a website grows, MySQL naturally becomes a bottleneck. Therefore, MySQL optimization becomes a problem that needs to be considered. The first step is to naturally consider the optimization of MySQL system parameters. As a system with a large number of websites (more than 0.2 million people a day, it is impossible to expect MySQL's default system parameters to make MySQL run smoothly.

Back_log: (maximum number of connection threads)

The number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a short period of time, this takes effect, and then the main thread takes some time to check the connection and start a new thread.

The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. You have limits on the queue size. Attempts to set back_log higher than your limit will be invalid.

When you observe the process list of your host and find a large number of processes to be connected, which are 264084 unauthenticated userxxx. XXX. XXX. xxxnullconnectnullloginnull, you need to increase the value of back_log. The default value is 50. I will change it to 500.

Interactive_timeout: (Closing session time setting)

The number of seconds that wait for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the client_interactive option for mysql_real_connect. The default value is 28800. I will change it to 7200.

, Key_buffer_size: (index block buffer size)

The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed. If you make it too large, the system will begin to change pages and it will really slow down. The default value is 8388600, and my MySQL host has 2 GB of memory, so I changed it to 402649088.

, Max_connections: (maximum number of connections)

Number of customers allowed simultaneously. Increase the number of file descriptors required by mysqld. This number should be added. Otherwise, you will often see the too connector connections error. The default value is 100. I will change it to 1024.

, Record_buffer: (buffer size per table)

Each thread that performs an ordered scan allocates a buffer of this size to each table it scans. If you perform many sequential scans, you may want to increase the value. The default value is 131072. I changed it to 16773120.

, Sort_buffer:

Each thread that needs to be sorted allocates a buffer of this size. Add this value to accelerate the order by or group by operation. The default value is 2097144. I will change it to 16777208.

, Table_cache:

Number of tables opened for all threads. Increase this value to increase the number of file descriptors required by mysqld. MySQL requires two file descriptors for each unique opened table. The default value is 64. I changed it to 512.

, Thread_cache_size: (number of threads retained in)

The number of threads that can be reused. If yes, the new thread is obtained from the cache. If there is space when the connection is disconnected, the customer's thread is placed in the cache. If there are many new threads, this variable value can be used to improve performance. By comparing variables in connections and threads_created states, you can see the role of this variable. I set it to 80.

, Wait_timeout: (close the connection wait seconds)

The number of seconds that wait for action on a connection before closing it. The default value is 28800. I will change it to 7200. Note: you can modify the parameters by modifying the/etc/My. CNF file and restarting MySQL. This is a relatively cautious job. The above results are just some of my views. You can further modify them based on the hardware situation of your host (especially the memory size ).