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

 like ' %max_connection% '  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

 like ' %back_log% ';

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

3. Wait time before data connection is closed

 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

 like ' %read% ';

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

5. Size of query buffer (query_cache_size)

 like ' %cache% '  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

 like ' %buffer% ';

Read_buffer_size
Read_rnd_buffer_size
Sort_buffer_size
Join_buffer_size

7. Table buffer Size

 like ' %tables% ';

Table_cache Adjust according to open_tables opented_tables size

8. Memory tables and temporary tables

 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

 like ' %tmp% ';

(created_tmp_disk_tables/created_tmp_tables) *100<25%

10. Number of cache threads

 like ' %tmp% ';

Thread_cache_size

11. Number of concurrent threads

 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