Detailed introduction to ten parameters for optimizing mysql performance

(1), back_log:
The required number of connections that MySQL can have. This works when the main MySQL thread gets a lot of connection requests in a short period of time, and then the main thread takes some time (albeit briefly) to check for connections and start a new thread. The
back_log value indicates how many requests can be stored in the stack in a short period of time before MySQL temporarily stops answering new requests. Only if you expect many connections in a short period of time you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limit on this queue size. Attempting to set back_log higher than your operating system's limit will have no effect.
When you observe your host process list and find a large number of 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL processes to be connected, you need to increase the value of back_log. The default value is 50, I changed it to 500.

(2), interactive_timeout:
The number of seconds the server waits for action on an interactive connection before closing it. An interactive client is defined as one that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). The default value is 28800, I changed it to 7200.

(3), key_buffer_size:
The index block is buffered and shared by all threads. key_buffer_size is the buffer size used for index blocks, increase it to get better handling of the index (for all reads and multiple writes), to as much as you can afford. If you make it too big, the system will start paging and really slow down. The default value is 8388600 (8M), my MySQL host has 2GB of memory, so I changed it to 402649088 (400MB).

(4), max_connections:
The number of simultaneous clients allowed. Increasing this value increases the number of file descriptors required by mysqld. This number should be increased, otherwise, you will see Too many connections errors frequently. The default value is 100, I changed it to 1024.

(5), record_buffer:
Each thread that performs 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 this value. The default value is 131072 (128K), I changed it to 16773120 (16M)

(6), sort_buffer:
Each thread that needs to be sorted allocates a buffer of this size. Increasing this value speeds up ORDER BY or GROUP BY operations. The default value is 2097144 (2M), I changed it to 16777208 (16M).

(7), table_cache:
The number of open tables for all threads. Increasing this value increases the number of file descriptors required by mysqld. MySQL requires 2 file descriptors for each unique open table. The default value is 64, I changed it to 512.

(8), thread_cache_size:
The number of threads saved in that can be reused. If there is, the new thread is fetched from the cache, and if there is space when the connection is disconnected, the client's thread is placed in the cache. If there are many new threads, this variable value can be used to improve performance. You can see the role of this variable by comparing the variables in the Connections and Threads_created states. I set it to 80.

(9) Mysql search function
Use mysql to search, the purpose is to be case-insensitive and search in Chinese
Just specify --default-character-set=gb2312 when starting mysqld

(10) , wait_timeout:
The number of seconds the server waits for action on a connection before closing it. The default value is 28800, I changed it to 7200.

Note: Parameter adjustment can be achieved by modifying the /etc/my.cnf file and restarting MySQL. This is a relatively cautious job, and the above results are just my opinions. You can further modify it according to the hardware conditions of your own host (especially the memory size).

Thank you very much for reading! If you want to get more related articles, please pay attention to the PHP Chinese website (www.php.cn)!