Category: MySQL

MariaDB Performance Schema is a powerful tool designed to monitor and analyze the performance of your MariaDB server. It provides detailed insights into the internal workings of the database, allowing database administrators and developers to identify and optimize performance bottlenecks.

Enabling Performance Schema

By default, the Performance Schema is disabled

To enable it, you need to add the following line to your MariaDB configuration file (my.cnf):

[mysqld]
performance_schema=ON

After enabling it, you can verify its status 

SHOW VARIABLES LIKE 'performance_schema';

Configuring Performance Schema

Once enabled, you need to set up all consumers and instrumentations

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';

You can also configure these settings in your my.cnf file

[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

Check the table present in performance schema:

USE performance_schema;
SHOW TABLES;

key tables include:

• events_statements_current: Current statement events.
• events_statements_history: Ten most recent events per thread.
• events_statements_summary_by_digest: Summarized statement events by scheme and digest.
• events_waits_current: Current wait events.
• events_waits_history: Ten most recent wait events per thread.

Identifying Slow Queries

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE `SUM_TIMER_WAIT` > 1000000
ORDER BY `SUM_TIMER_WAIT` DESC;

Monitoring Resource Utilization

SELECT * FROM performance_schema.events_stages_summary_global_by_event_name;
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name;

Analyzing I/O Performance

SELECT * FROM performance_schema.file_summary_by_instance;
SELECT * FROM performance_schema.table_io_waits_summary_by_table;

Synchronization Analysis

SELECT * FROM performance_schema.mutex_instances;