How do you use the Performance Schema in MySQL?

In MySQL, Performance Schema is the primary tool for performance monitoring and diagnostics. It is a powerful built-in utility that helps developers and database administrators understand database operations, optimize performance, and troubleshoot issues. Below are some steps and examples for utilizing this feature:

Step 1: Enable Performance Schema

By default, Performance Schema is enabled in many MySQL versions. If not enabled, you can activate it by modifying the configuration file my.cnf (Linux) or my.ini (Windows).

ini
[mysqld]
performance_schema=ON

Restart the MySQL service to apply the changes.

Step 2: Verify Performance Schema is Enabled

You can confirm whether Performance Schema is successfully enabled using the following SQL command:

sql
SHOW VARIABLES LIKE 'performance_schema';

Step 3: Use Performance Schema Monitoring Tools

Performance Schema includes multiple tables for querying performance-related data. For example:

• View Current Active Threads:

sql
SELECT * FROM performance_schema.threads;

• Monitor SQL Statement Execution:

sql
SELECT * FROM performance_schema.events_statements_history_long;

This table records recently executed SQL statements and their performance metrics, such as execution time and wait time.

Step 4: Analysis and Adjustment

By querying tables within Performance Schema, you can identify slow queries, frequently accessed tables, and other performance bottlenecks. Based on this analysis, you can optimize SQL queries, add indexes, or adjust configurations.

Example: Optimizing Queries

Suppose you discover a particularly slow query using Performance Schema. First, examine its execution details:

sql
SELECT * FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT LIKE '%keyword%';

Based on output information like TIMER_WAIT and NO_INDEX_USED, you can determine whether to add indexes or rewrite the query.

Step 5: Use the sys Schema

The sys schema is built on top of Performance Schema and provides user-friendly views and functionalities for easier performance analysis and issue diagnosis. For example, use its views to find queries consuming the most CPU:

sql
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

By following these steps and leveraging these tools, you can effectively utilize MySQL's Performance Schema to monitor and optimize database performance. This is essential for maintaining an efficient and stable database system.