MySQL's Performance Schema is a built-in database engine that monitors MySQL's internal runtime performance information. The enable method is as follows: 1. The default majority of versions are enabled, and you can check the status by SHOW VARIABLES LIKE 'performance_schema'; 2. If OFF, add performance_schema=ON in the [mysqld] part of my.cnf or my.ini, and restart it will take effect; 3. Note that the old version may need to be compiled manually. Common monitoring tables include: 1. events_statements_summary_by_digest is used to locate the most resource-consuming SQL; 2. table_io_waits_summary_by_table is used to locate the hotspot table; 3. file_summary_by_instance is used to monitor disk IO; 4. memory_summary_global_by_event_name is used to view memory usage. Performance impact: 1. Low overhead, with the general CPU and memory increasing by less than 5%; 2. Enable too many instruments may increase the load; 3. Overhead can be reduced by turning off non-essential acquisition items. How to use the tool: 1. Use sys schema to simplify query; 2. Prometheus Grafana to achieve visual monitoring; 3. Use pt-query-digest to analyze SQL performance problems. With these core content mastered, Performance Schema can become an efficient problem diagnosis tool.

MySQL's Performance Schema is a built-in database engine that monitors performance information in MySQL's internal runtime. It does not only focus on SQL statements like slow query logs, but instead captures detailed indicators such as threads, locks, file IO, memory usage, etc. from a lower level. If you find that the database is slowing down but can’t find the obvious reason, Performance Schema is your “magnifying glass”.

How to enable Performance Schema?

By default, Performance Schema is enabled for most MySQL versions, but you have to check if it is actually on. You can view it through the following command:

 SHOW VARIABLES LIKE 'performance_schema';

If ON is returned, it means it is enabled; if it is OFF , you need to add it to the configuration file (usually my.cnf or my.ini ):

 [mysqld]
performance_schema=ON

Restart MySQL and check again.

Note: Some older versions (such as before MySQL 5.6) may not be turned on by default, or relevant parameters need to be added during manual compilation.

What are the commonly used monitoring items? How should I look at it?

Performance Schema organizes data into multiple tables, which you can understand as "observation points". Here are a few practical and easy to use watches:

• events_statements_summary_by_digest
  This table can help you quickly find the most resource-consuming SQL statements. It summarizes the number of executions, average time, lock waiting time and other information according to the SQL template (digest). Suitable for troubleshooting "what SQL is the most frequent or slowest".

• table_io_waits_summary_by_table
  If you suspect that a table access is too slow, you can see which table has the longest waiting time for reading and writing in this table. Suitable for positioning hot spot table issues.

• file_summary_by_instance
  Monitor the disk IO performance and you can see which data files (ibdata1, ib_logfile, etc.) have the greatest read and write pressure.

• memory_summary_global_by_event_name
  Check the amount of memory used by different modules, such as InnoDB buffer pools, temporary tables, and connection-related memory allocation.

The data of these tables will not be cleaned automatically, so it is recommended to back up or export key fields regularly for analysis.

Does the performance impact have a big impact? Do you want to worry about the overhead?

This is a question that many people are concerned about. While Performance Schema is a lightweight design, it is logging internal events after all, so there is definitely an extra overhead. However, in general, its impact is very small, with the CPU and memory increasing by less than 5% in most scenarios.

Of course, if you turn on a large number of instruments (that is, acquisition items), especially high-frequency acquisition points such as "statement/sql/%" and "wait/io/file/%", it will still have some impact on performance.

One tip: You can reduce overhead by closing unwanted collection items based on actual needs. For example:

 UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'stage/%';

This will disable some unimportant phase events and reduce resource usage.

---

How to use it in combination with tools?

Although it is useful to check the Performance Schema table separately, it is not intuitive enough. You can use the following methods to improve efficiency:

• Use sys schema: It is an official view encapsulation based on Performance Schema, simplifying many complex queries.
• Combined with Prometheus Grafana: Crawl the key metrics of Performance Schema regularly and make a visual panel.
• In conjunction with pt-query-digest: Although it is mainly used to analyze slow query logs, it can also be viewed in combination with events_statements_summary data.

These tools can not only let you see historical trends, but also help set alarm rules to achieve early warning.

---

Basically that's it. Performance Schema is not particularly difficult to understand, but there are many details and we need to look at it in combination with specific issues. As long as you master a few core tables and basic ideas, it is a very practical diagnostic tool.

The above is the detailed content of Using and interpreting MySQL Performance Schema. For more information, please follow other related articles on the PHP Chinese website!