最好的 MySQL 性能调优工具？

Best MySQL performance tuning tool?

提问by Arne Evertsson

Which is the best, user-friendliest performance tool for MySQL? I'd like help with pinpointing the bottle neck of my setup. Is the problem in the SQL statements, the settings variables, or something else?

哪个是最好的、对用户最友好的 MySQL 性能工具？我需要帮助确定我的设置的瓶颈。是 SQL 语句、设置变量还是其他方面的问题？

回答by benlumley

The bad news: there are GUI tools to help with this, but its a skilled and wide ranging job. So they don't cover everything, its likely you will need to use command line stuff/sql statements etc to help. I've only really used the command line tools. I'll give a bit of an overview of things I know/have used:

坏消息：有 GUI 工具可以帮助解决这个问题，但它是一项技能娴熟且范围广泛的工作。因此，它们并未涵盖所有内容，您可能需要使用命令行内容/sql 语句等来提供帮助。我只真正使用过命令行工具。我将概述一下我知道/使用过的东西：

First, you need a good database design. If the design is bad, you can only get so far. This includes normalisation, as well as using appropriate types for fields. I'll leave this point here, as I think its a bit of an aside, and not what you are after.

首先，你需要一个好的数据库设计。如果设计不好，你只能走到这一步。这包括规范化以及对字段使用适当的类型。我将把这一点留在这里，因为我认为它有点放在一边，而不是你所追求的。

Make sure the MySQL Query Cache is set up and working and give it a bit more RAM if you can, and make sure that your important queries aren't doing anything that prevents mysql caching them. For example, using the NOW() function in queries does this - for obvious reasons - NOW changes every second! You can instead put a timestamp into the sql, and use the time to the nearest minute/hour/day (the largest period you can get away with) to allow mysql to get some caching benefit.

确保 MySQL 查询缓存已设置并正常工作，如果可以，请为其提供更多 RAM，并确保您的重要查询没有执行任何阻止 mysql 缓存它们的操作。例如，在查询中使用 NOW() 函数可以做到这一点 - 出于显而易见的原因 - NOW 每秒都在变化！您可以改为在 sql 中添加时间戳，并使用最接近的分钟/小时/天（您可以摆脱的最大时间段）的时间来让 mysql 获得一些缓存优势。

To begin optimising things: Sticking "EXPLAIN" in front of select is THE way to see how a query is being executed and idetify how to improve it. Learn to interpret the output: http://dev.mysql.com/doc/refman/5.0/en/using-explain.htmlYou'll often be able to add new indexes/add columns to existing ones to improve things. But you will also encounter times that queries need to be restructured.

开始优化：在 select 前面加上“EXPLAIN”是查看查询如何执行并确定如何改进它的方法。学习解释输出：http: //dev.mysql.com/doc/refman/5.0/en/using-explain.html您通常可以添加新索引/向现有索引添加列以改进内容。但是您也会遇到需要重构查询的情况。

Starting out improving performance with MySQL (assuming you don't already know what the problem query is) is to check the slow query log - it logs to a file all queries taking longer than x seconds.

开始使用 MySQL 提高性能（假设您还不知道问题查询是什么）是检查慢查询日志 - 它将所有查询时间超过 x 秒记录到文件中。

Overview, including config for if its not logging this already, is here: http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html- I've also found that setting long_query_time to 0 for a day or so, so that all queries are logged here with time taken, is a useful way to get an idea of exactly where the performance is going. But I wouldn't go there immediately! And don't leave it on, the logs can get massive.

概述，包括如果它没有记录它的配置，在这里：http: //dev.mysql.com/doc/refman/5.0/en/slow-query-log.html- 我还发现将 long_query_time 设置为0 一天左右，以便所有查询都随着时间记录在此处，这是了解性能进展情况的有用方法。但我不会马上去那里！不要让它一直开着，日志会变得很大。

Once you've got a few days of logging, I've found mysqlsla (mysql slow log analyser) from here: http://hackmysql.com/mysqlslais a good tool.

一旦你有几天的日志记录，我就从这里找到了 mysqlsla（mysql 慢日志分析器）：http://hackmysql.com/mysqlsla是一个很好的工具。

It can do more than just slow query log analysis - read the manual. But to explain what it does for slow logs: the slow query log can contain a lot of data, so it can be hard to figure out which queries are the most expensive overall - eg: factor in how many times they run and when two queries are actually the same with a different id in a where clause.

它可以做的不仅仅是慢速查询日志分析 - 阅读手册。但是要解释它对慢日志的作用：慢查询日志可以包含大量数据，因此很难确定哪些查询总体上是最昂贵的 - 例如：考虑它们运行的​​次数和两次查询的时间实际上是相同的，在 where 子句中具有不同的 id。

MySQL sla does this all for you. It runs through the log, and can group queries that are the same/have different values in the where clauses. It then presents you (by default) the top 10 queries in terms of total execution time - which often has some surprises, but is usually the most productive starting point - take the most expensive query and use EXPLAIN on it and see if you can improve it.

MySQL sla 为您完成这一切。它贯穿日志，并且可以对 where 子句中相同/具有不同值的查询进行分组。然后它会向您展示（默认情况下）总执行时间前 10 条查询 - 这通常会有一些惊喜，但通常是最高效的起点 - 选择最昂贵的查询并对其使用 EXPLAIN，看看您是否可以改进它。

Some queries take a long time, and can't easily be improved. In this case, can you get the data another way or at least cache it instead? You may even find that changing the DB schema is required. Similarly, some queries may be at the top of the mysqlsla output because you run them a lot (especially true if long_query_time is set to 0), even if they run pretty quick. Maybe time to add some caching to your app?

有些查询需要很长时间，而且不容易改进。在这种情况下，您能否以另一种方式获取数据或至少将其缓存起来？您甚至可能会发现需要更改数据库架构。同样，某些查询可能位于 mysqlsla 输出的顶部，因为您经常运行它们（尤其是如果 long_query_time 设置为 0），即使它们运行得非常快。也许是时候为您的应用程序添加一些缓存了？

http://www.maatkit.org/also looks promising - never used it, but the mk-query-profiler tool should be useful to further look into why queries slow.

http://www.maatkit.org/看起来也很有前途 - 从未使用过它，但 mk-query-profiler 工具应该有助于进一步研究查询缓慢的原因。

A completely separate thing to look at as well: the "status" page in PHPMYADMIN (or you can run all the queries to generate this info ....) - it highlights things it thinks might be bad in red, and can help you see where you might get benefit from allocating system resources. I don't know that much on this - my approach has always been that if something is red and looks bad, to go and read up about it and decide if its important and whether I should do something (usually means allocating more resources to MySQL by changing config).

一个完全独立的东西也要看：PHPMYADMIN中的“状态”页面（或者你可以运行所有查询来生成这个信息......） - 它用红色突出显示它认为可能不好的东西，并且可以帮助你查看您可以从分配系统资源中获得哪些好处。我对此知之甚少 - 我的方法一直是，如果某个东西是红色的并且看起来很糟糕，那就去阅读它并决定它是否重要以及我是否应该做某事（通常意味着将更多资源分配给 MySQL通过更改配置）。

Recently I've found that running SHOW PROCESSLIST can also be useful on a server that is suffering. Whilst it only gives you live (well, a live snapshot) info, it can help you get a feel for what is going on at a given time, especially if you refresh a few times and observe the changes. I recently spotted a server using every available mysql connection to run an identical query using this method. Sure, it'd have been in the slow query log, but this as a really quick and obvious way to see what was up.

最近，我发现运行 SHOW PROCESSLIST 在遇到问题的服务器上也很有用。虽然它只为您提供实时（嗯，实时快照）信息，但它可以帮助您了解在给定时间发生的情况，尤其是在您刷新几次并观察更改时。我最近发现一个服务器使用每个可用的 mysql 连接来使用这种方法运行相同的查询。当然，它会出现在慢查询日志中，但这是一种非常快速且明显的查看发生了什么的方式。

回答by MarkR

EXPLAIN is your friend. Other than that, you must rely on a mixture of ad-hoc tools, testing, and the OS tools to see exactly what's going on.

解释是你的朋友。除此之外，您必须结合使用临时工具、测试和操作系统工具来准确了解发生了什么。

A lot of things can be seen effectively either from the OS tools (good for determing whether a situation is CPU or IO bound) or the various variables which can be seen in MySQL itself (show global variables, show engine innodb status etc).

从操作系统工具（有助于确定情况是 CPU 还是 IO 绑定）或 MySQL 本身可以看到的各种变量（显示全局变量、显示引擎 innodb 状态等），可以有效地看到很多东西。

I feel that it's really important to have a PERFORMANCE TEST ENVIRONMENT. Get a production-sized database with production-like load (using load simulation) running on production-grade hardware in your lab (expensive but essential).

我觉得拥有一个性能测试环境真的很重要。获取在您实验室的生产级硬件上运行的具有类似生产负载（使用负载模拟）的生产规模数据库（昂贵但必不可少）。

Once you've done that you can tweak any parameters you want in complete safety, knowing that even if it melts the whole server you can just rebuild it without affecting anything.

完成后，您可以完全安全地调整您想要的任何参数，知道即使它融化了整个服务器，您也可以重建它而不会影响任何东西。

Don't do specific things that people suggest unless you've tested them in your performance test environment and found them to be provably beneficial.

不要做人们建议的具体事情，除非您已经在性能测试环境中对其进行了测试并发现它们被证明是有益的。

Essentially, performance tuning usually looks at three areas, in this order:

本质上，性能调优通常按以下顺序关注三个方面：

• Structure of your database itself (table structure, indexes etc)
• Queries
• Tuning the server parameters

• 数据库本身的结构（表结构、索引等）
• 查询
• 调整服务器参数

The first two are often overlooked in favour of the last one, which can help but only after the first two are done.

前两个经常被忽视而倾向于最后一个，这有帮助，但只有在前两个完成之后。

Don't underestimate the importance of reducing the amount of work the server needs to do - using smaller fields, fewer rows, fewer columns etc, is always better.

不要低估减少服务器需要做的工作量的重要性——使用更小的字段、更少的行、更少的列等总是更好。

回答by Hendrik

I found MONyogto be a BIG help when I had a suffering mysql server.

我发现MONyog对我的 mysql 服务器有很大帮助。

It's a webbased system that analyses your system 24/7 and gives you decent suggestions for better values of system variables, cache sizes, etc...

这是一个基于 Web 的系统，可以 24/7 全天候分析您的系统，并为您提供更好的系统变量、缓存大小等值的体面建议...

It also has a built in mysqlsla type slow query analyzer feature.

它还具有内置的 mysqlsla 类型慢查询分析器功能。

Too bad it's not free.

可惜不是免费的。

MONyog- MySQL Monitor and Advisorhas new UI which I love, I like these features mostly:

MONyog- MySQL Monitor and Advisor有我喜欢的新 UI，我最喜欢这些功能：

1. Find problem SQL - using slow log, general log, PROCESSLIST and through PROXY
2. Alert on critical conditions like - Server is down, too many connections etc..
3. Monitor your own query result periodically

4. Replication monitoring

   etc.... Complete feature list

1. 查找问题 SQL - 使用慢日志、一般日志、PROCESSLIST 和通过 PROXY
2. 对关键情况发出警报，例如 - 服务器已关闭、连接过多等。
3. 定期监控自己的查询结果

4. 复制监控

   等等....完整的功能列表