The events_transactions_summary_global_by_event_name table contains information on transaction events aggregated by event name.

The table contains the following columns:

+----------------------+---------------------+------+-----+---------+-------+
| Field                | Type                | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------+-------+
| EVENT_NAME           | varchar(128)        | NO   |     | NULL    |       |
| COUNT_STAR           | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_WAIT       | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_WAIT       | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_WAIT       | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_WAIT       | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_READ_WRITE     | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_READ_WRITE | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_READ_WRITE | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_READ_WRITE | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_READ_WRITE | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_READ_ONLY      | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_READ_ONLY  | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_READ_ONLY  | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_READ_ONLY  | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_READ_ONLY  | bigint(20) unsigned | NO   |     | NULL    |       |
+----------------------+---------------------+------+-----+---------+-------+

_{This page is licensed: CC BY-SA / Gnu FDL}

Description

The cond_instances table lists all conditions while the server is executing. A condition, or instrumented condition object, is an internal code mechanism used for signalling that a specific event has occurred so that any threads waiting for this condition can continue.

The maximum number of conditions stored in the performance schema is determined by the system variable.

The events_waits_summary_by_user_by_event_name table contains wait events summarized by user and event name. It contains the following columns:

The table lists stage events, summarized by host and event name.

It contains the following columns:

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

The table lists stage events, summarized by thread and event name.

It contains the following columns:

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

The events_stages_history table by default contains the ten most recent completed stage events per thread. This number can be adjusted by setting the performance_schema_events_stages_history_size system variable when the server starts up.

The table structure is identical to the events_stage_current table structure, and contains the following columns:

It is possible to empty this table with a TRUNCATE TABLE statement.

and are related tables.

_{This page is licensed: CC BY-SA / Gnu FDL}

The table lists stage events, summarized by user and event name.

It contains the following columns:

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

The events_transactions_summary_by_user_by_event_name table contains information on transaction events aggregated by user and event name.

The table contains the following columns:

+----------------------+---------------------+------+-----+---------+-------+
| Field                | Type                | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------+-------+
| USER                 | char(32)            | YES  |     | NULL    |       |
| EVENT_NAME           | varchar(128)        | NO   |     | NULL    |       |
| COUNT_STAR           | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_WAIT       | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_WAIT       | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_WAIT       | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_WAIT       | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_READ_WRITE     | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_READ_WRITE | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_READ_WRITE | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_READ_WRITE | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_READ_WRITE | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_READ_ONLY      | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_READ_ONLY  | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_READ_ONLY  | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_READ_ONLY  | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_READ_ONLY  | bigint(20) unsigned | NO   |     | NULL    |       |
+----------------------+---------------------+------+-----+---------+-------+

_{This page is licensed: CC BY-SA / Gnu FDL}

The global_status table contains a list of status variables and their global values. The table only stores status variable statistics for threads which are instrumented, and does not collect statistics for Com_xxx variables.

The table contains the following columns:

resets global status variables, including thread, account, host, and user status, but not those that are never reset by the server.

_{This page is licensed: CC BY-SA / Gnu FDL}

The events_stages_current table contains current stage events, with each row being a record of a thread and its most recent stage event.

The table contains the following columns:

It is possible to empty this table with a TRUNCATE TABLE statement.

The related tables, and derive their values from the current events.

_{This page is licensed: CC BY-SA / Gnu FDL}

The Performance Schema replication_applier_configuration table contains configuration settings affecting replica transactions.

It contains the following fields.

_{This page is licensed: CC BY-SA / Gnu FDL}

It aggregates object wait events, and contains the following columns:

You can the table, which will reset all counters to zero.

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

The session_status table contains a list of status variables for the current session. The table only stores status variable statistics for threads which are instrumented, and does not collect statistics for Com_xxx variables.

The table contains the following columns:

It is not possible to empty this table with a TRUNCATE TABLE statement.

_{This page is licensed: CC BY-SA / Gnu FDL}

Description

The setup_timers table shows the currently selected event timers. Deprecated since 10.5, removed in 12.0.

It contains the following columns:

The TIMER_NAME value can be changed to choose a different timer, and can be any non-NULL value in the column.

If you modify the table, monitoring is immediately affected, and currently monitored events would use a combination of old and new timers, which is probably undesirable. It is best to reset the Performance Schema statistics if you make changes to this table.

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

The status_by_thread table contains status variable information about active foreground threads. The table does not collect statistics for Com_xxx variables.

The table contains the following columns:

If is run, will aggregate the status for all threads to the global status and account status, then reset the thread status. If account statistics are not collected but host and user status are, the session status is added to host and user status.

_{This page is licensed: CC BY-SA / Gnu FDL}

