The Query Analyzer feature of SQL DM for MySQL helps you identify problem SQL. SQL DM for MySQL can find problem SQL by one or more of the following methods:
- Taking
SHOW PROCESSLISTsnapshots at regular intervals (using SQL DM for MySQL Sniffer) - Using MySQL Proxy to collect profiling data (using SQL DM for MySQL Sniffer)
- Utilising Performance schema tables (
events_statements_summary_by_digest,events_statements_history_long) - Parsing Slow Query Log and General Query Log (using SQL DM for MySQL Log Parser)
There are several advantages and disadvantages of each approach.
SHOW
PROCESSLIST is available in all MySQL versions and it is the easiest to setup. However, taking a snapshot of SHOW PROCESSLIST does not guarantee that all queries are captured. Many short-lived queries can be missed between two successive snapshots. It is a quick and easy way to find long running queries.
Log parsing requires some additional setup. Also, switching on the General Query Log puts a significant amount of load on the server. You should always keep the Slow Query Log switched on. Parsing the Slow Query Log is an effective way to find bad queries.
Using MySQL Proxy gives you the most accurate information on profiling SQL. However, during profiling you have to configure your clients to connect to MySQL Proxy, which in turn connects to MySQL server. Using MySQL Proxy ensures that all queries are profiled. It helps you to find problematic queries that don not take much time, but are executed thousands of times. Eliminating such queries can significantly improve the performance of your application.
Performance schema based sniffer makes use of the performance_schema database of the MySQL server. SQL DM for MySQL queries the performance schema database and collects snapshots at regular interval. Since each and every query is logged in the Performance schema database, SQL DM for MySQL displays even the short lived queries using this method.
To use the SQL DM for MySQL Query Analyzer functionality for a specific server, the server General query log or Slow query log details must be configured in Connection Settings or a Query Sniffer must be enabled for that server.
Using the above tools to find problem SQL is almost always a post-mortem excercise. In certain situations you may want real-time notifications for long-running queries. SQL DM for MySQL can continuously monitor queries in real-time and send notifications (on mail or SNMP) for queries that take more than a specified amount of time to execute. You can also specify an option to kill such queries instantly.
SHOW
PROCESSLIST is different from the the Processlist feature in that the Sniffer retains the information retrieved in a database for generation of reports and further analysis, whereas the Processlist feature just displays that information as is, without manipulating or storing it.