MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

27.9 Performance Schema Tables for Current and Historical Events

For wait, stage, statement, and transaction events, the Performance Schema can monitor and store current events. In addition, when events end, the Performance Schema can store them in history tables. For each event type, the Performance Schema uses three tables for storing current and historical events. The tables have names of the following forms, where xxx indicates the event type (waits, stages, statements, transactions):

The _current table for each event type contains one row per thread, so there is no system variable for configuring its maximum size. The Performance Schema autosizes the history tables, or the sizes can be configured explicitly at server startup using table-specific system variables, as indicated in the sections that describe the individual history tables. Typical autosized values are 10 rows per thread for _history tables, and 10,000 rows total for _history_long tables.

For each event type, the _current, _history, and _history_long tables have the same columns. The _current and _history tables have the same indexing. The _history_long table has no indexing.

The _current tables show what is currently happening within the server. When a current event ends, it is removed from its _current table.

The _history and _history_long tables show what has happened in the recent past. When the history tables become full, old events are discarded as new events are added. Rows expire from the _history and _history_long tables in different ways because the tables serve different purposes:

The difference between the two types of history tables relates to the data retention policy. Both tables contains the same data when an event is first seen. However, data within each table expires differently over time, so that data might be preserved for a longer or shorter time in each table:

When a thread ends, all its rows are discarded from the _history table but not from the _history_long table.

The following example illustrates the differences in how events are added to and discarded from the two types of history tables. The principles apply equally to all event types. The example is based on these assumptions:

After 5 seconds of execution:

After 5 minutes (300 seconds) of execution: