Detailed explanation of Mysql's concurrent parameter adjustment

When you start the MySQL query cache, when you execute the same SQL statement, the server will directly read the result from the cache. When the data is modified, the previous cache will become invalid. Tables that are modified frequently are not suitable for query caching.

1. The client sends a query to the server. 2. The server first checks the query cache. If the cache is hit, it immediately returns the result stored in the cache, otherwise it proceeds to the next stage. 3. The server parses and preprocesses the SQL, and then the optimizer generates the corresponding execution plan. 4. According to the generated execution plan, call the storage engine API to execute the query. 5. Return the result to the client--and put the query result into the query cache.

Check whether the database supports query caching (YES or ON)
	show variables like 'have_query_cache';
Check whether the database query cache is enabled show variables like 'query_cache_type';
	OFF or 0 query cache function is turned off ON or 1 query cache function is turned on, select results that meet the cache conditions will be cached, otherwise they will not be cached, and SQL_NO_CACHE will be specified, and no caching will be performed DEMAND or 2 query cache function is performed on demand, and only SELECT statements that specify SQL_CACHE will be cached; others will not be cached. View the size of the query cache show variables like 'query_cache_size';
Check the cache status show status like 'Qcache%';
		Qcache_free_blocks The number of memory blocks available in the cache Qcache_free_memory The amount of memory available in the cache Qcache_hits The number of query cache hits Qcache_lowmen_prunes The number of queries removed from the query cache due to low memory Qcache_not_cached The number of non-cached queries (cannot be cached due to the query_cache_type setting)
		Qcache_queries_in_cache The number of queries registered in the query cache Qcache_total_blocks The total number of blocks in the query cache Enable query cache Find the mysql configuration file and add my.cnf Add query_cache_type=1
	The query cache is enabled with query_cache_type=DEMAND   
		select SQL_CACHE id from table
	The query cache is enabled with query_cache_type=ON    
		select SQL_NO_CACHE id from table

Inconsistency in SQL statements: There is uncertain information in the query SQL:
	Such as now(), current_date(), curdate(), curtime(), rand(), uuid(), database()
When querying tables in the mysql, infomation_schema or performance_schema databases without using any table query statements, the query cache will not be used. Query table modifications performed within the body of a stored function, trigger or event will delete the query cache, such as insert, update, delete, drop...

Allocate as much memory as possible to MySQL for caching, and reserve enough memory for the operating system and other programs. The data file reading of the MyISAM storage engine depends on the operating system's own IO cache. Therefore, if there is a MyISAM table, more memory should be reserved for the operating system as an IO cache sorting area. The connection area and other caches are allocated to each database session. The default setting should be reasonably allocated according to the maximum number of connections. If the setting is too large, it will not only waste resources, but also cause memory exhaustion when there are many concurrent connections.

The myisam storage engine uses key_buffer to cache index blocks to speed up the reading and writing of myisam indexes. MySQL has no special cache mechanism for the data blocks of the MyISAM table, and relies entirely on the operating system's IO cache key_buffer_size=512M
	The larger the value of key_buffer_size, the higher the efficiency. It is recommended to allocate at least 1/4 of the available memory to key_buffer_size.
show variables like 'key_buffer_size'; query the size of key_buffer_size red_buffer_size
	If you frequently scan MyISAM tables sequentially, you can improve performance by increasing the read_buffer_size value, but do not increase it too much, because key_buffer_size is exclusive to each session. If you set more than one, it will waste resources.
	For queries on MyISAM tables that often require sorting, if the SQL has an order by clause, increasing the value of red_rnd_buffer_size appropriately can improve the performance of such SQL. This is also exclusive to the session and cannot be set too large.

InnoDB uses a memory area as an io buffer pool, which is used not only to cache innoDB index blocks, but also to cache innoDB data blocks inodb_buffer_pool_size
	This variable determines the maximum cache size of the innoDB storage engine and index data. Under the premise of ensuring that the operating system and other programs have enough memory available, the larger the value, the higher the cache hit rate, the less disk io required to access the innoDb table, and the higher the performance. innodb_buffer_pool_size=521M
innodb_log_buffer_size
	Determines the size of the innodb redo log buffer. For large transactions that may generate a large number of update records, increasing the innodb_log_buffer_size size can avoid innodb performing unnecessary log writes to disk io before the transaction is committed.
	innodb_log_buffer_size=10M
show variables like 'innodb_buffer_pool_size';
innodb_flush_log_at_trx_commit
	Controls the time when InnoDB writes the data in the log buffer to the log file and flushes the disk. The values ​​are 0, 1, and 2.
innodb_thread_concurrency
	Set the number of concurrent innodb threads. The default value is 0, which means unlimited. If you want to set innodb_log_buffer_size, it is recommended to be consistent with the number of CPU cores of the server or twice the number of CPU cores.
	The memory size used to execute the log file, in M ​​units innodb_log_files_in_group
	Write log files to multiple files in a circular manner read_buffer_size
	MySQL read buffer size, a request for sequential scan of the table will be allocated to a read buffer read_rnd_buffer_size
	MySQL random read buffer size

The maximum number of connections, the default is 151, Linux can generally support 500--1000 according to the server performance evaluation show variables like 'max_connections';
	View the maximum number of connections

The maximum number of waiting connections after the maximum number of connections is exceeded is stored in the stack (usually 50+(max_connections/5)), and the maximum does not exceed 900
show variables like 'back_log'

Controls the number of table caches that can be opened by all SQL statement execution threads. When executing SQL statements, each SQL execution thread must open at least one table cache. The value of this parameter should be set according to the maximum number of connections max_connectons and the maximum number of tables involved in the execution of associated queries by each connection.

In order to speed up the connection to the database, MySQL will cache a certain number of customer service threads for reuse. The parameter thread_cache_size can be used to control the number of MySQL cache customer service threads.

Set the time that innoDB transactions wait for row locks. The default value is 50ms, which can be set dynamically as needed. For business systems that require fast feedback, the row lock wait time can be reduced to avoid long-term transaction suspension. For batch processing programs running in the background, the row lock wait time can be increased to avoid large rollback operations. show variables like 'innodb_lock_wait_timeout';

Specifies the name of the binary log file, which is used to record all statements that cause changes to the database.

Specifies the database to be updated in the binary log. All other database updates not explicitly specified will be ignored and not recorded in the log.

Specify how many times to write logs before synchronizing disk

Whether to enable query logging

Specify the query log file name. The user records all query statements in the query log file name: slow_query_log_file=slow_query.log
mysqldumpslow slow_query.log; View the slow query log file

Whether to enable slow query log, 1 means enabled, 0 means disabled

Set the slow query time. Only query statements exceeding this time will be logged.

Whether to write management statements to the slow query log