MySQL optimization 4 (slow query log)

Time:2021-5-2

What is slow query?

MySQL optimization 4 (slow query log)
Note: by default, the database does not open slow query log. You need to set this parameter manually! Of course, it is generally not recommended to start this parameter if tuning is not required, because starting slow query log will bring certain performance impact more or less. Slow query log supports writing log records to files
First, we need to check whether the slow query function is enabled. First, we need to check whether the slow query function is enabled: Show variables like% slow_ query_ log%’;
MySQL optimization 4 (slow query log)
Will show whether to open and log file location!
Enable slow query function:
set global slow_ query_ Log = 1 enables slow query log, which is only effective for the current database. If MySQL is restarted, it will be invalid!
So how slow is slow query slow?
This is determined by the parameter long_ query_ Time control, long by default_ query_ The value of time is 10 seconds
View the system variable: Show variables like ‘long’_ query_ time%’;
MySQL optimization 4 (slow query log)
By default, 10s is considered slow. This is not OK, so let’s set the slow threshold time
set global long_query_time=3;
The display setting is successful, but let’s check the threshold again, but it’s still 10 seconds. Why?
This is because we need to re connect to MySQL or open a new session to see the modified value!
Because there is no real environment, we choose a database, such as Zhishi database, and simulate slow SQL
select sleep(4);// It means to execute after 4 seconds
Then, after the execution meeting, there will be records in the slow query SQL log
In which database, the real execution time and which SQL are recorded!
Then we find the slow SQL, analyze the explain, and create the index
How to check how many slow SQL are in my slow SQL log?
show global status like ‘%Slow_queries%’;
MySQL optimization 4 (slow query log)
At present, there is a slow SQL!
Generally, it is not recommended to start slow SQL for a long time. If you want to start slow SQL for a long time, the configuration in mysql.ini is as follows:
MySQL optimization 4 (slow query log)

show profile:

We started slow query, there may be a lot of slow SQL statements, but we want to know the specific reason for the slow? Is the link time slow or the SQL optimizer part slow or what’s going on? You can use show profile to analyze a single SQL. What is it? It is provided by mysql, which can be used to analyze the resource consumption of statement execution in the current session. It can be used for SQL tuning measurement. By default, the parameters are closed and the running results of the last 15 times are saved
First, check the show profile status
show variables like ‘profiling’;
MySQL optimization 4 (slow query log)
Open show profile
set profiling = on;
When we open it, any SQL statements that we execute on the database will be recorded
We just need to execute show profiles;
MySQL optimization 4 (slow query log)
Then the SQL that we have executed in MySQL is exposed, and the execution time of this SQL is included!
For example, we can see that the execution time of one SQL in the result set of show profiles is relatively long
Show profile CPU, block io for query_ The value of ID / / SQL is slow, either the CPU calculation is complex or the IO overhead is frequent, so we only look at the CPU and block IO!
show profile cpu,block io fro query 6; Then we see the whole SQL declaration cycle
MySQL optimization 4 (slow query log)
What do you think of this declaration cycle? The main point is to see the status. If there are four situations in the figure below, then this SQL must be causing trouble
MySQL optimization 4 (slow query log)
Let’s take a look at the declaration cycle of SQL with execution time of more than 3S. We will find that there are four situations in the figure above, which indicates that the SQL must be problematic;
MySQL optimization 4 (slow query log)

Query interception analysis:
1. Observe and run for at least one day to see the slow SQL generated
2. Open the slow query log, set the threshold. For example, if the time exceeds 5S, it is slow SQL and grab it out
3. Explain + slow SQL analysis
4.show profile
5. The operation and maintenance manager or DBA can tune the parameters of SQL server
Conclusion:
a. Opening and capturing slow queries
b. Explain + slow SQL analysis
c. Show profile query the execution details and declaration cycle of SQL in MySQL server
4. The parameter tuning of SQL database server is not studied when DBA is active. 4. The parameter tuning of SQL database server is not studied when DBA is active

This work adoptsCC agreementReprint must indicate the author and the link of this article

Hu Jun

Recommended Today

Notes on basic learning of ruby metaprogramming

Note 1:The code contains variables, classes and methods, which are collectively referred to as language construct. ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 # test.rb class Greeting  def initialize(text)   @text = text  end    def welcome   @text  end end my_obj = Greeting.new(“hello”) […]