Logging all MySQL queries into the Slow Log

栏目: IT技术 · 发布时间: 4年前

内容简介:MySQL optionally logs slow queries into the Slow Query Log – or just Slow Log, as friends call it. However,There are several reasons to log all queries. This list is not exhaustive:Below you can find the variables to change, as they should be written in th
Logging all MySQL queries into the Slow Log
Is this road’s traffic logged somehow?

MySQL optionally logs slow queries into the Slow Query Log – or just Slow Log, as friends call it. However, there are good reasons to log all the queries , not just some of them. This article shows how to log all available information into the Slow Log.

Table of Contents

1 Why can’t I only log the slowest queries?

2 How to apply the changes

3 Setting the Slow Log

4 Is the Slow Log slow?

Why can’t I only log the slowest queries?

There are several reasons to log all queries. This list is not exhaustive:

  • If you have a locking query that takes 0.5 seconds and runs 10 per second, most probably it’s much more relevant than a query that takes 1 second and is executed once per second.
  • Threshold are not smart. Put a 5 seconds threshold, and you will never know that a very frequent query takes 4.9 seconds.
  • Suppose you only log queries that take 5 or more seconds. If a query takes less than 1 second more of the times and ore than 5 seconds in rare cases (which typically depends on data distribution) you will lose this information.
  • If you don’t log relatively fast queries, you cannot analyse your workload and see, for example, that many locking statements insist on the same table.
  • You want to see and removeuseless queries.
  • For some queries it is perfectly ok to take a long time (one-time queries, cached queries, analytics…). This makes any time threshold not much significant.

How to apply the changes

Below you can find the variables to change, as they should be written in the configuration file (most probably /etc/mysql/my.cnf or /etc/my.cnf ). Note that changes in the configuration file will take effect on MySQL restart .

You avoid restarting MySQL, but I recommend to make the changes to the configuration file anyway, in case it crashes.

To change variables values at runtime:

SET GLOBAL variable_name := 1;
SET GLOBAL variable_name := 'value'; # string values

This only takes effect for new connections . You are not logging all queries until all existing connections are closed and new ones are established.

Setting the Slow Log

Here you can find the correct setting to use to log all your queries, depending on which MySQL flavour you use.

MySQL

8.0

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_extra = 1

5.7

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

5.6

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

Percona Server

8.0

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
slow_query_log_use_global_control = all

# information in the slow log
log_slow_extra = 1
log_slow_verbosity = full

5.7

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
slow_query_log_use_global_control = all

5.6

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
slow_query_log_use_global_control = all

MariaDB

10.5

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

10.4

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

10.3

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

10.2

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

10.1

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

10.0

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

5.5 and previous versions

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1

Is the Slow Log slow?

In most cases, the slow log performance impact is minimum. It may sound scary to think that MySQL will write something into a file for every single statement; but not if you know the amount of IO that is required for every single statement (undo log, redo log, binlog, potentially reads from tablespaces) and InnoDB background threads. And the slow log is just a sequential write-only file.

That said, if your IO is nearly saturated, MariaDB allows to throttle the slow log . Which means, multiple queries will be written to the file together, to reduce the IO operations. Take a look at log_slow_rate_limit .

See also

Related courses

Reference

Conclusions

We discussed why you should log all your queries and all available information about them. We saw exactly how to do it, depending on your MySQL or Percona Server or MariaDB version.

This is something that I check at the beginning of myMySQL Health Checks. This is because I include recommendations so optimise the most impacting queries, so I need to be sure to have complete query statistics.

If you spot any mistake, or if you have more ideas on how to get the most from the Slow Log, please drop a comment below.

Remember: your comments are valuable and welcome !

Toodle pip,
Federico

Photo credit


以上所述就是小编给大家介绍的《Logging all MySQL queries into the Slow Log》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

五子连珠必胜法

五子连珠必胜法

新井华石 / 张书 / 人民体育出版社 / 1997-10 / 12.00元

《五子连珠必胜法》经日本国虹有社授权,译自日本连珠社已故理事长新井华石九段经典著作《连珠必胜法》一书。内容阐述和介绍五子连珠的基本着法和各种常用的布局定式。全书分两大编。连珠基本编介绍连珠棋的发展历史、连珠棋的规则和规定以及基本珠形。连珠必胜编分为六章分别阐述和介绍各种常用布局定式,包括二号连浦月、五号连花月、一号连云月、二号桂名月、三号桂岚月、二号间恒星六种布局定式。一起来看看 《五子连珠必胜法》 这本书的介绍吧!

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

随机密码生成器
随机密码生成器

多种字符组合密码

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具