Query Profiling and Performance Analysis in MariaDB using Slow Query Log, Performance Schema, and Monyog

Query profiling (Query Profiling) is a diagnostic technique provided by MySQL/MariaDB to analyze SQL execution time distribution, system time, CPU user time, and locate key functions in source code.

Enabling profiling allows users to view per‑session SQL performance details, making it essential for diagnosing performance bottlenecks in large applications.

MariaDB inherits MySQL’s slow query log (Slow Query Log) and adds the ability to configure variables such as slow_query_log , long_query_time , log_output , and slow_query_log_file in my.cnf or my.ini to control logging behavior.

Typical configuration:

slow_query_log = 1
long_query_time = 5
slow_query_log_file = /var/log/mysql/slow-query.log
log_queries_not_using_indexes = ON

The logged queries can be examined directly or summarized with mysqldumpslow to produce aggregated reports, e.g.:

mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log

Performance Schema, introduced in MariaDB 5.5, provides a storage‑engine‑based set of tables that expose detailed performance metrics. It must be enabled at server start ( performance_schema=on ) and consumers/instruments must be activated, for example:

UPDATE performance_schema.setup_consumers SET ENABLED='YES';
UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE '%statement/%';
UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE '%stage/%';

Using these tables (e.g., events_statements_history_long , events_stages_history_long ) analysts can retrieve statement IDs, execution times, and stage nesting information for deeper insight.

Monyog is a commercial monitoring tool that visualizes slow query logs and Performance Schema data, offering dashboards, trend charts, query analysis panels, filtering, and CSV export. It simplifies the handling of large log volumes and provides actionable metrics for DBAs.

In summary, combining native MariaDB tools (Slow Query Log, Performance Schema) with a monitoring solution like Monyog enables comprehensive query profiling, performance diagnosis, and optimization.