MySQL Query和PHP Script Tuning：我如何找到问题？

I have inherited a site running several different software packages worked on by several different people over several years, with lots of modifications and addons throughout.

我继承了一个运行几个不同软件包的网站,这些软件包由几个不同的人在几年内工作,并且在整个过程中进行了大量的修改和插件。

How can I systematically, pervasively, and I hope automatically log/monitor/find scripts that use slow queries, persistent connections, unindexed joins, etc.?

我怎样才能系统地,普遍地,我希望自动记录/监视/查找使用慢查询,持久连接,无索引连接等的脚本?

2 个解决方案

#1

There is a variety of tools to service these needs:

有多种工具可满足这些需求:

MySQL Enterprise Monitor Query Analyzer (Costs $$$)
- This will log, monitor, and find slow queries, un-indexed joins, and persistent connections, as well as giving advice on how to improve MySQL performance

MySQL企业监视器查询分析器(成本$$$)这将记录,监视和查找慢速查询,非索引连接和持久连接,并提供有关如何提高MySQL性能的建议

mysqltuner (opensource perl script)
- This will debug you MySQL configuration and give suggestions on how to adjust it to improve performance

mysqltuner(opensource perl脚本)这将调试MySQL配置并提供有关如何调整它以提高性能的建议

mtop (like top, but for MySQL, free)
- This will monitor slow queries

mtop(如顶部,但对于MySQL,免费)这将监视慢速查询

XDebug and Web Grind (PHP performance, free)
- This will log, monitor and find slow scripts

XDebug和Web Grind(PHP性能,免费)这将记录,监控和查找慢速脚本

New Relic (good for finding slow requests, and the reason why they where slow, free or paid)
- This will log, monitor, and find slow scripts and queries

New Relic(适用于查找慢速请求,以及它们缓慢,免费或付费的原因)这将记录,监控和查找慢速脚本和查询

MySQL Monitor Tool (Java, free)
- This will log, monitor, and find slow queries, and persistent connections

MySQL Monitor Tool(Java,免费)这将记录,监视和查找慢速查询和持久连接

Also on a site note, another awesome tool is Toad for MySQL (free) which provides full database management (including tables, views, triggers, functions, procedures, etc), diagramming, and schema/data comparison functions.

另外在网站上,另一个很棒的工具是Toad for MySQL(免费),它提供完整的数据库管理(包括表格,视图,触发器,函数,程序等),图表和模式/数据比较功能。

I personally recommend, in order:

我个人建议,按顺序:

New Relic (free, or paid plan if its within budget)

New Relic(如果在预算范围内,则为免费或付费计划)

mysqltuner (only needs to be used once or twice)

mysqltuner(只需要使用一次或两次)

MySQL Enterprise Monitor (too expensive for most cases, but very good)

MySQL Enterprise Monitor(对于大多数情况来说太贵了,但非常好)

#2

-1

You can enable slow query log. mysqltuner is also good as a starting point to tune most important params.

您可以启用慢查询日志。 mysqltuner也是调整最重要的参数的起点。

#1