Shulou(Shulou.com)06/02 Report--

This article will explain in detail the example analysis of concurrent parameter adjustment in Mysql. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Query cache optimization

It is not recommended. It is generally used as the cache layer in the application service. After all, the data concurrency ability is limited.

Summary: when the query cache of Mysql is started and the same sql statement is executed, the server will read the results directly from the cache. When the data is modified, the previous cache will fail, and the frequently modified tables are not suitable for query caching query flow 1. The client sends a query to the server 2, and the server will check the query cache first. If the cache is hit, the results stored in the cache will be returned immediately. Otherwise, enter the next stage 3, the server performs sql parsing, preprocessing, and then the optimizer generates the corresponding execution plan 4. According to the generated execution plan, call the Api of the storage engine to execute the query 5, return the results to the client-- and put the query results into the query cache to configure whether the query database supports query cache (YES or ON) show variables like 'have_query_cache'. Check whether the database has enabled query cache show variables like 'query_cache_type'; OFF or 0 query cache function off ON or 1 query cache function on, select results will be cached if they meet the cache conditions, otherwise no cache will be displayed, specified SQL_NO_CACHE will be displayed, DEMAND will not be cached or query cache function 2 will be cached as needed, and only the SELECT statements of specified SQL_CACHE will be cached Other non-cache view query cache size show variables like 'query_cache_size'; view cache status show status like' Qcache%' Number of memory blocks available in Qcache_free_blocks cache number of memory available in Qcache_free_memory cache number of Qcache_hits query cache hits Qcache_lowmen_prunes number of queries deleted from query cache due to insufficient memory number of Qcache_not_cached non-cache queries (query_ Cache_type setting but cannot cache) number of queries registered in Qcache_queries_in_cache query cache total number of blocks in Qcache_total_blocks query cache open query cache find mysql configuration file add my.cnf add query_cache_type=1 open query cache result is query_cache_type=DEMAND When select SQL_CACHE id from table enables query caching, the result is that query_cache_type=ON select SQL_NO_CACHE id from table query cache is invalid. Sql statements are inconsistent. Query sql contains uncertain information, such as now (). Current_date (), curdate (), curtime (), rand (), uuid (), database () do not use any table query statements to query mysql When tables in infomation_schema or performance_schema databases are not cached, query table modifications performed within the body of triggers or events will delete the query cache, such as insert,update,delete,drop... Memory management optimization memory optimization principle allocates as much memory as possible to Mysql for caching, to reserve enough memory for the operating system and other programs MyISAM storage engine data file reading depends on the operating system's own IO cache, therefore, if there is a MyISAM table, it is necessary to reserve more memory for the operating system to do IO cache sorting area, and caches such as connection areas are allocated to each database session (session). Its default setting should be allocated reasonably according to the maximum number of connections. If the setting is too large, it will not only waste resources, but also lead to powerless memory depletion of MyISAM memory when concurrent connections are high. Optimize myisam storage engine to use key_buffer cache index blocks to speed up the read and write speed of myisam indexes. For myisam data blocks, mysql has no special caching mechanism. It is completely dependent on the operating system's io cache key_buffer_size=512M key_buffer_size. The higher the value, the higher the efficiency. It is recommended to allocate at least 1 to 4 available memory to key_buffer_sizeshow variables like 'key_buffer_size'. Query the size of key_buffer_size red_buffer_size if the myisam table is regularly scanned sequentially, the performance can be improved by increasing the size of the read_buffer_size, but not too large, because key_buffer_size is exclusive to each session. If you set up multiple queries that cause a waste of resources, red_rnd_buffer_size often needs to sort the myisam table. If the sql with the order by clause appropriately increases the value of red_rnd_buffer_size, it can improve the performance of this kind of sql-it is also exclusive to session, nor can it set too large InnoDB memory optimization InnoDB uses a memory area as the io cache pool, which is used not only to cache the index block of innoDB, but also to cache the data block inodb_buffer_pool_size of innoDB. This variable determines the maximum cache size of innoDB storage engine and index data. In the case of ensuring that the operating system and other programs have enough memory available, the higher the value, the higher the cache hit ratio, the less disk io is needed to access the innoDb table, and the higher the performance innodb_buffer_pool_size=521Minnodb_log_buffer_size determines the size of the innodb redo log cache. For large transactions that may generate a large number of update records, increase the innodb_log_buffer_size size. It can prevent innodb from writing unnecessary logs to disk io innodb_log_buffer_size=10Mshow variables like 'innodb_buffer_pool_size' before the transaction is committed. Innodb_flush_log_at_trx_commit controls the time when innodb writes the data in log buffer to the log file and flush disk. The values are: 0Magne1, 2innodbAccessThreadthreadconcurrency, set the number of concurrency of innodb threads. The default is 0, which means there is no limit. If you want to set the amount of memory used to execute log files by innodb_log_buffer_size, which is recommended to be consistent with the number of CPU cores of the server or twice the number of CPU cores, innodb_log_buffer_size executes log files. Innodb_log_files_in_group writes log files to multiple files in a circular manner, read_buffer_size mysql read buffer size, and requests for sequential scanning of tables are allocated to a read buffer read_rnd_buffer_size mysql randomly read buffer size connection optimizes the maximum number of max_connection connections, default is 151, Linux can generally support 500mur1000 to evaluate show variables like 'max_connections' based on server performance. Check the maximum number of waits after the maximum number of connections back_log exceeds the maximum number of connections, and store it in the stack (usually 50 + (max_connections/5)), no more than 900show variables like 'back_log'table_open_cache 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 set max_connectons and the maximum number of tables involved in each connection executing the associated query. In order to speed up the connection to the database, mysql will cache a certain number of customer service threads for reuse. The number of Mysql caching customer service threads can be controlled by the parameter thread_cache_size. Innodb_lock_wait_timeout sets the time for innoDB transactions to wait for row locks. The default is 50ms. It can be set dynamically as needed. For business systems that need quick feedback, the waiting time of row locks can be reduced to avoid long-term suspensions of transactions. For batch processors running in the background, the waiting time of row locks can be increased to avoid large rollback operation show variables like 'innodb_lock_wait_timeout'. Journal

The name of the error log file, and the user records relevant information when mysql starts and stops, and any validation errors occur while the server is running

Log_bin specifies the name of the binary log file, which is used to record all statements that cause changes to the database. Binlog_do_db binlog_ignore_db specifies that updates will be logged to the binary log, and all other database updates that are not displayed will be ignored. Do not record how many times the log is written after sync_binlog specifies whether the synchronous disk general_log=1 enables query log recording general_log_filefile_name (default host_name.log) specifies the query log file name, and the user records all query statements query log file name: slow_query_log_file=slow_query.logmysqldumpslow slow_query.log Check whether the slow log file slow_query_log=1 enables the slow log. 1 means to enable the slow log, and 0 indicates the time to close the long_query_time=3 to set the slow log. Only the query statements beyond this time will record whether the log _ slow_admin_statements writes management statements to the slow log. This is the end of the article on "sample Analysis of concurrent Parameter Adjustment in Mysql". Hope that the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.