Skip to content
PERCONA MONITORING AND MANAGEMENT DOCUMENTATION

For help, click the link below to get free database assistance or contact our experts for personalized support.

Get help from Percona

Optimize MySQL monitoring performance in PMM

When monitoring MySQL instances with a large number of tables, PMM’s data collection can impact both client and database performance.

Here are a few optimization options to ensure efficient monitoring without overloading your systems.

Options for table statistics optimization

PMM provides two command-line options when adding MySQL instances to control table statistics collection:

  • --disable-tablestats- Completely disables table statistics collection when there are more than 1000 tables (the default limit).
  • --disable-tablestats-limit- Customizes the threshold (number of tables) at which table statistics collection is disabled

When to use these options

Consider using these options in the following when:

  • monitoring MySQL instances with thousands of tables
  • you notice high resource usage on either PMM Client or your MySQL server
  • you observe monitoring delays or timeouts during data collection

Disable table statistics

For MySQL instances with many tables, you can completely disable per-table statistics collection: 

pmm-admin add mysql --disable-tablestats

This command configures PMM to:

  • add your MySQL instance to PMM without collecting table-level statistics
  • still collect all instance-level and database-level metrics
  • significantly reduce the monitoring load when you have more than 1000 tables

Set a custom table limit

For more precise control, you can specify a custom limit for when table statistics should be disabled.

Change the number of tables

When adding an instance with pmm-admin add, the --disable-tablestats-limit option changes the number of tables (from the default of 1000) beyond which per-table statistics collection is disabled:

pmm-admin add mysql --disable-tablestats-limit=<LIMIT>

This command configures PMM to:

  • collect table statistics normally until the instance reaches 2000 tables
  • automatically disable table statistics when the number of tables exceeds 2000
  • continue collecting all other MySQL metrics normally

Best practices for performance optimization

To find the right balance between visibility and performance:

  • If you have more than 1000 tables, begin with --disable-tablestats to start conservative
  • Check CPU, memory, and network usage on the client to monitor PMM Client resource usage
  • Watch for increased load during monitoring intervals to monitor MySQL load
  • If resources permit, you can try enabling table statistics with a higher limit and adjust incrementally

Additional performance considerations:

  • For high-traffic MySQL servers, consider using query sampling with the slow log. For details, see MySQL data source configuration
  • Adjust metrics collection frequency for remote instances. For details, see Remote instances monitoring
  • Ensure PMM Client has adequate CPU and memory resources on busy database servers

Change settings after initial setup

These settings only apply when adding an instance with pmm-admin add. Only one of the table statistics options can be used when adding an instance.

To change them after initial setup:

  1. Remove the existing MySQL service: 
    pmm-admin remove mysql SERVICE_NAME
  2. Add the service again with the desired table statistics settings: 
    pmm-admin add mysql --disable-tablestats-limit=3000 [OTHER_OPTIONS] SERVICE_NAME

Performance impact comparison

Scenario Table stats enabled Table stats disabled
Small MySQL (< 100 tables) Minimal impact Not necessary
Medium MySQL (100–1000 tables) Moderate impact Minimal performance gain
Large MySQL (1000–5000 tables) High impact Significant performance improvement
Very large MySQL (> 5000 tables) Severe impact Strongly recommended