When we started to focus on overall database performance optimization, we needed a set of MySQL query analysis tools. Especially in medium to large projects, there are often hundreds of queries scattered around the code base, with a lot of access and queries to the database in real time. Without a set of handy analysis methods and tools, it is difficult to find efficiency bottlenecks in the execution of the code. We need to use this set of tools to locate the problems and causes of slow EXECUTION of SQL statements.
This tutorial guides you through learning and practicing MySQL Server’s built-in query analysis tools — slow query logging, mysqlDumpslow, and Profiling — and explains how to use them to improve code execution. Cara Cloud is recommended if you want to develop a database query management tool based on your workflow. As long as you can write SQL, you can easily build your own backend query tools without the need for front-end, see the end of this article.
MySQL > slow query log
1. What is a slow query log?
The MySQL slow query log is used to record the RESPONSE time of THE MySQL commands that exceeds the preset threshold.
Logging these slow-executing SQL statements is the first step in optimizing the efficiency of the MySQL database.
By default, the slow log query function is disabled. You need to manually enable it. Of course, if not for tuning purposes, it is generally not recommended to enable this feature on a long-term basis, since turning on slow queries can have some impact on database performance. Slow query logging allows records to be written to files or directly to tables in the database.
2. Configure and enable slow query logs
(1) Temporarily enable the slow query function in MySQL Server
In MySQL Server, the slow query function is disabled by default. You can check the status of this function
show variables like 'slow_query_log';
Copy the code
As shown in the figure above, the status of the slow query log (slow_query_log) is off.
To enable and configure the slow query log function, run the following command in mysql:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/kalacloud-slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 100;
Copy the codeSET GLOBAL slow_query_log: enables the slow query function globally.
SET GLOBAL slow_query_log_file: specifies the address and file name of the slow query log file.
SET GLOBAL log_QUERies_NOT_USING_INDEXES: Records that are not indexed are recorded whether or not they have timed out.
SET SESSION long_QUERy_time: slow query threshold (seconds). When SQL execution exceeds this threshold, it will be recorded in logs.
SET SESSION MIN_examined_ROW_LIMIT: Slow query records only THE SQL whose number of scanned rows is greater than this parameter.
** Note in particular: ** in practice it is common to encounter logs that are not logged no matter how low the slow query threshold is set. Min_examined_row_limit: The number of rows in the min_examined_ROW_limit class is too large to be recorded. Note that min_examined_ROW_LIMIT is often ignored in configuration.
Now let’s execute the query and look at the configuration. (execute in MySQL Server)
show variables like 'slow_query_log%';
show variables like 'log_queries_not_using_indexes';
show variables like 'long_query_time';
show variables like 'min_examined_row_limit';
Copy the code
The above modification method is used to temporarily monitor the running status of the MySQL database. When the MySQL Server restarts, all the modifications become invalid and are restored to the original state.
Six types of MySQL triggers use tutorials and application scenarios
(2) Write the slow query setting into the MySQL configuration file, which takes effect permanently
Although slow queries can be dynamically set from the command line, the dynamic Settings will become invalid when the service is restarted. If you want to enable the slow query function for a long time, you need to write the Settings of the slow query into the MySQL configuration file, so that the Settings of the slow query remain unchanged no matter whether the server is restarted or MySQL is restarted.
The MySQL conf configuration file is usually in /etc or /usr. You can use the find command to find the location of the configuration file.
sudo find /etc -name my.cnf
Copy the code
Once the location is found, edit my.cnf using nano to write the slow query Settings to the configuration file.
sudo nano /etc/mysql/my.cnf
Copy the code[mysqld]
slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes
Copy the codeUse nano to open the configuration file and write the above code below [mysqld]. CTRL +X to save and exit.
sudo systemctl restart mysql
Copy the codeRestart the MySQL Server service to make the modified configuration file take effect.
** Special note: ** Slow query dynamic variables set directly on the command line have a different syntax than writing directly to the my.cnf configuration file.
Read more: 10 MySQL Management Tools – Free vs. Paid
For example, the dynamic variable is slow_query_log and the write configuration file is slow-query-log. Be careful here.
For more information on MySQL 8.0 dynamic variable syntax, see the MySQL documentation.
Use the slow query function to record logs
At this point we have configured everything we need for the slow query function. Let’s write an example where we will learn how to view and analyze slow query logs.
You can open two command line Windows connected to the server, one for writing MySQL code and one for viewing logs.
Note: In this tutorial, some of the code is executed on the command line and some is executed in MySQL Server.
Log in to MySQL Server, create a database, and write a set of sample data.
CREATE DATABASE kalacloud_demo;
USE kalacloud_demo;
CREATE TABLE users ( id TINYINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) );
INSERT INTO users (name) VALUES ('Jack Ma'),('Lei Jun'),('Wang Xing'),('Pony Ma'),('Zhang YiMing'),('Ding Lei'),('Robin Li'),('Xu Yong'),('Huang Zheng'),('Richard Liu');
Copy the codeTo keep you consistent with the tutorial configuration, let’s use dynamic variables together and set the slow query parameters.
Execute the following SQL code in MySQL Server:
SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/kalacloud-slow.log';
SET GLOBAL log_queries_not_using_indexes = 1;
SET long_query_time = 10;
SET min_examined_row_limit = 0;
Copy the codeWe now have a sample database with data in a table. The slow query function has also been enabled, and we specifically set the time threshold (long_query_time) to 10 and the min_examined_ROW_limit to 0.
Let’s run a code test:
USE kalacloud_demo;
SELECT * FROM users WHERE id = 1;
Copy the codeSelect queries on tables using primary key indexes are very fast and use indexes. Therefore, there is no record in the slow query log.
Let’s open the slow query log to verify that there are records, execute the following command on the command line:
sudo cat /var/log/mysql/kalacloud-slow.log
Copy the codeYou can seekalacloud-slow.logThere’s no record of anything yet.
Next we execute the following code in MySQL Server:
SELECT * FROM users WHERE name = 'Wang Xing';
Copy the codeThis query code uses a non-indexed column (name) for the query, so the slow query log will record the query.
We open the log to see the changes recorded:
sudo cat /var/log/mysql/kalacloud-slow.log
Copy the code
We can see that the non-index query has been logged in the slow query log.
Here’s another example. We raise the min_examined_ROW_limit to 100, and then execute the query.
Execute the following code in MySQL Server:
SET min_examined_row_limit = 100;
SELECT * FROM users WHERE name = 'Zhang YiMing';
Copy the codeAfter execution, open kalacloud-slow.log again and you can see that the query with less than 100 lines is not logged.
Note: If no data is recorded in the slow query log, check the following.
(1) The permission of the directory where logs are created is incorrect.
cd /var/log
mkdir mysql
chmod 755 mysql
chown mysql:mysql mysql
Copy the code(2) The other possibility is the query variable configuration problem, clean the slow query configuration in the my.conf file, then restart the service, and re-configure. See if there’s something wrong here.
How to save MySQL query results to a file
Slow query log parameter description
How should slow query logs be analyzed
Description of the log information:
Time: The running time of the logged code on the server.User@HostWho executed this code?Query_time: The running time of this code.Lock_time: How long was locked when this code was executed.Rows_sent: Records returned by slow query.Rows_examined: Slowly queries the number of scanned rows.
All code exceeding the threshold is logged. This information can be used to find inefficient MySQL query code, which can help us optimize MySQL performance.
MySQL > select * from table_name where table_name = ‘table_name’
Use the mysqlDumpslow tool to analyze slow query logs
In practice, the slow query log does not consist of just a few lines as described above. In reality, slow query logs record a large amount of slow query information and are frequently written. Log entries become longer and more difficult to analyze. Fortunately, MySQL has a built-in utility called mysqlDumpslow, which can group the same SQL and count the number of times the categorized items are executed and the time taken for each execution.
Let’s do a few lines of code to get the slow query log logged, and then analyze it with mysqlDumpSlow.
Min_examined_row_limit = 100; in this case, change it to 0. Execute the following code in MySQL Server:
SET min_examined_row_limit = 0;
Copy the codeThen we execute a few query commands:
SELECT * FROM users WHERE name = 'Wang Xing';
SELECT * FROM users WHERE name = 'Huang Zheng';
SELECT * FROM users WHERE name = 'Zhang YiMing';
Copy the codeAccording to the slow query setting above, all three records will be logged.
Now switch to the command-line window and execute the mysqlDumpslow command:
sudo mysqldumpslow -s at /var/log/mysql/kalacloud-slow.log
Copy the codeData returned:
SELECT * FROM users WHERE name = ‘S’; SELECT * FROM users WHERE name = ‘S’; SELECT * FROM users WHERE name = ‘S’;
The mysqlDumpslow command can also be used to summarize your own common requirements and save these scripts for later use.
mysqldumpslow -s at -t 10 kalacloud-slow.log: Top 10 SQL code with the longest average execution time.mysqldumpslow -s al -t 10 kalacloud-slow.log: The top 10 SQL codes with the longest average lock time.mysqldumpslow -s c -t 10 kalacloud-slow.log: The top 10 SQL codes executed most frequently.mysqldumpslow -a -g 'user' kalacloud-slow.log: Display alluserThe specific value of the SQL code associated with the tablemysqldumpslow -a kalacloud-slow.log: Displays the SQL code directly.
Parameter command for mysqlDumpslow
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 timeCopy the codeDescription of common parameters:
-s
- Al: Average lock time
- At: Average query time [default]
- Ar: average return record time
- C: count Total execution times
- L: Lock time
- R: Returns a record
- T: Query time
-t: Returns the first N items of data
-g: a regular expression that can be written, similar to the grep command, to filter out the required information. For example, only slow query records for table X are queried.
-r: rows sent Total number of returned rows.
The mysqlDumpslow log query tool is useful because it is extremely flexible and can merge parsed slow query logs of the same type. We can see why mysqlDumpSlow is useful in our daily work.
The parameters for using mysqlDumpslow can also be found in the MySQL 8.0 user manual.
How to check the size of MySQL database, table, index? Find the table that occupies the most space
Profilling – MySQL performance analysis tool
To better pinpoint performance problems with an SQL statement, we need to break down where and how much resources the statement consumes while running. We can use the Profilling tool for this kind of detailed analysis. The Profilling tool can be used to obtain details of the consumption of various resources during the execution of an SQL statement.
After entering MySQL Server, execute the following code to start Profilling
SET SESSION profiling = 1;
Copy the codeCheck the status of profiling
SELECT @@profiling;
Copy the codeReturned data: 0 indicates that the function is disabled, and 1 indicates that the function is enabled.
Execute the SQL statement to locate the problem.
USE kalacloud_demo;
SELECT * FROM users WHERE name = 'Jack Ma';
Copy the codeView the SQL statement status.
SHOW PROFILES;
Copy the codeAfter profiling is turned on, SHOW PROFILES; A table that links Query_ID to the SQL statement is displayed.
Query_ID: INDICATES the ID of the SQL statement.Duration: Indicates the execution duration of the SQL statement.Query: Indicates a specific SQL statement.
Execute the following SQL code to replace [# Query_ID] with the number of the SQL code Query_ID that we want to analyze.
SHOW PROFILE CPU, BLOCK IO FOR QUERY [# Query_ID];
Copy the codenamely
SHOW PROFILE CPU, BLOCK IO FOR QUERY 4;
Copy the code
Status is the specific step in the query execution process, Duration is the time (in seconds) required to complete the step.
Based on these details, we can analyze how to optimize the corresponding SQL code.
Six. Slow query tutorial summary
Slow query is a tool that lets us see the real running state of the database, which has guiding significance for server and database performance optimization. Whether in production, development, or QA, you can carefully turn on slow queries to log performance.
We can set the dynamic variable long_query_time to a larger size, observe it, and then fine-tune it. With slow query logging, we have a direction and goal to optimize performance, and then use mysqlDumpslow and Profiling for macro and micro log analysis. Find the details of inefficient SQL statements and fine-tune them so that our system can achieve optimal execution performance.
If you periodically check the log, you can use kara Cloud to build a log kanban. You can not only view and analyze the data by yourself, but also share the data with your group partners with one click.
Cara Cloud is a new generation of low-code development tools, free of installation and deployment, with one-click access to common databases and apis including MySQL. Not only can be as flexible as the command line, but also according to their own workflow, customized development. No tedious front-end development, just simple drag and drop, can quickly build enterprise internal tools. Months of development work can be reduced to a few days after using Cara cloud. Welcome to use my Cara cloud.
Cara Cloud provides quick access to common databases and apis
Kara Cloud can easily build data kanban according to the workflow requirements of the company, and can share data with partners in the group
A single line of code can map data to a specified component by dragging and dropping the front-end code.
Cara cloud can directly add export button, export data format suitable for all kinds of analysis software, convenient and fast. Open cara cloud immediately, quickly build your own background management system.
MySQL > MySQL > MySQL
- How to remotely connect to MySQL database, Tencent cloud exnet connection tutorial
- How to import and export data from MySQL/MariaDB, database files, Excel, and CSV
- How to migrate MySQL database between two servers ali Cloud Tencent cloud migration case
- How to implement BLOB data type access in MySQL? What are the application scenarios of BLOB?
- How to use MySQL Workbench to operate MySQL/MariaDB database