MySQL Performance Optimization Configuration summary

Read some of the books that optimize MySQL operations, as summarized in this record:
Enter the following SQL into the MySQL client:
1. Connection settings

Show variables like '%max_connection% ', show status like '%max_used_connections% ';

Max_used_connections/max_connection <=85%, the value of the parameter configuration item can be modified against

2. Number of connections stored in the stack

Show variables like '%back_log% ';

Back_log default to 50 is recommended for modification to 128~512

3. Wait time before data connection is closed

Show variables like '%timeout% ';

Modify the value of the Interactive_timeout wait_timeout 2 item by default to 28800, and recommend to change to 7200

4. Size of index buffer

Show status like '%read% ';

Index not cache hit ratio key_read/key_request_reads ~=0.001~0.01

5. Size of query buffer (query_cache_size)

Show variables like '%cache% ', show status like '%qcache% ';

Cache Fragmentation Rate Qcache_free_blocks/qcache_total_blocks <20%
Cache Utilization (Query_cache_size-qcache_free_memory)/query_cache_size<25%
Cache Hit Ratio qcache_hits/qcache_inserts>95%

6, sequential read, random read, sort, the size of the connection buffer, each thread exclusive, recommended set to 16MB

Show status like '%buffer% ';

Read_buffer_size
Read_rnd_buffer_size
Sort_buffer_size
Join_buffer_size

7. Table buffer Size

Show status like '%tables% ';

Table_cache Adjust according to open_tables opented_tables size

8. Memory tables and temporary tables

Show status like '%table% ';

Max_heap_table_size
Tmp_table_size
The size of the memory table needs to be resized if the memory table exceeds the temporary table size

9. The size of the temporary table on disk

Show status like '%tmp% ';

(created_tmp_disk_tables/created_tmp_tables) *100<25%

10. Number of cache threads

Show variables like '%tmp% ';

Thread_cache_size

11. Number of concurrent threads

Show variables like '%thread% ';

Twice times the number of innodb_thread_concurrency (cpu+ disks)

12. Other
The size of the data and index buffers innodb_buffer_pool_size 80% of the physical content
Size of the log buffer innodb_log_buffer_size 1~8MB
Size of data fields and other structures innodb_additional_mem_pool_size 20MB
Innodb_flush_log_at_trx_commit the mechanism of dealing with things
0 commit things do not write to log, log file writes per second and flush disk
1 log files written to flush disk per second or every time a thing is committed
2 log files are written, flush disk per second, every time things are committed

MySQL Performance Optimization Configuration summary