How to Use performance_schema in MySQL for Monitoring

To use performance_schema in MySQL, first ensure it is enabled in your MySQL configuration. Then, query its tables like events_statements_summary_by_digest to analyze server performance and resource usage.

Syntax

The performance_schema is a built-in database in MySQL that collects performance data. You query it like any other database using SELECT statements on its tables.

Key parts include:

• performance_schema: The database name.
• Tables like events_statements_summary_by_digest, threads, events_waits_summary_global_by_event_name that store performance data.
• Standard SQL SELECT queries to retrieve data.

sql

SELECT * FROM performance_schema.events_statements_summary_by_digest LIMIT 5;

Example

This example shows how to check the top 5 most frequent SQL statements by execution count using performance_schema.

sql

SELECT DIGEST_TEXT AS query, COUNT_STAR AS exec_count
FROM performance_schema.events_statements_summary_by_digest
ORDER BY exec_count DESC
LIMIT 5;

Output

query | exec_count
-----------------------------------------
SELECT * FROM users WHERE id = ? | 150
INSERT INTO orders (user_id, total) VALUES (?, ?) | 120
UPDATE products SET stock = stock - 1 WHERE id = ? | 90
SELECT * FROM products WHERE category = ? | 75
DELETE FROM sessions WHERE expires < NOW() | 60

Common Pitfalls

Common mistakes when using performance_schema include:

• Not enabling performance_schema in my.cnf or my.ini. It must be enabled before MySQL starts.
• Expecting real-time data immediately; some data is aggregated and updated periodically.
• Querying without proper permissions; you need SELECT privileges on performance_schema.
• Confusing performance_schema with information_schema; they serve different purposes.

Wrong way:

SELECT * FROM performance_schema.events_statements_summary_by_digest;

If performance_schema is disabled, this returns an error.

Right way: Enable it in the config file:

[mysqld]
performance_schema=ON

Then restart MySQL and run the query.

Quick Reference

Feature	Description
performance_schema	Database collecting performance data
events_statements_summary_by_digest	Summary of SQL statements by type
events_waits_summary_global_by_event_name	Wait events and their counts
threads	Information about server threads
Enable in config	Set performance_schema=ON in my.cnf or my.ini and restart MySQL
Querying	Use SELECT statements on performance_schema tables

Key Takeaways

Enable performance_schema in MySQL configuration before use.

Query performance_schema tables with SELECT to analyze server performance.

Common tables include events_statements_summary_by_digest and threads.

You need proper permissions to access performance_schema data.

Data is aggregated and may not reflect instant real-time stats.