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/binlogis 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.