What do you find is the optimal setting for mysql slow query log parameter, and why?
你发现什么是mysql慢速查询日志参数的最佳设置，为什么?
5 个解决方案
#1
18
I recommend these three lines
我推荐这三行
log_slow_queries set-variable = long_query_time=1 log-queries-not-using-indexes
The first and second will log any query over a second. As others have pointed out a one second query is pretty far gone if you are a shooting for a high transaction rate on your website, but I find that it turns up some real WTFs; queries that should be fast, but for whatever combination of data it was run against was not.
第一个和第二个将在一秒钟内记录任何查询。正如其他人指出的那样，如果你在你的网站上以高交易率为目标，你的第二次查询就会消失，但我发现它会出现一些真正的WTFs;查询应该是快速的，但是对于它运行的任何数据组合都不是。
The last will log any query that does not use an index. Unless your doing data warehousing any common query should have the best index you can find so pay attention to its output.
最后一个将记录任何不使用索引的查询。除非你做数据仓库，否则任何常见的查询都应该有你能找到的最好的索引，所以要注意它的输出。
Although its certainly not for production, this last option
尽管它肯定不是用于生产，这是最后一个选择
log = /var/log/mysql/mysql.log
will log all queries, which can be useful if you are trying to tune a specific page or action.
将记录所有查询，如果您试图优化特定的页面或操作，这将非常有用。
#2
4
Whatever time /you/ feel is unacceptably slow for a query on your systems.
对于系统上的查询来说，无论何时/您/感觉如何都慢得令人无法接受。
It depends on the kind of queries you run and the kind of system; a query taking several seconds might not matter if it's some back-end reporting system doing complex data-mining etc where a delay doesn't matter, but might be completely unacceptable on a user-facing system which is expected to return results promptly.
这取决于你运行的查询类型和系统类型;如果某个后端报告系统执行复杂的数据挖掘等操作，而延迟无关紧要，那么花费几秒钟的查询可能并不重要，但对于预期会迅速返回结果的面向用户的系统来说，这可能是完全不可接受的。
#3
3
Set it to whatever you like. The only problem is that in a stock MySQL, it can only be set in increments of 1 second, which is too slow for some people.
把它设置成你喜欢的样子。唯一的问题是，在一个股票MySQL中，它只能设置为1秒，这对一些人来说太慢了。
Most heavily used production servers execute far too many queries to log them all. The slow log is a way of filtering the log so that we can see the ones which take a long time (most queries are likely to be executed almost instantly). It's a bit of a blunt instrument.
大多数严重使用的生产服务器执行了太多的查询来记录它们。慢日志是过滤日志的一种方式，这样我们就可以看到那些需要很长时间的日志(大多数查询很可能会立即执行)。它有点生硬。
Set it to 1 sec if you like, you're probably not going to run out of disc space or create a performance problem by doing that.
如果你愿意的话，把它设置为1秒，你可能不会用完磁盘空间或者这样做会产生性能问题。
It's really about the risk of enabling the slow log- don't do it if you feel it's likely to cause further disc or performance problems.
它实际上是关于启用慢日志的风险——如果您认为它可能会导致进一步的磁盘或性能问题，请不要这样做。
Of course you could enable the slow log on a non-production server and put simulated load through, but that is never quite the same.
当然，您可以在非生产服务器上启用慢日志，并通过模拟负载，但这与以前完全不同。
#4
3
Peter Zaitsev posted a nice article about using the slow query log. One thing he notes is important is to also consider how often a certain query is used. Reports run once a day are not important to be fast. But something that is run very often might be a problem even if it takes half a second. And you cant detect that without the microslow patch.
Peter Zaitsev发表了一篇关于使用慢速查询日志的文章。他指出，重要的一点是还要考虑使用某个查询的频率。报告每天运行一次对于快速来说并不重要。但是经常运行的东西可能是一个问题，即使它需要半秒钟。如果没有微慢贴片，你是无法检测到的。
#5
1
Not only is it a blunt instrument as far as resolution is concerned, but also it is MySQL-instance wide, so that if you have different databases with differing performancy requirements you're kind of out of luck. Obviously there are ways around that, but it's important to keep that in mind when setting your slow log setting.
就分辨率而言，它不仅是一种生硬的工具，而且在mysql实例范围内也是如此，因此，如果您有不同的数据库，并且有不同的性能要求，那么您就有点不走运了。显然有很多方法可以解决这个问题，但是在设置慢速日志设置时要记住这一点。
Aside from performance requirements of your application, another factor to consider is what you're trying to log. Are you using the log to catch queries that would threaten the stability of your db instance (ones that cause deadlocks or Cartesian joins, for instance) or queries that affect the performance for specific users and that might require a little tuning? That will influence where you set your threshold.
除了应用程序的性能需求之外，还需要考虑的另一个因素是要记录什么。您是否正在使用日志来捕获可能会威胁您的db实例稳定性的查询(例如，导致死锁或笛卡尔连接的查询)或影响特定用户性能的查询(这可能需要一点调优)?这将影响您设置阈值的位置。
#1
18
I recommend these three lines
我推荐这三行
log_slow_queries set-variable = long_query_time=1 log-queries-not-using-indexes
The first and second will log any query over a second. As others have pointed out a one second query is pretty far gone if you are a shooting for a high transaction rate on your website, but I find that it turns up some real WTFs; queries that should be fast, but for whatever combination of data it was run against was not.
第一个和第二个将在一秒钟内记录任何查询。正如其他人指出的那样，如果你在你的网站上以高交易率为目标，你的第二次查询就会消失，但我发现它会出现一些真正的WTFs;查询应该是快速的，但是对于它运行的任何数据组合都不是。
The last will log any query that does not use an index. Unless your doing data warehousing any common query should have the best index you can find so pay attention to its output.
最后一个将记录任何不使用索引的查询。除非你做数据仓库，否则任何常见的查询都应该有你能找到的最好的索引，所以要注意它的输出。
Although its certainly not for production, this last option
尽管它肯定不是用于生产，这是最后一个选择
log = /var/log/mysql/mysql.log
will log all queries, which can be useful if you are trying to tune a specific page or action.
将记录所有查询，如果您试图优化特定的页面或操作，这将非常有用。
#2
4
Whatever time /you/ feel is unacceptably slow for a query on your systems.
对于系统上的查询来说，无论何时/您/感觉如何都慢得令人无法接受。
It depends on the kind of queries you run and the kind of system; a query taking several seconds might not matter if it's some back-end reporting system doing complex data-mining etc where a delay doesn't matter, but might be completely unacceptable on a user-facing system which is expected to return results promptly.
这取决于你运行的查询类型和系统类型;如果某个后端报告系统执行复杂的数据挖掘等操作，而延迟无关紧要，那么花费几秒钟的查询可能并不重要，但对于预期会迅速返回结果的面向用户的系统来说，这可能是完全不可接受的。
#3
3
Set it to whatever you like. The only problem is that in a stock MySQL, it can only be set in increments of 1 second, which is too slow for some people.
把它设置成你喜欢的样子。唯一的问题是，在一个股票MySQL中，它只能设置为1秒，这对一些人来说太慢了。
Most heavily used production servers execute far too many queries to log them all. The slow log is a way of filtering the log so that we can see the ones which take a long time (most queries are likely to be executed almost instantly). It's a bit of a blunt instrument.
大多数严重使用的生产服务器执行了太多的查询来记录它们。慢日志是过滤日志的一种方式，这样我们就可以看到那些需要很长时间的日志(大多数查询很可能会立即执行)。它有点生硬。
Set it to 1 sec if you like, you're probably not going to run out of disc space or create a performance problem by doing that.
如果你愿意的话，把它设置为1秒，你可能不会用完磁盘空间或者这样做会产生性能问题。
It's really about the risk of enabling the slow log- don't do it if you feel it's likely to cause further disc or performance problems.
它实际上是关于启用慢日志的风险——如果您认为它可能会导致进一步的磁盘或性能问题，请不要这样做。
Of course you could enable the slow log on a non-production server and put simulated load through, but that is never quite the same.
当然，您可以在非生产服务器上启用慢日志，并通过模拟负载，但这与以前完全不同。
#4
3
Peter Zaitsev posted a nice article about using the slow query log. One thing he notes is important is to also consider how often a certain query is used. Reports run once a day are not important to be fast. But something that is run very often might be a problem even if it takes half a second. And you cant detect that without the microslow patch.
Peter Zaitsev发表了一篇关于使用慢速查询日志的文章。他指出，重要的一点是还要考虑使用某个查询的频率。报告每天运行一次对于快速来说并不重要。但是经常运行的东西可能是一个问题，即使它需要半秒钟。如果没有微慢贴片，你是无法检测到的。
#5
1
Not only is it a blunt instrument as far as resolution is concerned, but also it is MySQL-instance wide, so that if you have different databases with differing performancy requirements you're kind of out of luck. Obviously there are ways around that, but it's important to keep that in mind when setting your slow log setting.
就分辨率而言，它不仅是一种生硬的工具，而且在mysql实例范围内也是如此，因此，如果您有不同的数据库，并且有不同的性能要求，那么您就有点不走运了。显然有很多方法可以解决这个问题，但是在设置慢速日志设置时要记住这一点。
Aside from performance requirements of your application, another factor to consider is what you're trying to log. Are you using the log to catch queries that would threaten the stability of your db instance (ones that cause deadlocks or Cartesian joins, for instance) or queries that affect the performance for specific users and that might require a little tuning? That will influence where you set your threshold.
除了应用程序的性能需求之外，还需要考虑的另一个因素是要记录什么。您是否正在使用日志来捕获可能会威胁您的db实例稳定性的查询(例如，导致死锁或笛卡尔连接的查询)或影响特定用户性能的查询(这可能需要一点调优)?这将影响您设置阈值的位置。