Mysql parameter Summary

1. query_cache_size
After the select statement is used to query mysql, the query structure is cached as SQL text. When the same SQL statement is used to query again, data is directly retrieved from the cache and returned.
The value of Qcache_lowmem_prunes is very large, which indicates that the buffer is insufficient frequently. The value of Qcache_hits is very large, which indicates that the query buffer is frequently used. In this case, the value of Qcache_hits is not large, this indicates that your query repetition rate is very low. In this case, using the Query Buffer will affect the efficiency, so you can consider not to use the query buffer.
Defects:
When mysql uses query_cache to work, it is required that the tables involved in this statement do not change during this period of time. If the data is updated, all query_cache is set to invalid and then updated.
If query_cache_size is too large, there are many queries, and the invalidation will be slow, and the update or insert will also be slow.
2. key_buffer_size
Only works for the myisam table
Key_buffer_size specifies the size of the index buffer, which determines the index processing speed, especially the index reading speed.
Check the status values Key_read_requests and Key_reads to check whether the key_buffer_size setting is reasonable.
Total key_read_requests read requests. key_keys indicates the number of disk requests, that is, the smaller the key_read_requests/key_reads, the better.
3. table_open_cache (earlier than 5.1 is table_cache)
Table_open_cache specifies the table cache size. When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly.

Check the status values Open_tables and Opened_tables of the peak time to determine whether to increase the value of table_open_cache. If you find that open_tables is equal to table_open_cache and opened_tables is growing, you need to increase the value of table_open_cache.

4. log_bin
Binary logs can be shut down from the slave database, which can improve the performance by about 1%.

5. max_connections
Max connections
6. back_bog
The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection.
7. thread_cache
To improve the performance of the connection process created by client requests, mySQL provides a connection pool, namely the Thread_Cache pool, which stores idle connection threads in the connection pool instead of destroying them immediately. the advantage is that when there is another new request, mysql will not immediately create a connection thread, but will first go to Thread_Cache to find idle connection threads. If so, it will be used directly, A new connection thread is created if it does not exist.
Thread_cache_size
The maximum number of connection threads stored in Thread_Cache. Thread_Cache is very effective in applications with short connections.
Thread_stack
The memory allocated to mysql when each connection is created. This value is generally considered to be applicable to most scenarios by default.
Thread_cache hit rate calculation
Show variables like 'thread % '; show status like' % connections % '; show status like' % thread %'

Formula: thread_cache_hit = (connections-thread_create)/connections * 100%

8. log-slave-update
In the master-slave architecture, it is used when the slave database needs to maintain the same binary data as the master database (the slave database does not need to enable binary logs by default)
9. Sync_binlog
If it is positive, after each sync_binlog is written to the binary log, the MySQL server synchronizes its binary log to the hard disk.
If the value is 0, it is not synchronized with the hard disk. If the value is 1, it is synchronized with the hard disk. when mysql crashes, one statement or transaction can be lost at most.
10. record and analyze slow logs
Slow-query-log-file
Long-query-time
Set the time to 1 s.
Explain Analysis
Syntax: explain [extended] select... from... where
If extended is used, you can use the show warnings statement to query the optimization information after the explain statement is executed.
Type: ALL indicates full table scan.
Key: NULL no index used
Extra: Using where; Using filesort does not use index sorting
-----------------------------
Type: range indicates no full table Scan
Extra: Using where indicates no File Sorting

10. Lock
Show status like 'table % ';
Table_locks_immediate indicates the number of table locks to be released immediately, Table_locks_waited indicates the number of table locks to wait
If the value of Table_locks_waited is relatively high, it indicates a serious table-Level Lock contention. At this time, we need to perform further checks on the application to determine the problem.

11. innodb_buffer_pool_size
The size of the memory buffer for buffering innodb data and indexes. The higher the value, the less disk I/o required.

12. innodb_flush_log_at_trx_commit
0: writes the log buffer to the log file every second, and refreshes the log file to the disk.
1: When each transaction is committed, the log buffer is written to the log file and refreshed to the disk.
2: log files are written to the log cache for each submission, but the log files of the troops are refreshed on the disk.
The default value is 1, which is also the safest
This article is from the "2" blog