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:
- Variable status slow_query_log.
- Slow query log storage directory.
- Enable slow_query_log logging.
- Setting the time limit long_query_time.
- 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';
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';
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';
Well, let's add a variable to the configuration file.
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.
Save and restart the database.
Check.
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';
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;
Well, let's add a variable to the configuration file.
5. Check.
Let's test the component's robot. Let's execute a query that will run for 6 seconds.
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.
