DPM is not tracking all MySQL queries when using performance_schema

5/28/2020 7:46 PM

5/28/2020 8:12 PM

You may know that your MySQL database is doing work that does not appear in DPM. For example, queries you know are executing may not be visible in the Profiler, Explorer, or on the Queries page. This article provides information on changes required to capture these queries if you are monitoring your database using the MySQL performance_schema.

Database Performance Monitor

When monitoring a MySQL database using the performance_schema DPM collects query data using the events_statements_summary_by_digest and (optionally) the prepared_statements_instances tables.

These tables will not capture a query's execution (and therefore the query will not be visible in DPM) in the following situations:

• The size of events_statements_summary_by_digest is limited by the MySQL system variable performance_schema_digests_size and the table is currently out of space
• The queries are prepared statements and DPM has not been configured to track prepared statements, the prepared statements are too short-lived to be captured, or the server does not support prepared_statements_instances
• The queries are replicated queries and the server is not using statement-based replication

First, check that the performance_schema is not losing queries due to the size of events_statements_summary_by_digest. You can do this from within DPM by viewing the MySQL Performance Schema Lost chart and looking for the presence of the performance_schema_digest_lost metric; this metric is the number of digests, per second, that MySQL did not record due to space limitations. If this number is not zero, you will need to truncate the table. You can also set up an Alert to notify you if this metric is not zero in the future. See the MySQL documentation for information on truncation and its effects. (© 2020 Oracle Corporation, available at https://dev.mysql.com/, obtained on May 28, 2020)

Next, if your queries are prepared statements and you are running MySQL version 5.7 or newer, you can configure DPM to read data from performance_schema.prepared_statements_instances. To do this, add the option "track-offhost-prep-stmt":"true" to your DPM global.conf configuration file and restart the agent. More information regarding configuration files is available in our documentation. Note that if you are running an earlier version of MySQL, or if your prepared statements are short lived and not reused, DPM will be unable to track those executions.

Replication that is not statement based, or queries executed from stored programs or stored procedures, cannot be tracked at this time.

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.