Description

The setup_objects table determines whether objects are monitored by the performance schema or not. By default limited to 100 rows, this can be changed by setting the performance_schema_setup_objects_size system variable when the server starts.

It contains the following columns:

When the Performance Schema looks for matches in the setup_objects, there may be more than one row matching, with different ENABLED and TIMED values. It looks for the most specific matches first, that is, it will first look for the specific database and table name combination, then the specific database, only then falling back to a wildcard for both.

Rows can be added or removed from the table, while for existing rows, only the TIMED and ENABLED columns can be updated. By default, all tables except those in the performance_schema, information_schema and mysql databases are instrumented.

_{This page is licensed: CC BY-SA / Gnu FDL}

The setup_actors table contains information for determining whether monitoring should be enabled for new client connection threads.

The default size is 100 rows, which can be changed by modifying the performance_schema_setup_actors_size system variable at server startup.

If a row in the table matches a new foreground thread's client and host, the matching INSTRUMENTED column in the threads table is set to either YES orNO, which allows selective application of instrumenting by host, by user, or combination thereof.

Initially, any user and host is matched:

_{This page is licensed: CC BY-SA / Gnu FDL}

Description

The mutex_instances table lists all mutexes that the Performance Schema seeing while the server is executing.

A mutex is a code mechanism for ensuring that threads can only access resources one at a time. A second thread attempting to access a resource will find it protected by a mutex, and will wait for it to be unlocked.

The performance_schema_max_mutex_instances system variable specifies the maximum number of instrumented mutex instances.

_{This page is licensed: CC BY-SA / Gnu FDL}

The events_waits_summary_by_host_by_event_name table contains wait events summarized by host and event name. It contains the following columns:

The table lists stage events, summarized by thread and event name.

It contains the following columns:

The events_waits_history_long table by default contains the ten thousand most recent completed wait events. This number can be adjusted by setting the system variable when the server starts up.

The table structure is identical to the table structure, and contains the following columns:

The table lists stage events, summarized by account and event name.

It contains the following columns:

Description

Each account that connects to the server is stored as a row in the accounts table, along with current and total connections.

The table size is determined at startup by the value of the system variable. If this is set to 0, account statistics will be disabled.

The Performance Schema digest is a normalized form of a statement, with the specific data values removed. It allows statistics to be gathered for similar kinds of statements.

For example:

With the data values removed, both of these statements normalize to:

which is the digest text. The digest text is used to compute a digest. For example:

By contrast, the following two statements would not have the same digest as, while the data values are the same, they call upon different tables.

The digest text is limited to 1024 bytes. Queries exceeding this limit are truncated with '...', meaning that long queries that would otherwise have different digests may share the same digest.

Digest information is used in a number of performance scheme tables. These include

Description

The session_account_connect_attrs table shows connection attributes for the current session.

Applications can pass key/value connection attributes to the server when a connection is made. The and session_account_connect_attrs tables provide access to this information, for all sessions and the current session respectively.

The C API functions and are used for passing connection attributes to the server.

The events_stages_history_long table by default contains the ten thousand most recent completed stage events. This number can be adjusted by setting the system variable when the server starts up.

The table structure is identical to the events_stage_current table structure, and contains the following columns:

The replication_applier_status table contains information about the general transaction execution status on the replica.

It contains the following fields.

Description

The performance_timers table lists available event timers.

It contains the following columns:

Description

Each user that connects to the server is stored as a row in the users table, along with current and total connections.

The table size is determined at startup by the value of the system variable. If this is set to 0, user statistics will be disabled.

Lists the types of consumers for which event information is available.

The setup_consumers table contains the following columns:

The table can be modified directly, or the server started with the option enabled, for example:

Description

The hosts table contains a row for each host used by clients to connect to the server, containing current and total connections.

The size is determined by the system variable, which, if set to zero, will disable connection statistics in the hosts table.

It contains the following columns:

The socket_instances table lists active server connections, with each record being a Unix socket file or TCP/IP connection.

The socket_instances table contains the following columns:

The user_variables_by_thread table contains information about and the threads that defined them.

cannot be performed on the table.

The table contains the following columns:

The rwlock_instances table lists all read write lock (rwlock) instances that the Performance Schema sees while the server is executing. A read write is a mechanism for ensuring threads can either share access to common resources, or have exclusive access.

The system variable specifies the maximum number of instrumented rwlock objects.

The rwlock_instances table contains the following columns:

The status_by_account table contains status variable information by user/host account. The table does not collect statistics for Com_xxx variables.

The table contains the following columns:

The replication_applier_status_by_worker table displays replica worker thread specific information.

