The performance_schema database

In the most relevant parts of MariaDB code, instrumentations can be found that allow detailed performance monitoring. The results of such monitoring are written into a special database called performance_schema. Since the monitoring activity sensibly slows down the server performance, it is possible to disable it in the configuration file, by setting the performance_schema variable to 0.

The performance_schema variable is based on the following concepts:

• Actors: An actor is a thread that is currently monitored. It can be a user connection or a background MariaDB thread.
• Consumers: Consumers are tables that are populated with performance data.
• Instruments: These are used in instrumented MariaDB activities such as knowing the server's internals where the instruments names are intuitive. For example, wait/io/file/sql/binlog is a wait to acquire a lock on the binary log.
• Objects. These are the tables whose activities must be monitored.

To determine what the server must monitor, the performance_schema setup tables can be modified: setup_actors, setup_consumers, setup_instruments, and setup_objects. When a low-level operation takes place and performance_schema is enabled, if the involved actor, consumer, instrument, and object is monitored, new information is written into the performance_schema. A setup_timer table determines the granularity of the timers that are used to monitor various events (microseconds, nanoseconds, and so on).

The performance_schema setup table consists of several tables. However, the names of the most important ones follow a pattern, based on a prefix and suffix. The prefix indicates what type of information the table provides. The most important prefixes are:

• events_statements_: This means that the table refers to SQL statements.
• events_stages_: This means that the table refers to the stages of a SQL statement execution (such as parsing and table opening).
• *_instances_: This means that the table refers to a certain type of lock. For example, mutex_instances_ refer to mutexes.
• events_waits_: This means that the table refers to threads that are waiting for a lock to be released.

The suffix indicates how the information is aggregated, shown as follows:

• _current: This means that only the current server activities are in the table
• _history: This means that some limited historical information is stored
• _history_long: This means that more historical information is present

Other suffixes exist, but are self-explanatory.

For example, the events_waits_current table lists the threads that are currently waiting for an event. The events_statements_history table shows information about the recently executed statements.