Real time query monitoring on MySQL - with 3rd party tool and without

No comments
I've tried out Idera's MySQL Query Explorer, a free tool and I found it easy to use and simple to setup. The only improvement that can be suggested is to add, on the technical requirements page, that your MySQL instance (MySQL Server version 5.5 and newer) must be running with the performance_schema turned on. Otherwise the tool will just display an empty grid. After you've successfully set things up it will display live queries being executed on all databases (or if you prefer, the selected database from a dropdown list). It'll refresh every 30 seconds or manually by pushing a refresh button:

For each captured query you can select it from the grid and choose from three options:
  • Read the full SQL statement;
  • Analyze the optimizer's execution plan;
  • Check out the statistics on each of the execution steps.
This is a nice free tool, most welcome for developers and system administrators. However, database administrators should be able to retrieve the same set of information using MySQL's built-in mechanisms. For most MySQL users, the "Explain plan" option is well known. Another fair share of users knows about profiling a given query. What might not be so evident is:
  • How to capture live queries on the database?
  • The profiling statements are deprecated as of MySQL 5.6.7, what to use instead?
To capture live queries on the MySQL instance we'll resort to the information schema. With locking and blocking information and limiting to the top 10 longest running statements, we can use a similar query to MySQL Query Explorer:
After getting our longest running queries, we'll get the execution statistics from the performance schema. I won't get into details as there is already a great post about it on Percona's blog:
Profiling MySQL queries from Performance Schema

Happy tuning!



No comments :

Post a Comment