Mysql performance optimization configuration parameters and mysql performance optimization parameters

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.