The slow query log is nothing but a log of SQL queries that took a long time on your server. You can use this facility to find out SQL queries that are slowing down your dynamic web app. Please note that default disables the slow query log. So let us see how to set and enable slow query log in MariaDB server.
How to activating the slow query Log
Edit my.cnf or mariadb.cnf stored in /etc/mysql/ directory. In this example I am editing the /etc/mysql/mariadb.conf.d/50-server.cnf file:
$ sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
Append the following syntax in [mysqld] section:
slow_query_log = 1 long_query_time = 1 slow_query_log_file = /var/log/mysql/slow-query.log log_queries_not_using_indexes
Save and close the file. Where,
- slow_query_log = 1 – Enable the slow query log.
- long_query_time = 1 – Set time in seconds/microseconds defining a slow query.
- slow_query_log_file = /var/log/mysql/slow-query.log – Name of the slow query log file
- log_queries_not_using_indexes – Whether to log queries that don’t use indexes
Restart the mariadb server
Type the following command:
$ sudo /etc/init.d/mysql restart
OR
$ sudo systemctl restart mysql
OR for RHEL/CentoS
$ sudo systemctl restart mysqld
View your slow query log
Type the following command:
$ sudo tail -f /var/log/mysql/slow-query.log
Sample outputs:
# Thread_id: 950 Schema: wpblog QC_hit: No # Query_time: 0.006252 Lock_time: 0.000032 Rows_sent: 499 Rows_examined: 816 SET timestamp=1490818968; SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'; # User@Host: bloguser[bloguser] @ www1 [192.168.162.36] # Thread_id: 951 Schema: wpfront QC_hit: No # Query_time: 0.009001 Lock_time: 0.000038 Rows_sent: 448 Rows_examined: 2184 use wpfront; SET timestamp=1490818968; SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
You need to use the pt-query-digest to analyzes MySQL queries from slow, general, and binary log files.
$ pt-query-digest /var/log/mysql/slow-query.log
For more info see this page.
🐧 2 comments so far... add one ↓
Category | List of Unix and Linux commands |
---|---|
File Management | cat |
Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
Network Utilities | dig • host • ip • nmap |
OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
Package Manager | apk • apt |
Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
Searching | grep • whereis • which |
User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
You definitelly need this to analyze: https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html
Thanks. I just used pt-query.