MySQL Query and PHP script Customization: how can I find problems?

Question

I inherited a site on which several different software packages work, which several times worked on several people, with many modifications and additions.

How can I systematically, universally, and I hope to automatically record / control / find scripts that use slow queries, persistent connections, unindexed connections, etc.?

Answer 1

There are many tools to serve these needs:

• Microsoft Enterprise Monitor Query Analyzer ($$$ cost)
  • This will allow you to register, track and find slow queries, unindexed connections and persistent connections, as well as give tips on how to improve MySQL performance.
• mysqltuner (opensource perl script)
  • This will allow you to debug the MySQL configuration and give recommendations on how to configure it to improve performance.
• mtop (like for MySQL, for free)
  • This will track slow requests.
• XDebug and Web Grind (PHP performance, free)
  • This will keep a log, monitor and find slow scripts.
• New relic (good for finding slow queries and the reason why they are slow, free or pay)
  • This will log, monitor and find slow scripts and requests
• MySQL Monitor Tool (Java, free)
  • This will log, track and find slow requests and persistent connections.

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

I personally recommend in order:

• New relic (free or paid plan, if it is within the budget)
• mysqltuner (only needed once or twice)
• MySQL Enterprise Monitor (too expensive for most cases, but very good)

Answer 2

You can enable the slow query log . mysqltuner also a good starting point for setting the most important parameters.