MySQL Using PERFORMANCE_SCHEMA for Analysis Performance Optimization

The `PERFORMANCE_SCHEMA` in MySQL is a powerful tool designed for performance optimization and monitoring. It provides detailed insights into the server runtime and helps in diagnosing performance issues by collecting and displaying various system and query metrics.

Usage

The `PERFORMANCE_SCHEMA` is used to gain visibility into MySQL server performance, which is essential for identifying and resolving bottlenecks. It is particularly useful when diagnosing slow queries or understanding resource utilization.

SELECT * 
FROM performance_schema.table_name;

In this syntax, `performance_schema.table_name` specifies the particular table within the `PERFORMANCE_SCHEMA` from which you want to retrieve data, such as `events_statements_summary_by_digest` for query analysis.

To enable and configure `PERFORMANCE_SCHEMA`, ensure that the server is started with the `performance_schema` system variable set to ON. This can be configured in the MySQL configuration file (my.cnf or my.ini) or at runtime using the following statement:

SET GLOBAL performance_schema = ON;

Examples

1. Viewing Active Threads

SELECT * 
FROM performance_schema.threads;

This example retrieves information about currently active threads, helping identify thread activity and possible bottlenecks.

2. Analyzing Statement Execution

SELECT digest_text, count_star, avg_timer_wait
FROM performance_schema.events_statements_summary_by_digest
ORDER BY count_star DESC 
LIMIT 5;

`events_statements_summary_by_digest` provides aggregated metrics for SQL statements, offering insights into frequently executed queries needing optimization.

3. Monitoring Table I/O Operations

SELECT object_schema, object_name, count_read, count_write
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'your_database'
ORDER BY count_read DESC
LIMIT 5;

`table_io_waits_summary_by_table` helps review I/O operations on tables, showing which tables are read or written to most frequently, suggesting potential indexing or caching improvements.

Tips and Best Practices

• Enable Only Necessary Instruments. To minimize overhead, enable only the instruments needed for your specific analysis.
• Regularly Check for Changes. Performance patterns can change over time, so regularly review `PERFORMANCE_SCHEMA` data.
• Use Summary Tables. Focus on summary tables like `events_statements_summary_by_digest` for a high-level view of performance.
• Optimize Based on Findings. Use insights from `PERFORMANCE_SCHEMA` to guide optimizations, such as indexing or query rewriting.
• Monitor Overhead. Be aware of the potential performance overhead of running `PERFORMANCE_SCHEMA` and adjust your monitoring scope accordingly.

Common Pitfalls

• Over-Enabling Instruments. Enabling too many instruments can lead to performance degradation. Start with essential instruments and gradually expand if needed.
• Ignoring Overhead. Always account for potential overhead when using `PERFORMANCE_SCHEMA`, especially in production environments.