Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 26.9Mb
PDF (A4) - 27.0Mb
PDF (RPM) - 25.4Mb
HTML Download (TGZ) - 6.3Mb
HTML Download (Zip) - 6.4Mb
HTML Download (RPM) - 5.4Mb
Man Pages (TGZ) - 160.0Kb
Man Pages (Zip) - 263.1Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  MySQL Performance Schema  /  Performance Schema Tables for Current and Historical Events

22.8 Performance Schema Tables for Current and Historical Events

The Performance Schema can monitor and store current wait events. In addition, when events end, the Performance Schema can store them in history tables. The Performance Schema uses three tables for storing current and historical events:

  • events_waits_current: The current events table stores the current monitored event for each thread (one row per thread).

  • events_waits_history: The recent history table stores the most recent events that have ended per thread (up to a maximum number of rows per thread).

  • events_waits_history_long: The long history table stores the most recent events that have ended globally (across all threads, up to a maximum number of rows per table).

The event_waits_current table contains one row per thread, so there is no system variable for configuring its maximum size. The history tables by default store 10 rows per thread for event_waits_history, and 10,000 rows total for event_waits_history_long. These sizes can be configured explicitly at server startup using table-specific system variables, as indicated in the sections that describe the individual history tables.

The three tables have the same columns.

The event_waits_current table shows what is currently happening within the server. When a current event ends, it is removed from the table.

The event_waits_history and event_waits_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 event_waits_history and event_waits_history_long tables in different ways because the tables serve different purposes:

  • event_waits_history is meant to investigate individual threads, independently of the global server load.

  • event_waits_history_long is meant to investigate the server globally, not each thread.

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:

  • For event_waits_history, when the table contains the maximum number of rows for a given thread, the oldest thread row is discarded when a new row for that thread is added.

  • For event_waits_history_long, when the table becomes full, the oldest row is discarded when a new row is added, regardless of which thread generated either row.

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 example is based on these assumptions:

  • The Performance Schema is configured to retain 10 rows per thread in the event_waits_history table and 10,000 rows total in the event_waits_history_long table.

  • Thread A generates 1 event per second.

    Thread B generates 100 events per second.

  • No other threads are running.

After 5 seconds of execution:

  • A and B have generated 5 and 500 events, respectively.

  • event_waits_history contains 5 rows for A and 10 rows for B. Because storage per thread is limited to 10 rows, no rows have been discarded for A, whereas 490 rows have been discarded for B.

  • event_waits_history_long contains 5 rows for A and 500 rows for B. Because the table has a maximum size of 10,000 rows, no rows have been discarded for either thread.

After 5 minutes (300 seconds) of execution:

  • A and B have generated 300 and 30,000 events, respectively.

  • event_waits_history contains 10 rows for A and 10 rows for B. Because storage per thread is limited to 10 rows, 290 rows have been discarded for A, whereas 29,990 rows have been discarded for B. Rows for A include data up to 10 seconds old, whereas rows for B include data up to only .1 seconds old.

  • event_waits_history_long contains 10,000 rows. Because A and B together generate 101 events per second, the table contains data up to approximately 10,000/101 = 99 seconds old, with a mix of rows approximately 100 to 1 from B as opposed to A.