How to enable and analyze slow query logs in MySQL?
This article will take you to understand the slow query in MySQL, introduce the method of enabling the slow query log, and analyze the slow query. I hope it will be helpful to everyone!
Mysql has a function that can record slow records of long-term queries. The specific length of time can be configured by yourself, but slow queries are not necessarily bad SQL, and may also be affected by It is also a commonly used performance analysis tool that affects other queries or is caused by system resource limitations. [Related recommendations: mysql video tutorial]
MySQL’s slow query log is controlled by long_query_time
and min_examined_row_limit
. The former means if the query costs The time exceeds so many seconds, the MySQL server records this to the slow query log file when the slow query log is enabled.
Enable slow query log
To enable slow query log in MySQL, please follow the steps below:
First take a look at the slow query log in MySQL The status of the query.
MariaDB [(none)]> show variables like '%slow%'; +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ | log_slow_admin_statements | ON | | log_slow_disabled_statements | sp | | log_slow_filter | admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | | log_slow_rate_limit | 1 | | log_slow_slave_statements | ON | | log_slow_verbosity | | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | hxl-slow.log | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ 9 rows in set (0.001 sec)
First look at the status of slow_query_log
, which represents whether the slow query log is enabled. The value can be 0 or OFF to disable, 1 or ON to enable. The destination of the log output is controlled by the log_output
system variable. If the value is NONE
, even if the slow query log is enabled, it will not be written to the log. log_output
The values of the variables are as follows:
- TABLE: (Default) Write general queries to the
mysql.general_log
table and write general queries to the tablemysql.slow_log
Writing slow queries. - FILE: Write general and slow query logs to the file system.
- NONE – Disable logging.
Also general_log_file
and slow_query_log_file
variables represent the names of the query log and slow query log files. These variables can be set when the server is started or running.
Start the slow query log
The slow query log function is turned off by default in MySQL, so to turn on this function, we need to set slow_query_log
to ON as shown below.
SET GLOBAL slow_query_log = 1;
Set the recording duration
long_query_time
The variable indicates that only the running time exceeding this value will be recorded, as shown below, change the time to 5 Seconds, it defaults to 10 seconds and the minimum value is 0.
SET GLOBAL long_query_time = 5;
Modify the output location
By default, the slow query log file is located at /var/lib/mysql/hostname-slow.log
, We can also set another location using the slow_query_log_file
variable.
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
Write the slow query log to the table
The slow query log can also be written to the slow_log
table, the method is as mentioned above Modify the log_output
system variable to TABLE
, the operation is as follows:
SET GLOBAL log_output='TABLE';
Slow query log related variables
- slow_query_log - 启用/禁用慢查询日志
- log_output - 如何写入输出
- slow_query_log_file - 慢查询日志文件的名称
- long_query_time - 定义慢查询的时间(以秒/微秒为单位)
- log_queries_not_using_indexes - 是否记录不使用索引的查询
- log_slow_admin_statements - 是否记录某些管理语句
- log_slow_disabled_statements - 不应记录在慢查询日志中的语句类型
- min_examined_row_limit - 查询必须检查的最小行数才能变慢
- log_slow_rate_limit - 允许记录一部分慢查询
- log_slow_verbosity - 日志中的详细信息量
- log_slow_filter - 限制要记录的查询
分析慢查询
日志记录后,我们还需要分析这些日志,找出影响系统的查询,MySQL提供了一个名为mysqldumpslow的工具,可以简单的显示结果,只需要把日志路径传递给他即可,如下:
# mysqldumpslow -a mysql-slow.log Reading mysql slow query log from mysql-slow.log Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=21.0 (21), Rows_examined=21.0 (21), Rows_affected=0.0 (0), root[root]@localhost show databases Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=2.0 (2), Rows_examined=2.0 (2), Rows_affected=0.0 (0), root[root]@localhost select * from users Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost select @@version_comment limit 1 Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
他的参数如下
-
-s ,按照什么方式起来排序。默认at,也就是按照平均查询时间来排序。都是按照倒序排列。
al: average lock time 平均锁定时间
ar: average rows sent 平均返回行数
at: average query time 平均查询时间
c: count 总执行次数
l: lock time 总锁定时间
r: rows sent 总返回行数
t: query time 总查询时间
-t ,show the top n queries,显示前多少名的记录
-a ,默认不开启这个选项。mysqldumpslow将相似的SQL的值(字符串或者数字)替换为N,开启该选项,则显示真实值。不开启该选项,有点类似于Oracle的绑定变量的记录。\
-g ,类似于grep命令,过滤出需要的信息。如,只查询A表的慢查询记录。\
-l ,总时间中包含锁定时间
原文地址:https://juejin.cn/post/6993221989175394311
更多编程相关知识,请访问:编程视频!!
The above is the detailed content of How to enable and analyze slow query logs in MySQL?. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.
