Detailed explanation of MySQL basic configuration optimization

When querying a large amount of data, we need to adopt some optimization methods, some basic optimization methods of mysql, and share some of our experiences with everyone.

1.max_connections, the maximum number of connections. If the server has a large number of concurrent connection requests, it is recommended to increase this value. show variables like 'max_connections' Maximum number of connections Let’s check the maximum number of connections

2. Check the real connections Number, execute show status like 'max_used_connections' The number of responses is

3.back_log: MySQL can temporarily store the connections quantity. If the connection data of MySQL reaches max_connections, new requests will be stored in the stack to wait for a certain connection to release resources. The number of the stack is back_log. If the number of waiting connections exceeds back_log, no connection resources will be granted.

Execute show full processlist to view

4.key_buffer_size: Specifies the size of the index buffer, which determines the speed of index processing. Especially the speed of index reading, execute show variables like 'key_buffer_size'; to check it

5. Let's take a look Usage of key_buffer_size Execute

show global status like 'key_read%';

##6.query_cache_size, using the mysql buffer, MySQL stores the query results in the buffer, for the same SELECT statement (case sensitive), the results will be read directly from the buffer. Execute show global status like 'qcache%'; to view, there are many ways to optimize the configuration of mysql

#

The above is the detailed content of Detailed explanation of MySQL basic configuration optimization. For more information, please follow other related articles on the PHP Chinese website!