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.