Configure MariaDB server to log slow queries.

Log slow queries to a file

Check log_output variable.

$ sudo mariadb --skip-column-names --batch --execute "show variables like 'log_output'" 

log_output      FILE

Check if slow_query_log is enabled.

$ sudo mariadb --skip-column-names --batch --execute "show variables like 'slow_query_log%'" | column -t

slow_query_log       OFF
slow_query_log_file  bullseye-slow.log

Log file will be stored in data directory as slow_query_log_file variable does not contain full path.

$ sudo mariadb --skip-column-names --batch --execute "show variables like 'datadir'"

datadir /var/lib/mysql/

Determine how long the query needs to run (10 seconds) to be logged.

$ sudo mariadb --skip-column-names --batch --execute "show variables like 'long_query_time'"

long_query_time 10.000000

Enable slow query log at this moment using the current configuration.

$ sudo mariadb --batch --execute "set global slow_query_log=on;"

Enable slow query log when the server is started.

$ echo -e "[mariadb]\nlog_output=FILE\nslow_query_log\nslow_query_log_file=bullseye-slow.log\nlong_query_time=10.000000\n" | sudo tee /etc/mysql/mariadb.conf.d/51-slow_query_log.cnf

[mariadb]
log_output=FILE
slow_query_log
slow_query_log_file=bullseye-slow.log
long_query_time=10.000000

Inspect slow query log.

$ sudo tail /var/lib/mysql/bullseye-slow.log 

# Time: 210421 21:45:26
# User@Host: root[root] @ localhost []
# Thread_id: 42  Schema:   QC_hit: No
# Query_time: 10.000036  Lock_time: 0.000081  Rows_sent: 90200  Rows_examined: 90200
# Rows_affected: 0  Bytes_sent: 101241
SET timestamp=1619041526;
select * from log_entries;

Log slow queries to a table

Check log_output variable.

$ sudo mariadb --skip-column-names --batch --execute "show variables like 'log_output'" 

log_output      FILE

Set log_output to a table at this moment.

$ sudo mariadb --batch --execute "set global log_output=table;"

Check if slow_query_log is enabled.

$ sudo mariadb --skip-column-names --batch --execute "show variables like 'slow_query_log'"

slow_query_log       OFF

Determine how long the query needs to run (using seconds) to be logged.

$ sudo mariadb --skip-column-names --batch --execute "show variables like 'long_query_time'"

long_query_time 10.000000

Enable slow query log at this moment using the current configuration.

$ sudo mariadb --batch --execute "set global slow_query_log=on;"

Enable slow query log when the server is started.

$ echo -e "[mariadb]\nlog_output=TABLE\nslow_query_log\nlong_query_time=10.000000\n" | sudo tee /etc/mysql/mariadb.conf.d/51-slow_query_log.cnf

[mariadb]
log_output=TABLE
slow_query_log
long_query_time=10.000000

Inspect slow query log.

$ sudo mariadb --batch --execute "SELECT * FROM mysql.slow_log limit 1\G"

*************************** 1. row ***************************
    start_time: 2021-06-20 15:06:38.823480
     user_host: root[root] @ localhost []
    query_time: 00:00:10.000169
     lock_time: 00:00:00.000134
     rows_sent: 0
 rows_examined: 100003
            db: 
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: select * into @discard from `application`.`users`
     thread_id: 29
 rows_affected: 1

Additional notes

Please read Slow Query Log Overview for more detailed information on this subject.

ko-fi