Shulou(Shulou.com)05/31 Report--

This article mainly explains "the reason analysis and solution of slow query on mysql server". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "mysql server query slow reason analysis and solutions" it!

In the development of friends, especially friends who have contact with mysql will encounter sometimes mysql query is very slow, of course, I mean a large amount of data millions of levels, not dozens of, let's take a look at the solution to slow query.

It is often found that developers check statements without indexes or statements without limit n, which will have a great impact, such as scanning a large table with tens of millions of records, or constantly doing filesort, causing io impact on databases and servers, and so on. This is the situation above the mirror library.

When it comes to the online library, in addition to the statements that do not have an index and do not use limit, there is also one more case, the problem of too many connections. Speaking of which, let's take a look at our monitoring practices in the past.

1. Deploy open source distributed monitoring systems such as zabbix to obtain the io,cpu and connections of the daily database

two。 Deploy weekly performance statistics, including data growth and iostat,vmstat,datasize

3. Mysql slowlog collection, listing top 10

Www.2cto.com

I used to think that it was perfect to do these monitoring, but now after the deployment of mysql node process monitoring, I found a lot of disadvantages.

The disadvantages of the first approach: the zabbix is too large, and it is not monitored within mysql, and a lot of data is not very prepared. Now it is generally used to look up historical data.

The disadvantage of the second method: because it runs only once a week, many cases cannot be detected and reported to the police.

The downside of the third approach: when the node has a lot of slowlog, top10 becomes meaningless, and most of the time it will give you regular task statements that must be run. The reference is of little value.

So how do we solve and inquire about these problems?

For troubleshooting problems and finding performance bottlenecks, the easiest problems to find and solve are slow queries in MYSQL and queries that do not have available indexes.

OK, start finding the SQL statements in mysql that don't "feel good" to execute.

=

Method 1: I am using this method, hehe, I prefer this kind of immediacy.

The code is as follows

Versions of Mysql5.0 and above can support the recording of slowly executed SQL statements.

Mysql > show variables like 'long%'; Note: this long_query_time is used to define the number of seconds before a "slow query"

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 10.000000 | |

+-+ +

1 row in set (0.00 sec)

Mysql > set long_query_time=1; Note: I set 1, that is, any query that takes more than 1 second to execute is a slow query.

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like 'slow%'

+-+ +

| | Variable_name | Value |

+-+ +

| | slow_launch_time | 2 | |

| | slow_query_log | ON | Note: whether to enable logging |

| | slow_query_log_file | / tmp/slow.log | Note: where to set it |

+-+ +

3 rows in set (0.00 sec) www.2cto.com

Mysql > set global slow_query_log='ON' Note: turn on logging

Recording starts as soon as the slow_query_log variable is set to ON,mysql.

The initial value of the above MYSQL global variable can be set in / etc/my.cnf.

Long_query_time=1

Slow_query_log_file=/tmp/slow.log

=

Method 2: mysqldumpslow command

The code is as follows

/ path/mysqldumpslow-s c-t 10 / tmp/slow-log

This outputs the 10 SQL statements with the most records, of which:

-s indicates how to sort. C, t, l, r are sorted by the number of records, time, query time, and the number of records returned. Ac, at, al, and ar indicate the corresponding flashbacks.

-t, which means top n, that is, how many previous pieces of data are returned.

-g, a regular matching pattern can be written later, which is case-insensitive.

such as

/ path/mysqldumpslow-s r-t 10 / tmp/slow-log

Get the 10 queries that return the most recordsets.

/ path/mysqldumpslow-s t-t 10-g "left join" / tmp/slow-log

Get the query statements with left links in the first 10 items sorted by time.

Finally, summarize the benefits of node monitoring.

1. Lightweight monitoring, which is real-time and can be customized and modified according to the actual situation.

2. A filter program is set up to filter those statements that must run.

3. It is worthwhile to find those queries that do not use indexes or are illegal in time, although it takes time to deal with those slow statements, but it can avoid database hanging.

4. When there are too many connections in the database, the program will automatically save the processlist,DBA of the current database to find the reason. This is a sharp weapon.

5. When using mysqlbinlog to analyze, you can get a clear period of time when the database state is abnormal

Some people will suggest that we do the mysql configuration file settings.

Found some other parameters when adjusting tmp_table_size

Number of queries that Qcache_queries_in_cache has registered in the cache

Number of queries that Qcache_inserts was added to the cache

Number of Qcache_hits cache samples

The number of queries Qcache_lowmem_prunes deleted from the cache due to lack of memory

The number of queries that Qcache_not_cached has not been cached (cannot be cached, or due to QUERY_CACHE_TYPE)

Total free memory of Qcache_free_memory query cache

The number of free memory blocks in the Qcache_free_blocks query cache

The total number of blocks in the Qcache_total_blocks query cache

Qcache_free_memory can cache some commonly used queries, and if it is a common sql, it will be loaded into memory. That will increase the speed of database access.

At this point, I believe that everyone on the "mysql server query slow reason analysis and solutions" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.