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

This article shows you how to analyze MYSQL from trying to abandon the slow query. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

MYSQL's slow query is generally a way for developers and DBA to get bad SQL and possibly lack indexes. This method has been consistent with MYSQL to MYSQL 5.7.But whether we can have other ways to get such usability information, and then reduce our dependence on SLOW LOG, this is a question that can be discussed. (here is not to replace, but with the mentality of learning and exploration, but also with the mentality of adapting to development.)

Most DBAer who pay attention to MYSQL probably know that the weather vane of MYSQL from MYSQL5.6 is close to the style of ORACLE, and it is well known that databases such as ORALCE and SQL SERVER do not have a slow query system such as MYSQL.

What I want to say here is that if you find some system problems through non-slow query, and it is effective, of course, it does not mean to abandon slow query, one more method, one more method recommended to you by programmers, naturally there are a lot of benefits.

Let's talk about this.

Question 1: can I know how long it will take to finish when I do DDL?

This question is estimated, if the knowledge is not updated MYSQL DBA will be more difficult to answer, it is true that there are traditional methods, but not very accurate, specific how to do, we Baidu. (live CQ using PT tools is outside the scope of this discussion)

The MYSQL 5.7I want to talk about today has provided you with an accurate way to know exactly where your DDL is doing, instead of waiting for it to count.

Then what knowledge do we need to know?

1 what events will occur in an alter

1 read competing for and internal sort

2 merge sort

3 insert

4 log apply index

5 flush

6 log apply table

7 end

How to do this? first, let's open performance_schema_setup_instrumets and performance_schema.setup_consumers.

Then we can find a large table above 1 million, do a DDL operation, and then execute the following statement

We can clearly know from the above two pictures, our DDL operation to where, exactly where, a little wrist can be shown as a percentage.

2 monitoring of some slow sentences and mutexes

For resources that can only be monopolized in a period of time, mutual exclusion will inevitably occur, and how to monitor them in the original MYSQL is more troublesome, how to identify long-awaited events, or objects is a problem to be solved.

MYSQL can monitor certain waits through events_stages_summry_global_by_event_name, and use these parameters to understand the problems that may be experienced or faced in MYSQL.

Let's give some examples below, and you can cross the SLOW_LOG directly in the following ways

SELECT EVENT_ID, TRUNCATE (TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long where SQL_TEXT IS NOT NULL

Obviously through the following query we can see the statements running in the system, and quickly know the execution time of each statement, from this point we can already get the running time of the statement without slow query, the time unit is seconds.

Through the analysis of sentences, we can find slow sentences without using slow query system. at the same time, we can also draw the changes of some parameters of a database system by monitoring the design of the system, which is convenient to check some unexpected events and find problems quickly.

Select * from events_stages_summary_by_account_by_event_name where MIN_TIMER_WAIT 0 and user='app_collection'

With a slight change in the above statement, you can get the wait time for the system of a MYSQL database, and if you take it every few seconds, it will be beneficial to find some problems.

The above is how to try to abandon the slow query analysis MYSQL, have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.