Advanced Configuration for MySQL Database Monitoring

Advanced Configuration for MySQL Database Monitoring

Database Monitoring is not supported for this site.

Truncating events_statements_summary_by_digest

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:

TRUNCATE performance_schema.events_statements_summary_by_digest;

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';

Handling many identical tables

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_?

Add the quantize_sql_tables option to your database instance configuration in the Datadog Agent:

init_config:

instances:
  - dbm: true
    ...
    quantize_sql_tables: true