MariaDB Slow Query Log, part 1

MariaDB Slow Query Log, part 1

Colleagues hello to all.

In today's article I will tell you about such a component as slow queries (Slow Query Log) in MariaDB. Slow queries are those queries that take longer than the time you specify.

 

 Article content:

  1. Variable status slow_query_log.
  2. Slow query log storage directory.
  3. Enable slow_query_log logging.
  4. Setting the time limit long_query_time.
  5. Check.

 

1. Slow_query_log variable status.

The  slow_query_log variable is responsible for enabling the slow query log. It is disabled by default. To check the state of a variable, use the command:

mariadb> SHOW VARIABLES LIKE 'slow_query_log';

slow_query_log

The slow query log is disabled.


 

2. Slow query log storage directory.

By default, the log will be stored in the directory where you have all the database files (datadir). I recommend keeping such things in separate directories. The  slow_query_log_file variable is responsible for the name of the log and storage directory. To check the state of a variable, use the command:

mariadb> SHOW VARIABLES LIKE 'slow_query_log_file';

slow_query_log

 

The slow_query_log_file variable is dynamic, so we can change the values ​​with the SET GLOBAL command.

mariadb> SET GLOBAL slow_query_log_file = '/mariadb/log/slow_log_query.log';

slow_query_log

 

Well, let's add a variable to the configuration file.

slow_query_log


 

3. Enable slow_query_log logging.

Although the  slow_query_log variable is dynamic and it would be possible to change the values ​​from OFF to ON through the SET GLOBAL command, but for some reason the DBMS does not change it... So we will change it through the configuration file.

slow_query_log

Save and restart the database.

 

Check.

slow_query_log

slow_query_log

Log included.


 

4. Setting the time limit long_query_time.

The long_query_time variable is responsible for the time limit limit, after this time the query will be considered slow in execution. The default is 10 seconds. All queries that take longer than 10 seconds will be considered slow and will be logged.

mariadb> SHOW VARIABLES LIKE 'long_query_time';

slow_query_log

Default is 10 seconds.

 

The long_query_time variable is dynamic, so let's try to change the limit from 10 to 5 seconds via the SET command.

mariadb> SET long_query_time = 5.0;

slow_query_log

 

Well, let's add a variable to the configuration file.

slow_query_log


 

5. Check.

Let's test the component's robot. Let's execute a query that will run for 6 seconds.

slow_query_log

slow_query_log

As you can see, there is an entry in the log that the request was executed on 6.000605


 

Thank you all, I hope that my article helped you in some way.

SIMILAR ARTICLES

MariaDB - ERROR Incorrect definition of table
MariaDB - ERROR Incorrect definition of table
MARIADB
Read more
Mariabackup backup - gzip compression
Mariabackup backup - gzip compression
MARIADB
Read more
Mariabackup error - Error writing file UNKNOWN errno 32 Broken pipe
Mariabackup error - Error writing file UNKNOWN errno 32 Broken pipe
MARIADB
Read more