MySQL turn on slow query log

1.1 Introduction

Turn on the slow query log, you can let the MySQL record query over a specified time of the statement, through the location analysis of performance bottlenecks, to better optimize the performance of the database system.

1.2 Login Database View

[Email protected] lib]# Mysql–uroot

Because there is no setting password, password is mysql–uroot–p.

1.2.1 Enter MySQL query whether to open a slow query

Mysql> Show variables like ' slow_query% ';

+---------------------+--------------------------------------------+

| variable_name | Value |

+---------------------+--------------------------------------------+

| Slow_query_log | OFF |

| Slow_query_log_file | /application/mysql/data/localhost-slow.log |

+---------------------+--------------------------------------------+

2 rows in Set (0.00 sec)

Parameter description:

Slow_query_log slow query on status OFF does not open on to open
Slow_query_log_file the location of the slow query log (this directory requires the writable permissions of the MySQL running account, generally set to the MySQL data storage directory)

1.2.2 View slow query time-out

Mysql> Show variables like ' long% ';

+-----------------+-----------+

| variable_name | Value |

+-----------------+-----------+

| Long_query_time | 10.000000 |

+-----------------+-----------+

1 row in Set (0.00 sec)

Long_query_time query over how many seconds to record default 10 seconds modified to 1 seconds

1.3 Modification Method 1: (Not recommended)

Method One: The advantage of temporarily open slow query, do not need to restart the database disadvantage: MYSQL Restart slow query invalidation

Recommendation: According to business needs, the second type is recommended, temporary can be used in the first

By default, the value of Slow_query_log is off, which means that the slow query log is disabled and can be turned on by setting the value of Slow_query_log, as follows: whether to turn on the slow query log, 1 to open, and 0 to close.

1.3.1 to see if slow queries are turned on

Mysql> Show variables like '%slow_query_log% ';

+---------------------+--------------------------------------------+

| variable_name | Value |

+---------------------+--------------------------------------------+

| Slow_query_log | OFF |

| Slow_query_log_file | /application/mysql/data/localhost-slow.log |

+---------------------+--------------------------------------------+

2 rows in Set (0.01 sec)

Input statement modification (invalid after reboot, recommended for permanent change in/etc/my.cnf)

mysql> set global slow_query_log=1;

Query OK, 0 rows affected (0.11 sec)

1.3.2 View again

Mysql> Show variables like '%slow_query_log% ';

+---------------------+--------------------------------------------+

| variable_name | Value |

+---------------------+--------------------------------------------+

| Slow_query_log | On |

| Slow_query_log_file | /application/mysql/data/localhost-slow.log |

+---------------------+--------------------------------------------+

2 rows in Set (0.00 sec)

1.4 Modification Method 2: (recommended)

Modify MYSQL Slow query, many people do not know my.cnf path, you can use Find find

Note: My MySQL is compiled with a path of/etc/my.cnf (usually here)

[[email protected] log]# Find/-type f-name "MY.CNF"

/application/mysql-5.5.51/mysql-test/suite/rpl/my.cnf

/application/mysql-5.5.51/mysql-test/suite/federated/my.cnf

/application/mysql-5.5.51/mysql-5.5.51-linux2.6-x86_64/mysql-test/suite/rpl/my.cnf

/application/mysql-5.5.51/mysql-5.5.51-linux2.6-x86_64/mysql-test/suite/federated/my.cnf

/ETC/MY.CNF # # # (usually here)

1.4.1.1 modification

[Email protected] log]# VIM/ETC/MY.CNF

Find [mysqld] below to add

Slow_query_log =1

Slow_query_log_file=/application/mysql/data/localhost-slow.log

Long_query_time = 1

Parameter description:

Slow_query_log slow query on status 1 is turned on
Slow_query_log_file the location of the slow query log storage

Long_query_time query over how many seconds to record default 10 seconds modified to 1 seconds

Restart MySQL after modifying

1.5 viewing, testing 1.5.1.1 inserting a test slow query

Mysql> Select Sleep (2);

+----------+

| Sleep (2) |

+----------+

| 0 |

+----------+

1 row in Set (2.00 sec)

1.5.1.2 View Slow query log

[Email protected] data]# Cat/application/mysql/data/localhost-slow.log

/application/mysql/bin/mysqld, Version:5.5.51-log (MySQL Community Server (GPL)). Started with:

TCP port:3306 Unix Socket:/tmp/mysql.sock

Time Id Command Argument

/application/mysql/bin/mysqld, Version:5.5.51-log (MySQL Community Server (GPL)). Started with:

TCP port:3306 Unix Socket:/tmp/mysql.sock

Time Id Command Argument

/application/mysql/bin/mysqld, Version:5.5.51-log (MySQL Community Server (GPL)). Started with:

TCP port:3306 Unix Socket:/tmp/mysql.sock

Time Id Command Argument

# time:170605 6:37:00

# [email protected]: root[root] @ localhost []

# query_time:2.000835 lock_time:0.000000 rows_sent:1 rows_examined:0

SET timestamp=1496615820;

Select Sleep (2);

1.5.1.3 How many slow queries are viewed with the MySQL command

Mysql> show global status like '%slow_queries% ';

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| slow_queries | 1 |

+---------------+-------+

1 row in Set (0.00 sec)

1.6 Log Analysis Tool Mysqldumpslow

In a production environment, if you want to analyze logs manually, find and analyze SQL, it's obviously a physical activity, and MySQL provides log analysis tools Mysqldumpslow

MySQL turn on slow query log