Mysql performance optimization configuration parameters and mysql performance optimization parameters
To modify the values of the following parameter configuration items, refer
1. connection settings
Show variables like '% max_connection % ';
Show status like '% Max_used_connections % ';
Max_used_connections/max_connection <= 85%
2. Number of connections stored in the stack
Show variables like '% back_log % ';
The default value of back_log is 50. We recommend that you change it to 128 ~ 512
3. Wait time before the data connection is closed
Show variables like '% timeout % ';
Modify the value of interactive_timeout wait_timeout. The default value is 28800. We recommend that you change it to 7200.
4. Index buffer size
Show status like '% read % ';
Index cache hit rate key_read/key_request_reads ~ = 0.001 ~ 0.01
5. query the buffer size (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 rate: Qcache_hits/Qcache_inserts> 95%
6. The size of the sequential read, random read, sorting, and connection buffer. Each thread exclusive. It is recommended to set it to 16 MB.
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 adjusted 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 must be adjusted only when the size of the temporary table is exceeded.
9. Temporary table size 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 % ';
Innodb_thread_concurrency (cpu + disk) twice
12. Others
Size of data and index buffer innodb_buffer_pool_size 80% of physical content
Log buffer size innodb_log_buffer_size 1 ~ 8 MB
Size of data fields and other data structures innodb_additional_mem_pool_size 20 MB
Transaction Processing Mechanism innodb_flush_log_at_trx_commit
0 commit transactions do not write logs, log file writing per second and flush Disk
1 second or every time a transaction is committed, the log file is written to the flush Disk
2. When each transaction is committed, log files are written to the flush disk per second.