MYSQL learning summary (6): configuration of each parameter of my.cnf and optimization according to status

#Only the [mysqld] part is mentioned here
Datadir = /data/mysql //mysql data directory
Skip-locking //Reduce the chance of errors
Key_buffer = 384M //Index buffer size, we will explain how to set it in detail later
Max_allowed_packet = 1M //The maximum value of a message transmission in network transmission
Table_cache = 512 //Table buffer size
Sort_buffer_size = 2M //Buffer size when querying sorting, will be discussed in detail later
Read_buffer_size=2M//Query operation The buffer size that can be used
Read_rnd_buffer_size = 8M //The memory size that can be used by random query operations
Myisam_sort_buffer_size=64M //The buffer required for reordering when the MyISAM table changes
Thread_cache_size = 8M //Set the Tread Cache pool The maximum number of connection threads that can be cached, with a memory of 4G or above, a value of 64 or greater
Query_cache_size = 32M //Query buffer size
Thread_concurrency = 8 //Set to the number of logical servers * 2
Thread_stack = 256K //MYSQL each The stack size of the thread, the default value is large enough, when ERROR:1436 Thread stack overrun occurs, the value can be increased
Skip-name-resolve //It is best to add, remove the dns resolution of the connected machine, ERROR often occurs: 2013 was caused by this
Log-bin=mysql-bin //Open the binary log
Log-slow-queries = /usr/local/mysql/var/slowquery.log //Open the slow query log to determine which queries have timed out. And which queries do not have indexes. The default slow query time is 5 seconds, and queries exceeding 5 seconds are recorded. You can use set long_query_time = 2 to set the slow query time, and you can also use mysqldumpslow to analyze the slow query log. For example, to analyze the 20 most accessed statements, mysqldumpslow –s c –t 20 /usr/local/mysql/var /slowquery.log


# Modify the value of each parameter by checking the MYSQL status


#To check the MYSQL status, mainly use the show command, generally check the system status, and the value of the variable parameter
For example: check how slow the current system is Query log
Show global status like “%slow%”; //You can get the number of slow queries slow_queries
Check the current slow query settings
Show variables like “%slow%”; //You can get slow_launch_time
#Set max_connections The value and view
Show variables like “max_connections”;
Show global status like “max_used_connections”;
If max_used_connections is close to the max_connections value, you need to increase the max_connections value. Set max_connections = 256 (After restarting, it is best to write in my.cnf)


#Set the value of key_buffer_size
Show Global Status Like "key_read%"; Calculation formula: key_reads/ Key_read_requests < 0.1% is best, but if Key_reads/ Key_read_requests < 0.01%, it means that key_buffer_size is allocated too much.


#Check process usage
Show global status like “Thread%”;

If Threads_created is too large, it means that the MYSQL server has been creating threads, and the value of thread_cache_size can be adjusted.


#View query cache (qurey_cache_size) settings
Show global status like “qcache%”;

Show variables like “query_cache%”; //View query_cache configuration

There are three indicators for query:
Query cache fragmentation Rate = Qcache_free_blocks/Qcache_total_blocks, if it exceeds 20%, you can use flush query cache to defragment the cache.
Query cache utilization = (query_cache_size-Qcache_free_memory)/query_cache_size. If it is below 25%, it means that the query_cache_size setting is too large. If it is above 80% and Qcache_lowmem_prunes>50, it means that query_cache_size is a bit small.
Query cache hit rate = (Qcache_hits-Qcache_inserts)/Qcache_hits, this is just for reference. If it is too small, it means frequent reading and writing, and there are fragments.


#View the number of open files
Show global status like “open_files”;

Show variables like “open_files_limit”;

Generally, the number of open files/limit the number of open files should be

Comprehensive As mentioned above, we should usually pay attention to the following parameters:
                                                                                                                                                             . Query_cache_size = 64M
Tmp_table_size = 256M //Maximum memory temporary table value
Table_cache = 614 //Accessing the memory allocated by the table can reduce IO
Skip-name-resolve //Added to avoid access timeout etc.

The above is the MYSQL learning summary (6): my.cnf parameter configuration and status optimization. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!