mysql-Slow Query log

Source: Internet
Author: User
Tags benchmark

mysql-Slow Query log

tags (space delimited): MySQL

Turn on slow query log
慢查询是一种mysql提供的日志,记录所有执行时间超过某个时间界限的sql的语句。这个时间界限我们可以指定。在mysql中默认没有开启慢查询,即使开启了,只会记录执行的sql语句超过10秒的语句

[Way One]

Enter mysql:mysql-uroot-h127.0.0.1-proot# to view the parameters related to slow query show variables like ' slow_query% '; [Slow query is open and log location]show variables like ' long_query_time '; [Slow query time]mysql> show variables like ' slow_query% '; +---------------------+--------------------------------------+| variable_name | Value |+---------------------+--------------------------------------+| Slow_query_log | OFF | | Slow_query_log_file | /usr/local/mysql/var/cherry-slow.log |+---------------------+--------------------------------------+2 rows in Set ( 0.02 sec) mysql> Show variables like ' long_query_time '; +-----------------+-----------+| variable_name | Value |+-----------------+-----------+| Long_query_time | 10.000000 |+-----------------+-----------+1 row in Set (0.00 sec) # Open set global slow_query_log= ' on '; # Log storage location set global SLO w_query_log_file= '/usr/local/mysql/data/slow.log '; # Setting query time set global long_query_time=1;mysql> set global Slow_ Query_log = ' on ';Query OK, 0 rows affected (0.37 sec) mysql> set global slow_query_log_file = '/usr/local/mysql/var/cherry-slow.log '; Query OK, 0 rows affected (0.01 sec) mysql> Set global long_query_time = 1; Query OK, 0 rows Affected (0.00 sec)

[Way II]

# 修改配置文件vi /etc/my.cnf[mysqld]配置下    slow_query_log=ON    slow_query_log_file=‘/usr/local/mysql/data/slow.log‘;    long_query_time=1# 重启mysql服务service mysql restart
Test Slow Query

Select Sleep (2) [Execute a slow query SQL]

mysql> select sleep(2);+----------+| sleep(2) |+----------+|        0 |+----------+1 row in set (2.00 sec)

Benchmark (count,expr) [function to test the time required to perform the count expr operation]

mysql> select benchmark(900000000, 90000000*4);+----------------------------------+| benchmark(900000000, 90000000*4) |+----------------------------------++----------------------------------+1 row in set (10.80 sec)

Vi/usr/local/mysql/var/cherry-slow.log [view slow query log]

# Time: 180811 11:24:01# [email protected]: root[root] @ localhost []# Query_time: 5.000886  Lock_time: 0.000000 Rows_sent: 1  Rows_examined    : 0SET timestamp=1533957841;select sleep(5);# Time: 180811 11:34:16# [email protected]: root[root] @ localhost []# Query_time: 10.798234  Lock_time: 0.000000 Rows_sent: 1  Rows_examine    d: 0SET timestamp=1533958456;select benchmark(900000000, 90000000*4);
Slow query analysis

Show variables like '%general% '; [Information about the current database and version number]

Mysql> Show variables like '%quer% '; +-------------------------------+--------------------------------------+| variable_name | Value |+-------------------------------+--------------------------------------+| Ft_query_expansion_limit | 20 | | Have_query_cache | YES | | log_queries_not_using_indexes | OFF | | log_slow_queries | On | | Long_query_time | 10.000000 | | Query_alloc_block_size | 8192 | | Query_cache_limit | 1048576 | | Query_cache_min_res_unit | 4096 | | Query_cache_size | 8388608 | | Query_cache_type | On | | Query_cache_wlock_invalIdate | OFF | | Query_prealloc_size | 8192 | | Slow_query_log | On | | Slow_query_log_file | /usr/local/mysql/var/cherry-slow.log |+-------------------------------+--------------------------------------+ Rows in Set (0.00 sec) 1) The value of Slow_query_log is on to turn on the slow query log, and off is to turn off the slow query log. 2) The value of Slow_query_log_file is a slow query log to the file (note: The default name is host name. Log, the slow query log is written to the specified file, you need to specify the output log format for the slow query as a file, the related command is: Show variables like '% Log_output% '; To see the format of the output). 3) long_query_time Specifies the threshold for a slow query, which is a slow query statement if the time to execute the statement exceeds the threshold, and the default value is 10. 4) Log_queries_not_using_indexes If the value is set to ON, all queries that do not utilize the index are logged (note: If you just set log_queries_not_using_indexes to ON, Slow_ Query_log is set to OFF, this setting also does not take effect, that is, the setting takes effect if the value of Slow_query_log is set to ON), which is normally turned on temporarily when performance is tuned.

Show variables like '%log_output% '; [How to log Records]

mysql> show variables like ‘%log_output%‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output    | FILE  |+---------------+-------+1 row in set (0.00 sec)默认记录方式是文本 可以修改成记录到数据表方式

Set global log_output= ' file,table '; [How to set up logging]

mysql> show variables like ‘%log_output%‘;+---------------+------------+| Variable_name | Value      |+---------------+------------+| log_output    | FILE,TABLE |+---------------+------------+1 row in set (0.00 sec)

Show global status like '%slow% '; [Number of statements that query the current slow query]

mysql> show global status like ‘%slow%‘;+---------------------+-------+| Variable_name       | Value |+---------------------+-------+| Slow_launch_threads | 0     || Slow_queries        | 4     |+---------------------+-------+2 rows in set (0.00 sec)

mysql-Slow Query log

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.