MySQL Query Top SQL Statements are not displaying in Statement Digests (4226898)

MySQL agents are not displaying query statements in the Statement Digests dashboard. Affected agents may be missing data or show an empty results set despite expected activity on the monitored MySQL instances.

Cause 1: MySQL version < 5.6.5 – Requires slow query log configuration, as performance_schema is not available.

Cause 2: Performance Schema is not enabled on the MySQL instance or the corresponding collection is not enabled in the MySQL Foglight agent.

Cause 3: Topology limits reached – Excessive data from monitored instances can exceed Foglight topology object limits.

Cause 4: Digest table too large – The events_statements_summary_by_digest table in performance_schema contains excessive rows.

Cause 5: Insufficient user permissions to collect required data from the MySQL instance.

Cause 6: Advanced Filter is enabled – By design, the dashboard filters out queries from internal databases such as performance_schema, information_schema, mysql, and sys.

Resolution 1 – Configure Slow Query Log (MySQL < 5.6.5)

Enable the slow query log on affected MySQL servers.

See: KB 233602 - How to configure the slow query log for the MySQL agent

Resolution 2 – Enable Performance Schema

Run the following command on the MySQL server:

SHOW VARIABLES LIKE 'performance_schema';

If disabled, enable it in the MySQL server configuration and restart the service.

Resolution 3 – Increase Topology Object Limits

Increase the limit for MySQLAgent_Statement_Digest topology objects in Foglight.

See: KB 76022 - How to increase Foglight topology instance limits

Note: This may increase Foglight Management Server memory usage.

Resolution 4 – Reduce Digest Table Size or Disable Collection

• Adjust performance_schema_digests_size in MySQL to reduce rows in the digest table.
• Disable statement digest collection for instances where it is not required.

Resolution 5 – Verify Required Permissions

Ensure the MySQL monitoring user has the necessary permissions.

• KB 259690 - MySQL agent user permissions
• KB 233651 - Required MySQL permissions for monitoring

Resolution 6 – Disable Advanced Filter

On the Statement Digests dashboard, click the red X next to Advanced Filter On at the bottom-right to remove filters and show all data.

Post-Change Steps

1. Clear the digest table to refresh data:

   TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

2. Clear topology data in Foglight (Script Console):

   def objects = new HashSet(#!MySQLAgent_Statement_Digest#.getTopologyObjects());
   server.TopologyService.deleteObjects(objects)