It contains the following fields.

The status_by_account table contains status variable information by user. The table does not collect statistics for Com_xxx variables.

The table contains the following columns:

The prepared_statements_instances table contains aggregated statistics of prepared statements.

The maximum number of rows in the table is determined by the performance_schema_max_prepared_statement_instances system variable, which is by default autosized on startup.

The table contains the following columns:

_{This page is licensed: CC BY-SA / Gnu FDL}

The Performance Schema is a feature for monitoring server performance.

Introduction

It is implemented as a storage engine, and so will appear in the list of storage engines available.

However, performance_schema is not a regular storage engine for storing data, it's a mechanism for implementing the Performance Schema feature.

The storage engine contains a database called performance_schema, which in turn consists of a number of tables that can be queried with regular SQL statements, returning specific performance information.

See for a full list and links to detailed descriptions of each table. From , there are 80 Performance Schema tables.

Activating the Performance Schema

The performance schema is disabled by default for performance reasons. You can check its current status by looking at the value of the system variable.

The performance schema cannot be activated at runtime - it must be set when the server starts by adding the following line in your my.cnf configuration file.

From , some memory is allocated dynamically, depending on load, number of connections, number of tables open etc.

Enabling the Performance Schema

You need to set up all consumers (starting collection of data) and instrumentations (what to collect):

You can decide what to enable/disable with WHERE NAME like "%what_to_enable"; You can disable instrumentations by setting ENABLED to "NO".

You can also do this in your my.cnf file. The following enables all instrumentation of all stages (computation units) in MariaDB:

Listing Performance Schema Variables

See for a full list of available system variables.

Note that the "consumer" events are not shown on this list, as they are only available as options, not as system variables, and they can only be enabled at .

Column Comments

From MariaDB 10.7.1, comments have been added to table columns in the Performance Schema. These can be viewed with, for example:

See Also

• ``

_{This page is licensed: CC BY-SA / Gnu FDL}

The Performance Schema replication_applier_status_by_coordinator table displays the status of the coordinator thread used in multi-threaded replicas to manage multiple worker threads.

It contains the following fields.

_{This page is licensed: CC BY-SA / Gnu FDL}

The Performance Schema events_waits_summary_global_by_event_name table contains wait events summarized by event name. It contains the following columns:

The *_TIMER_WAIT columns only calculate results for timed events, as non-timed events have a NULL wait time.

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

Description

The file_instances table lists instances of instruments seen by the Performance Schema when executing file I/O instrumentation, and the associated files. Only files that have been opened, and that have not been deleted, will be listed in the table.

The performance_schema_max_file_instances system variable specifies the maximum number of instrumented file objects.

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

The events_transactions_history_long table contains the most recent completed transaction events that have ended globally, across all threads.

The number of records stored in the table is determined by the system variable, which is autosized on startup.

If adding a completed transaction would cause the table to exceed this limit, the oldest row, regardless of thread, is discarded.

The events_waits_summary_by_instance table contains wait events summarized by instance. It contains the following columns:

It aggregates timer and byte count statistics for all socket I/O operations by socket instance.

The table_handles table contains table lock information. It uses the wait/lock/table/sql/handler instrument, which is enabled by default.

Information includes which table handles are open, which sessions are holding the locks, and how they are locked.

The table is read-only, and cannot be performed on the table.

The Performance Schema events_statements_summary_by_account_by_event_name table contains statement events summarized by account and event name. It contains the following columns:

The *_TIMER_WAIT columns only calculate results for timed events, as non-timed events have a NULL wait time.

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

The Performance Schema digest is a hashed, normalized form of a statement with the specific data values removed. It allows statistics to be gathered for similar kinds of statements.

The Performance Schema events_statements_summary_by_digest table records statement events summarized by schema and digest. It contains the following columns:

The *_TIMER_WAIT columns only calculate results for timed events, as non-timed events have a NULL wait time.

The events_statements_summary_by_digest table is limited in size by the system variable. Once the limit has been reached and the table is full, all entries are aggregated in a row with a NULL digest. The COUNT_STAR value of this NULL row indicates how many digests are recorded in the row and therefore gives an indication of whether performance_schema_digests_size should be increased to provide more accurate statistics.

_{This page is licensed: CC BY-SA / Gnu FDL}

The Performance Schema events_statements_summary_global_by_event_name table contains statement events summarized by event name. It contains the following columns:

The *_TIMER_WAIT columns only calculate results for timed events, as non-timed events have a NULL wait time.

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

The events_transactions_history table contains the most recent completed transaction events for each thread.

The number of records stored per thread in the table is determined by the performance_schema_events_transactions_history_size system variable, which is autosized on startup.

