Certain workloads require some maintenance on tables in
performance_schema. Query statistics are aggregated in the
performance_schema.events_statements_summary_by_digest table, which has a limit on the number of rows. This limit is specified by the
performance_schema_digests_size system variable. If the table is full, new query digests are tracked in a catch-all row with null schema and null query digest, preventing the Agent from distinguishing between queries that make up that row.
To prevent this loss of accurate per-query metrics, periodically truncate this table as a maintenance step so that all new queries can be collected:
To determine the frequency of truncation, run the query below to determine the number of statements sent to this catch-all row per second. A value greater than zero means the table is full and should be truncated.
SHOW STATUS LIKE 'Performance_schema_digest_lost';
Partitioning your database across tables, such that table definitions are identical except for the name, can result in a large number or normalized queries:
SELECT * FROM daily_aggregates_001 SELECT * FROM daily_aggregates_002 SELECT * FROM daily_aggregates_003
In these cases, track these queries as a single normalized query using the
quantize_sql_tables option, so all metrics for those queries are rolled up into a single query:
SELECT * FROM daily_aggregates_?
quantize_sql_tables option to your database instance configuration in the Datadog Agent:
init_config: instances: - dbm: true ... quantize_sql_tables: true