Example of MySQL slow query

Example of MySQL slow query

Introduction

By enabling the slow query log, MySQL can record query statements that exceed the specified time. By locating and analyzing performance bottlenecks, the performance of the database system can be better optimized.

1. Configure slow query

1. Parameter Description

  • slow_query_log : Slow query enable status (off by default)
  • slow_query_log_file: The location where the slow query log is stored (this directory requires writable permissions for the MySQL running account, and is generally set to the MySQL data storage directory)
  • long_query_time: How many seconds does it take for a query to be recorded (10 seconds by default)

2. View slow query related parameters

show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------+----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /mysql/data/localhost-slow.log |
+---------------------------+----------------------------------+

show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

3. Configure slow queries

It has two configuration methods, one is global variable configuration and the other is configuration file configuration

(1) Global variable configuration

--Set the slow_query_log global variable to "ON" state set global slow_query_log='ON'; 

--Set the location where the slow query log is stored set global slow_query_log_file='/usr/local/mysql/data/slow.log';

--Record if the query exceeds 1 second set global long_query_time=1;

(2) Modify the configuration file my.cnf (under Linux environment)

  slow_query_log = ON
  slow_query_log_file = /usr/local/mysql/data/slow.log
  long_query_time = 1

Summarize:
(1) After configuring through global variables, you need to close the session and reopen the query to be effective. Through the configuration file, you need to restart the Mysql server to be effective. (2) Because enabling slow query will affect performance, it is generally recommended to configure through global variables. In this way, restarting the server will turn off the slow query state by default.

2. mysqldumpslow tool

mysqldumpslow is a tool that comes with MySQL. With it, we don't have to look for it in the log file above. If the data is ordinary, then checking the log is also a tedious task.

1. Main commands

--s: indicates the sorting method--c: number of visits--l: lock time--r: returned records--t: query time--al: average lock time--ar: average number of returned records--at: average query time--t: how many records are returned in front--g: followed by a regular matching pattern, case-insensitive

2. Examples

--1. Get the 10 SQL statements that return the most records
  mysqldumpslow -s -r -t 10 /logs/mysql-slow.log

 --2. Get the top 10 SQLs with the highest number of visits
  mysqldumpslow -s -c -t 10 /logs/mysql-slow.log

 --3. Get the first 10 query statements containing links sorted by time mysqldumpslow -st -t 10 -g "left join" /logs/mysql-slow.log
 
 --4. In addition, it is recommended to use | and more when using these commands, otherwise the screen may explode. mysqldumpslow -sr -t 10 /logs/mysql-slow.log | more

3. show profile

Show profiles was added after 5.0.37. To use this feature, make sure the version is after 5.0.37.

1. Enable show profile

show variables like "profiling";--The default is closed+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+

--enable set profiling=1;

2. Run the specified SQL

I run a few SQL statements here, and then run

show profiles;--will list all SQL statements executed during this opening period, with the QUERY ID attached
+----------+------------+----------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------+
| 1 | 0.00168025 | select * from vhr.employee |
| 2 | 0.06573200 | select * from vhr.hr |
+----------+------------+----------------------------+
--We can see two statements showing the most recent operations. The list size is controlled by the profiling_history_size session variable. The default value is 15 and the maximum value is 100.

3. Diagnose specific SQL

show profile cpu,block io for queryid -- corresponds to query_id in 2

SHOW PROFILE CPU FOR QUERY 1;--Query the specific information of query_id 1+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000194 | 0.000000 | 0.000000 |
| checking permissions | 0.000012 | 0.000000 | 0.000000 |
| Opening tables | 0.000030 | 0.000000 | 0.000000 |
| init | 0.000053 | 0.000000 | 0.000000 |
| System lock | 0.000011 | 0.000000 | 0.000000 |
| optimizing | 0.000003 | 0.000000 | 0.000000 |
| statistics | 0.000014 | 0.000000 | 0.000000 |
| preparing | 0.000010 | 0.000000 | 0.000000 |
| executing | 0.000001 | 0.000000 | 0.000000 |
| Sending data | 0.001213 | 0.000000 | 0.000000 |
| end | 0.000014 | 0.000000 | 0.000000 |
| query end | 0.000012 | 0.000000 | 0.000000 |
| closing tables | 0.000019 | 0.000000 | 0.000000 |
| freeing items | 0.000070 | 0.000000 | 0.000000 |
| cleaning up | 0.000025 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+

Conclusions that need to be noted in daily development:

1 converting HEAP to MyISAM: The query results are too large to fit in memory, so they are moved to disk;
2 creating tmp table: Create a temporary table, copy data to the temporary table, and then delete it;
3 copying to tmp table on disk: copy the temporary table in memory to disk, dangerous! ! !
4 locked

Note: If one or more of the above four appear, it means that the SQL statement must be optimized.

The above is an example to explain the details of MySQL slow query. For more information about MySQL slow query, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MySQL slow query pitfalls
  • MYSQL slow query and log example explanation
  • The role and opening of MySQL slow query log
  • MYSQL slow query and log settings and testing
  • Enabling and configuring MySQL slow query log
  • Mysql sql slow query monitoring script code example
  • How to locate MySQL slow queries
  • MySQL slow query method and example
  • Detailed explanation of the reason why the MySQL 5.7 slow query log time is 8 hours behind the system time
  • Mysql slow query optimization method and optimization principle
  • How to optimize MySQL performance through MySQL slow query
  • Solve the problem of MySQL Threads_running surge and slow query

<<:  js to realize simple shopping cart function

>>:  Introduction to several ways to introduce CSS in HTML

Recommend

15 important variables you must know about MySQL performance tuning (summary)

Preface: MYSQL should be the most popular WEB bac...

Implementation of nginx worker process loop

After the worker process is started, it will firs...

Do you know all 24 methods of JavaScript loop traversal?

Table of contents Preface 1. Array traversal meth...

How to prevent the scroll bar from affecting the page width when the scroll bar appears on the page

Set the width of the body to the width of the wind...

Detailed explanation of how to use the Vue license plate search component

A simple license plate input component (vue) for ...

Analysis of rel attribute in HTML

.y { background: url(//img.jbzj.com/images/o_y.pn...

Detailed explanation of the role of explain in MySQL

1. MYSQL index Index: A data structure that helps...

Summary of the differences between count(*), count(1) and count(col) in MySQL

Preface The count function is used to count the r...

Example of how to use CSS3 to layout elements around a center point

This article introduces an example of how CSS3 ca...

How to build pptpd service in Alibaba Cloud Ubuntu 16.04

1. To build a PPTP VPN, you need to open port 172...

Detailed usage of MYSQL row_number() and over() functions

Syntax format: row_number() over(partition by gro...

In-depth explanation of the global status of WeChat applet

Preface In WeChat applet, you can use globalData ...

Javascript operation mechanism Event Loop

Table of contents 1. Four concepts 1. JavaScript ...

Let's talk about the LIMIT statement in MySQL in detail

Table of contents question Server layer and stora...

Pure CSS3 code to implement a running clock

Operation effectCode Implementation html <div ...