If adding a completed transaction event would cause the table to exceed this limit, the oldest thread row is discarded.

All of a thread's rows are discarded when the thread ends.

The table contains the following columns:

_{This page is licensed: CC BY-SA / Gnu FDL}

The events_waits_history table by default contains the ten most recent completed wait events per thread. This number can be adjusted by setting the performance_schema_events_waits_history_size system variable when the server starts up.

The table structure is identical to the events_waits_current table structure, and contains the following columns:

It is possible to empty this table with a TRUNCATE TABLE statement.

and are related tables.

_{This page is licensed: CC BY-SA / Gnu FDL}

The Performance Schema file_summary_by_event_name table contains file events summarized by event name. It contains the following columns:

I/O operations can be avoided by caching, in which case they will not be recorded in this table.

You can the table, which will reset all counters to zero.

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

The metadata_locks table contains metadata lock information.

To enable metadata lock instrumention, at runtime:

UPDATE performance_schema.setup_instruments SET enabled='YES', timed='YES' 
  WHERE name LIKE 'wait/lock/metadata%';

Or in the configuration file:

performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

The table is by default autosized, but the size can be configured with the performance_schema_max_metadata_locks system variable.

The table is read-only, and cannot be used to empty the table.

The table contains the following columns:

• OBJECT_TYPE

  • VARCHAR(64)

  • Null: No

  • Default: NULL

_{This page is licensed: CC BY-SA / Gnu FDL}

The events_transactions_summary_by_account_by_event_name table contains information on transaction events aggregated by account and event name.

The table contains the following columns:

_{This page is licensed: CC BY-SA / Gnu FDL}

The events_waits_current table contains the status of a thread's most recently monitored wait event, listing one event per thread.

The table contains the following columns:

It is possible to empty this table with a TRUNCATE TABLE statement.

The related tables, and derive their values from the current events.

_{This page is licensed: CC BY-SA / Gnu FDL}

The Performance Schema replication_connection_configuration table displays replica's configuration settings used for connecting to the primary.

It contains the following fields.

_{This page is licensed: CC BY-SA / Gnu FDL}

It aggregates timer and byte count statistics for all socket I/O operations by socket instrument.

You can the table, which will reset all counters to zero.

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

There are five memory summary tables in the Performance Schema that share a number of fields in common. These include:

• memory_summary_by_account_by_event_name

• memory_summary_by_host_by_event_name

The memory_summary_by_host_by_event_name table contains memory usage statistics aggregated by host and event.

The table contains the following columns:

_{This page is licensed: CC BY-SA / Gnu FDL}

There are five memory summary tables in the Performance Schema that share a number of fields in common. These include:

• memory_summary_by_account_by_event_name

• memory_summary_global_by_event_name

The memory_summary_global_by_event_name table contains memory usage statistics aggregated by event and event.

The table contains the following columns:

Example

Seeing what memory was most often allocated for:

_{This page is licensed: CC BY-SA / Gnu FDL}

The Performance Schema events_waits_summary_by_account_by_event_name table contains wait events summarized by account and event name. It contains the following columns:

The *_TIMER_WAIT columns only calculate results for timed events, as non-timed events have a NULL wait time.

Example

_{This page is licensed: CC BY-SA / Gnu FDL}

The events_statements_summary_by_host_by_event_name table contains statement events summarized by host and event name. It contains the following columns:

The events_statements_history table by default contains the ten most recent completed statement events per thread. This number can be adjusted by setting the system variable when the server starts up.

The table structure is identical to the table structure, and contains the following columns:

The table contains the following columns:

The events_statements_summary_by_user_by_event_name table contains statement events summarized by user and event name. It contains the following columns:

The host_cache table contains host and IP information from the host_cache, used for avoiding DNS lookups for new client connections.

The host_cache table contains the following columns:

There are five memory summary tables in the Performance Schema that share a number of fields in common. These include:

The events_transactions_current table contains current transaction events for each thread.

The table size cannot be figured, and always stores one row for each thread, showing the current status of the thread's most recent monitored transaction event.

The table contains the following columns:

There are five memory summary tables in the Performance Schema that share a number of fields in common. These include:

Description

session_connect_attrs is a table that shows connection attributes for all sessions. The Performance Schema needs to be enabled for the table to be populated.

Applications can pass key/value connection attributes to the server when a connection is made. The session_connect_attrs and tables provide access to this information, for all sessions and the current session respectively.

The C API functions and

The table_io_waits_summary_by_table table records table I/O waits by table.

Each server thread is represented as a row in the threads table.

The threads table contains the following columns:

The file_summary_by_instance table contains file events summarized by instance. It contains the following columns: