Mysql Server slow query Cause Analysis and Solution

Some developers, especially those who have contact with mysql, may encounter slow mysql queries. Of course, I am referring to a large data volume of millions or tens of millions, not dozens of times, next we will look at the solution to slow query. Developers often find that they do not use indexed or limit n statements, which will have a great impact on the database, for example, a large table with tens of millions of records needs to be fully scanned, or filesort is constantly performed, causing io impact on the database and server. This is the case above the image library. In addition to the non-index statements in the online database, the limit statement is not used. This is another case where the number of mysql connections is too large. Speaking of this, let's take a look at our previous monitoring practices 1. deploy zabbix and other open-source distributed monitoring systems to obtain the daily database I/O, cpu, and connections. weekly Performance Statistics for deployment, including data increase, iostat, vmstat, and datasize. mysql slowlog collection, listing top 10 www.2cto.com previously thought it was perfect to perform these monitoring. Now, after the mysql node process monitoring is deployed, we can find the disadvantages of the first practice: zabbix is too large and is not used for internal mysql monitoring. A lot of data is not very prepared. Now it is generally used to check historical data. The second method has the disadvantages: because it only runs once a week, it is impossible to find and alarm the third method of drawback in many cases: when there are many node slowlogs, top10 becomes meaningless, in addition, you will often be given the regular task statements that must be run .. The reference is of little value. How can we solve and query these problems? For troubleshooting and identifying performance bottlenecks, slow query of MYSQL and query without indexes are the most common problems to be found and solved. OK. Start to find the SQL statement that is not "refreshing" in mysql. ========================================================== ======================= Method 1: I am using this method. The Code is as follows: MySQL and later versions Support recording slow SQL statements. Mysql> show variables like 'long % '; note: this long_query_time is used to define how many seconds are slower to calculate the "slow query" + ----------------- + ----------- + | Variable_name | Value | + bytes + ----------- + | long_query_time | 10.000000 | + bytes + --------- + 1 row in set (0.00 sec) mysql> set long_query_time = 1; Note: I set 1, that is, if the execution time exceeds 1 second, the query is slow. Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'slow % '; + logging + ------------- + | Variable_name | Value | + metrics + --------------- + | slow_launch_time | 2 | slow_query_log | ON | Note: whether to enable logging | slow_query_log_file |/tmp/slow. log | Note: where to set + --------------------- + --------------- + 3 rows in set (0.00 sec) www.2cto.com mysql> set global slow_query_log = 'on' note: Once the slow_query_log variable is set to ON, mysql starts logging immediately. In/etc/my. cnf, you can set the initial values of the above MYSQL global variables. Long_query_time = 1slow_query_log_file =/tmp/slow. log = ============= Method 2: the mysqldumpslow command code is as follows:/path/mysqldumpslow-s c-t 10/tmp/slow-log this will output 10 SQL statements with the most record times, of which:-s, indicates the sorting method. c, t, l, and r are sorted by the number of records, time, query time, and number of returned records. ac, at, al, ar, -t refers to top n, that is, the number of data records returned.-g indicates a regular expression matching mode, which is case insensitive; for example,/path/mysqldumpslow-s r-t 10/tmp/slow-log can obtain a maximum of 10 queries from the returned record set. /Path/mysqldumpslow-s t-t 10-g "left join"/tmp/slow-log get the first 10 query statements containing the left join in chronological order. Summary of the benefits of node monitoring 1. lightweight monitoring and real-time monitoring. You can also customize and modify it based on the actual situation. the filter program is set to filter the statements that must be run. 3. timely discovery of those queries that do not use indexes or are illegal. Although it takes a lot of time to process those slow statements, it is worthwhile to avoid database crashes. when there are too many connections in the database, the program will automatically save the processlist of the current database. This is a good tool for DBA to find the cause. when using mysqlbinlog for analysis, we can get a clear period of time when the database status is abnormal. Some people will make the mysql configuration file set www.2cto.com to adjust tmp_table_size and find that the Qcache_queries_in_cache parameter has been added to the cache. qcache_hits cache sample count Qcache_lowmem_prune S the number of queries deleted from the cache due to lack of memory Qcache_not_cached the number of queries not cached (cannot be cached, or because QUERY_CACHE_TYPE) qcache_free_memory query the total number of idle memory in the cache Qcache_free_blocks query the number of idle memory blocks in the cache Qcache_total_blocks query the total number of blocks in the cache Qcache_free_memory can cache some common queries, common SQL statements are loaded into the memory. This will increase the database access speed. This article is from yijucheng.