Article catalogue
MySQL - slow query log analysis
MySQL's slow query log is a kind of log record provided by mysql. It is used to record the statements whose response time exceeds the threshold in mysql, specifically, the running time exceeds long_ query_ The SQL with time value will be recorded in the slow query log. long_ query_ The default value of time is 10.
Through slow query logs, you can find out which query statements are inefficient for optimization.
If it is not necessary for tuning, it is generally not recommended to start this parameter, because starting the slow query log will more or less have a certain performance impact.
long_ query_ The default value of time is 10
mysql> SHOW VARIABLES LIKE 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
1. Start and set slow query log
Check whether the slow query log function is enabled
-
slow_query_log: slow query on status
-
slow_query_log_file: the location where the slow query log is stored (generally set as the data storage directory of MySQL)
mysql> SHOW VARIABLES LIKE 'slow_query%';
±--------------------±---------------------------+
| Variable_name | Value |
±--------------------±---------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /data/mysql/CHENG-slow.log |
±--------------------±---------------------------+
2 rows in set (0.00 sec)
Enable slow query log function
Method 1: open the configuration file (permanently valid)
log_slow_queries option and long_ query_ The time option is added to the [mysqld] group of the configuration file. The format is as follows:
[mysqld] show_query_log=on/off # On or off log-slow-queries=dir/filename long_query_time=n log_output=file
Of which:
- The dir parameter specifies the storage path of the slow query log. If the storage path is not specified, the slow query log will be stored in the data folder of the MySQL database by default.
- The filename parameter specifies the file name of the log. The full name of the generated log file is filename slow log. If you do not specify a file name, the default file name is hostname slow Log, hostname is the hostname of the MySQL server.
- "n" parameter is the set time value, and the unit of this value is seconds. If long is not set_ query_ Time option, the default time is 10 seconds.
Method 2: command to start slow query log
SET GLOBAL slow_query_log=on/off; -- On or off -- Global and current session All need to be modified SET GLOBAL long_query_time=1; SET long_query_time=1;
2. Data preparation
1. Build table
create table student(
id int(11) not null auto_increment,
stuno int not null ,
name varchar(20) default null,
age int(3) default null,
classID int(11) default null,
primary key (id)
)engine=innodb auto_increment=1 default charset=utf8;
2. Create function:
Open create function:
set global log_bin_trust_function_creators = 1;
Function 1: randomly generate string function
delimiter //
create function rand_string(n int)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwsyzABCDEFGJHIKLMNOPQRSTUVWSYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while ;
return return_str;
end //
delimiter ;
Function 2: random number generation function
delimiter //
create function rand_num(from_num int , to_num int) returns int(11)
begin
declare i int default 0;
set i = floor(from_num+rand()*(to_num - from_num + 1));
return i;
end //
delimiter ;
3. Create stored procedure
delimiter //
create procedure insert_stu1(start int,max_num int)
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into student(stuno, name,age,classID) VALUES
((start+1),rand_string(6),rand_num(10,100),rand_num(10,1000));
until i = max_num
end repeat ;
commit;
end //
delimiter ;
4. Call stored procedure
call insert_stu1(100001,4000000);
5. View data addition results
mysql> select count(*) from student; +----------+ | count(*) | +----------+ | 4000000 | +----------+ 1 row in set (1.40 sec)
6. View the number of sql recorded in the slow query log
mysql> show status like 'slow_queries'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 1 | +---------------+-------+ 1 row in set (0.01 sec)
3. Slow query log analysis tool: mysqldumpslow
mysqldumpslow is a log analysis tool provided by MySQL to help us analyze logs, find and analyze SQL.
View the usage of mysqldumpslow
[root@CHENG bin]# mysqldumpslow -help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
- -s is the order of order
- al average locking time
- ar average return recording time
- at average query time (default)
- c count
- Lock time
- r return record
- t query time
- -t means top n, that is, how many pieces of data are returned
- -g can be followed by a regular matching pattern, which is case insensitive
bash: mysqldumpslow: command not found
This error is because mysqldumpslow is not under /usr/bin. We need to find the installation directory of mysqldumpslow (usually under the installation directory of MySQL), and then make a soft link to /usr/bin. My mysqldumpslow path is /usr/local/mysql/bin
ln -s /usr/local/mysql/bin/mysqldumpslow /usr/bin
Use of mysqldumpslow
# /data/mysql/CHENG-slow.log slow query log path [root@CHENG mysql]# mysqldumpslow -a -s t -t 5 /data/mysql/CHENG-slow.log Reading mysql slow query log from /data/mysql/CHENG-slow.log Count: 1 Time=356.29s (356s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[58.242.191.115] /* ApplicationName=DataGrip 2021.1 */ call insert_stu1(100001,4000000) # Call sql of stored procedure Count: 1 Time=1.39s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost select count(*) from student Died at /usr/bin/mysqldumpslow line 167, <> chunk 2.
4. Delete slow query log
The deletion method of slow query logs is the same as that of general logs. You can use the mysqladmin command to delete. It can also be deleted manually (rm -rf). The syntax of the mysqladmin command is as follows: the mysqladmin command will regenerate the query log file
mysqladmin -uroot -p flush-logs slow
After executing the command, the command line prompts for a password. After entering the correct password, the deletion operation will be performed. The new slow query log will directly overwrite the old query log, and there is no need to delete it manually.
The database administrator can also delete the slow query log manually. After deletion, the MySQL service needs to be restarted.
Note: both the general query log and the slow query log use this command. Be sure to note that once this command is executed, both the general query log and the slow query log only exist in the new log file. If you need to back up the old slow query log file, you must first rename the old log, and then restart the MySQL service or execute the mysqladmin command.
5,show profile
Show Profile is a tool provided by MySQL that can be used to analyze what SQL has done and the resource consumption of execution in the current session, and can be used to measure SQL tuning. It is off by default, and the results of the last 15 runs are saved|
Check whether show profile is enabled
mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ 1 row in set (0.00 sec)
Open show profile
mysql> set profiling = 'ON'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | ON | +---------------+-------+ 1 row in set (0.02 sec)
View the profiles of the current session
mysql> show profiles; +----------+------------+-----------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------+ | 1 | 0.02887100 | show variables like 'profiling' | | 2 | 0.00010500 | select * from student where stuno = 1234567 | | 3 | 0.00062425 | show databases | | 4 | 0.00016825 | SELECT DATABASE() | | 5 | 0.00034500 | show databases | | 6 | 0.01144500 | show tables | | 7 | 2.41117450 | select * from student where stuno = 1234567 | | 8 | 1.27697000 | select * from student where stuno = 1231234 | | 9 | 1.33853225 | select * from student where stuno = 3453451 | | 10 | 0.00023825 | show create table student | | 11 | 0.00043500 | select * from student where id = 1231231 | | 12 | 1.23249650 | select * from student where classID = 1231231 | | 13 | 1.29235175 | select * from student where classID = 1000000 | | 14 | 1.45108200 | select * from student where name = 'jnoefp' | +----------+------------+-----------------------------------------------+ 14 rows in set, 1 warning (0.00 sec)
View the cost of the latest query
mysql> show profile; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000093 | | checking permissions | 0.000009 | | Opening tables | 0.000022 | | init | 0.000033 | | System lock | 0.000011 | | optimizing | 0.000020 | | statistics | 0.000022 | | preparing | 0.000016 | | executing | 0.000004 | | Sending data | 1.437393 | | end | 0.000027 | | query end | 0.000013 | | closing tables | 0.000013 | | freeing items | 0.000679 | | cleaning up | 0.012730 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)
View the cost of a specified query
Query query_ Cost with ID 3:
mysql> show profile for query 3; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000114 | | checking permissions | 0.000011 | | Opening tables | 0.000050 | | init | 0.000014 | | System lock | 0.000008 | | optimizing | 0.000007 | | statistics | 0.000015 | | preparing | 0.000016 | | executing | 0.000314 | | Sending data | 0.000019 | | end | 0.000005 | | query end | 0.000008 | | closing tables | 0.000004 | | removing tmp table | 0.000007 | | closing tables | 0.000005 | | freeing items | 0.000015 | | cleaning up | 0.000015 | +----------------------+----------+ 17 rows in set, 1 warning (0.00 sec)
Common query parameters of show profile
- ALL: display ALL overhead information.
- BLOCK IO: displays the BLOCK IO overhead.
- CONTEXT SWITCHES: context switching overhead.
- Cpu: displays CPU overhead information.
- IPC: displays send and receive overhead information.
- MEMORY: displays MEMORY overhead information.
- PAGE FAULTS: displays page error overhead information.
- SOURCE: display and Source_function，Source_file，Source_line related overhead information.
- Swap: displays the cost information of the number of exchanges.
First of all, I would like to introduce myself. I graduated from Jiaotong University in 13 years. I once worked in a small company, went to large factories such as Huawei OPPO, and joined Alibaba in 18 years, until now. I know that most junior and intermediate Java engineers who want to improve their skills often need to explore and grow by themselves or sign up for classes, but there is a lot of pressure on training institutions to pay nearly 10000 yuan in tuition fees. The self-study efficiency of their own fragmentation is very low and long, and it is easy to encounter the ceiling technology to stop. Therefore, I collected a "full set of learning materials for java development" and gave it to you. The original intention is also very simple. I hope to help friends who want to learn by themselves and don't know where to start, and reduce everyone's burden at the same time. Add the business card below to get a full set of learning materials