MySQL Query and PHP Script Tuning: How can I find the problems?

Question

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

Answer 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
• mysqltuner (opensource perl script)
  • This will debug you MySQL configuration and give suggestions on how to adjust it to improve performance
• mtop (like top, but for MySQL, free)
  • This will monitor slow queries
• XDebug and Web Grind (PHP performance, free)
  • This will log, monitor and find slow scripts
• 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
• MySQL Monitor Tool (Java, free)
  • This will log, monitor, and find slow queries, and persistent connections

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.

I personally recommend, in order:

• New Relic (free, or paid plan if its within budget)
• mysqltuner (only needs to be used once or twice)
• MySQL Enterprise Monitor (too expensive for most cases, but very good)

Answer 2

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