The Performance Schema was introduced in MariaDB 5.5.
The Performance Schema is a feature for monitoring server performance that was introduced in MariaDB 5.5.
| Performance Schema Version | Introduced |
|---|---|
| 5.6.43 | MariaDB 10.0.38 |
| 5.6.42 | MariaDB 10.0.37 |
| 5.6.39 | MariaDB 10.0.34 |
| 5.6.38 | MariaDB 10.0.33 |
| 5.6.37 | MariaDB 10.1.26, MariaDB 10.0.32 |
| 5.6.36 | MariaDB 10.2.7, MariaDB 10.1.24, MariaDB 10.0.31 |
| 5.6.35 | MariaDB 10.1.21, MariaDB 10.0.29 |
| 5.6.33 | MariaDB 10.2.5, MariaDB 10.1.18, MariaDB 10.0.28 |
| 5.6.32 | MariaDB 10.1.17, MariaDB 10.0.27 |
| 5.6.31 | MariaDB 10.1.15, MariaDB 10.0.26 |
| 5.6.30 | MariaDB 10.1.14, MariaDB 10.0.25 |
| 5.6.29 | MariaDB 10.1.12, MariaDB 10.0.24 |
| 5.6.28 | MariaDB 10.0.23 |
| 5.6.27 | MariaDB 10.1.8 , MariaDB 10.0.22 |
| 5.6.26 | MariaDB 10.1.7, MariaDB 10.0.21 |
| 5.6.25 | MariaDB 10.1.6, MariaDB 10.0.20 |
| 5.6.24 | MariaDB 10.0.18 |
| 5.6.20 | MariaDB 10.0.13 |
| 5.6.17 | MariaDB 10.0.11 |
It is implemented as a storage engine, and so will appear in the list of storage engines available.
SHOW ENGINES; +--------------------+---------+----------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------+--------------+------+------------+ | ... | | | | | | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ... | | | | | | +--------------------+---------+----------------------------------+--------------+------+------------+
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.
USE performance_schema
SHOW TABLES; +----------------------------------------------------+ | Tables_in_performance_schema | +----------------------------------------------------+ | accounts | | cond_instances | | events_stages_current | | events_stages_history | | events_stages_history_long | | events_stages_summary_by_account_by_event_name | | events_stages_summary_by_host_by_event_name | | events_stages_summary_by_thread_by_event_name | | events_stages_summary_by_user_by_event_name | | events_stages_summary_global_by_event_name | | events_statements_current | | events_statements_history | | events_statements_history_long | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | | events_waits_current | | events_waits_history | | events_waits_history_long | | events_waits_summary_by_account_by_event_name | | events_waits_summary_by_host_by_event_name | | events_waits_summary_by_instance | | events_waits_summary_by_thread_by_event_name | | events_waits_summary_by_user_by_event_name | | events_waits_summary_global_by_event_name | | file_instances | | file_summary_by_event_name | | file_summary_by_instance | | host_cache | | hosts | | mutex_instances | | objects_summary_global_by_type | | performance_timers | | rwlock_instances | | session_account_connect_attrs | | session_connect_attrs | | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | | socket_instances | | socket_summary_by_event_name | | socket_summary_by_instance | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | | threads | | users | +----------------------------------------------------+ 52 rows in set (0.00 sec)
See List of Performance Schema Tables for a full list and links to detailed descriptions of each table.
Since MariaDB 10.0.12, the performance schema has been disabled by default for performance reasons. You can check its current status by looking at the value of the performance_schema system variable.
SHOW VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+
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.
performance_schema=ON
You need to set up all consumers (starting collection of data) and instrumentations (what to collect):
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'; UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
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:
[mysqld] performance_schema=ON performance-schema-instrument='stage/%=ON' performance-schema-consumer-events-stages-current=ON performance-schema-consumer-events-stages-history=ON performance-schema-consumer-events-stages-history-long=ON
SHOW VARIABLES LIKE "perf%"; +--------------------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------------------+-------+ | performance_schema | ON | | performance_schema_accounts_size | 100 | | performance_schema_digests_size | 5000 | | performance_schema_events_stages_history_long_size | 1000 | | performance_schema_events_stages_history_size | 10 | | performance_schema_events_statements_history_long_size | 1000 | | performance_schema_events_statements_history_size | 10 | | performance_schema_events_waits_history_long_size | 1000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_hosts_size | 100 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 986 | | performance_schema_max_digest_length | 1024 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 2500 | | performance_schema_max_mutex_classes | 200 | | performance_schema_max_mutex_instances | 5086 | | performance_schema_max_rwlock_classes | 40 | | performance_schema_max_rwlock_instances | 2886 | | performance_schema_max_socket_classes | 10 | | performance_schema_max_socket_instances | 43 | | performance_schema_max_stage_classes | 160 | | performance_schema_max_statement_classes | 191 | | performance_schema_max_table_handles | 2858 | | performance_schema_max_table_instances | 667 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 100 | | performance_schema_session_connect_attrs_size | 512 | | performance_schema_setup_actors_size | 100 | | performance_schema_setup_objects_size | 100 | | performance_schema_users_size | 100 | +--------------------------------------------------------+-------+
Note that the "consumer" events are not shown on this list. They can only be enabled at startup.
SHOW ENGINE STATUS SHOW PROFILE ANALYZE STATEMENT
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/performance-schema-overview/