最好的MySQL性能调优工具? [英] Best MySQL performance tuning tool?

问题描述

哪个是MySQL最好的，用户友好的性能工具?我需要帮助来查明设置的瓶颈.是SQL语句，设置变量还是其他问题?

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?

推荐答案

坏消息:有GUI工具可以帮助解决此问题，但这是一项熟练而广泛的工作.因此，它们不能涵盖所有内容，可能您需要使用命令行的东西/sql语句等来提供帮助.我只真正使用过命令行工具.我将概述一些我知道/曾经使用过的东西:

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:

首先，您需要一个良好的数据库设计.如果设计不好，那么您只能走得太远.这包括规范化，以及对字段使用适当的类型.我将这一点留在这里，因为我认为这还有一点，而不是你所追求的.

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.

确保MySQL Query Cache已设置并正常工作，并且如果可以的话，给它更多的RAM，并确保您的重要查询没有采取任何措施阻止mysql对其进行缓存.例如，在查询中使用NOW()函数可以做到这一点-显而易见的原因-NOW每秒都会变化！您可以改为在sql中添加时间戳，并使用最接近的分钟/小时/天(您可以避免的最大时间)来使mysql获得一些缓存好处.

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.

开始优化事情:在select前面加上"EXPLAIN"是查看查询如何执行并确定如何改进查询的方式.学习解释输出: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html 您通常可以在现有索引中添加新索引/添加列以改善性能.但是，您还会遇到需要重组查询的时间.

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.html You'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.

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

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.

概述，包括配置(如果尚未记录的话)位于此处: http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html -我还发现将一天的long_query_time设置为0左右，以便将所有查询记录在此处并留出时间，这是一种了解性能确切运行方向的有用方法.但是我不会立即去那里！而且不要将其保留，日志可能会变得很大.

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.

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

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

它不仅可以进行缓慢的查询日志分析，还可以做更多的工作-阅读手册.但是要解释一下它对慢速日志的作用:慢速查询日志可能包含大量数据，因此很难找出哪些查询是整体上最昂贵的-例如:将它们运行多少次以及何时进行两次查询的因素考虑在内实际上，它们在where子句中具有相同的ID.

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.

MySQL sla为您完成了这一切.它遍历日志，并且可以对where子句中相同/具有不同值的查询进行分组.然后(默认情况下)向您展示总执行时间最多的10个查询-这通常会让人感到有些意外，但通常是最有效率的起点-选择最昂贵的查询并对其使用EXPLAIN，看看是否可以改进它.

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.

某些查询需要很长时间，因此无法轻松进行改进.在这种情况下，您可以通过其他方式获取数据还是至少将其缓存?您甚至可能会发现需要更改数据库架构.同样，某些查询可能位于mysqlsla输出的顶部，因为它们运行很多(特别是如果long_query_time设置为0，则为true)，即使它们运行得非常快.也许是时候为您的应用添加一些缓存了?

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?

http://www.maatkit.org/看起来也很有希望-从未使用过，但是mk -query-profiler工具应有助于进一步调查查询变慢的原因.

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.

还要完全独立地看一看:PHPMYADMIN中的状态"页面(或者您可以运行所有查询以生成此信息....)-用红色突出显示它认为可能不好的东西，并且可以帮助您了解从分配系统资源中可以从中受益的地方.我对此并不太了解-我的方法一直是，如果某物是红色且看起来很糟糕，请去阅读它，并确定它是否重要以及我是否应该做某事(通常意味着向MySQL分配更多的资源)通过更改配置).

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).

最近，我发现在受苦的服务器上运行SHOW PROCESSLIST也很有用.虽然它只提供实时(实时快照)信息，但它可以帮助您了解给定时间的状况，尤其是刷新几次并观察更改时.我最近发现了一个服务器，它使用每个可用的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.

这篇关于最好的MySQL性能调优工具?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！