Knowledge base
  • Goal of knowledge base
  • Linux & core
    • Linux
      • Record SSH session for reporting
      • Compress / Decompress files
      • Colorize logs
      • Cron output & logging
      • Signal
      • Break out and escape SSH session
      • Mount volume permanently
      • Show processes most consuming CPU & MEM
      • Improve and optimize battery life on Linux
      • File ownership & groups in linux
      • Automatic security update/patch on Ubuntu
      • Clean buffers and cached on linux
      • Bash completion on Linux/Mac
    • Core services
      • Nginx reload
      • OpenVPN Split tunneling
      • Nmap commands
    • Hardware
      • CPU Architecture fundamental
  • Database
    • MySQL
      • InnoDB - innodb_file_per_table parameter
      • MySQL - enable slow query log
      • MySQL - export large tables
    • MongoDB
  • Container
    • Docker
      • ADD or COPY in Dockerfile
        • Clean data of docker completely
    • Podman
  • Automation
    • Ansible
      • Output format
  • Build & Deployment
    • Jenkins
      • Jenkins - force exit pipeline when failure
  • Language & Toolset
    • PHP
      • Composer
      • php-redis & php-igbinary
  • Mindset
    • Technical based
      • Writing well
      • Reinvent The Wheel
      • Approach a new system
      • Backup philosophy
      • Mindset for building HA and scalable system
      • GitLab database incident
    • Non-technical based
      • How to read news efficiency?
      • How long should you nap?
      • Assume good faith
  • Reference & learning source
    • Books
      • Sysadmin/SRE
      • Mindsets
      • Software fundamentals
    • English
Powered by GitBook
On this page
  • What is slow query log?
  • How to enable?
  • How to turn it off?
  1. Database
  2. MySQL

MySQL - enable slow query log

What is slow query log?

  • Slow queries can affect database and server performance.

  • The slow query log consists of SQL statements that took more than long_query_time seconds to execute.

  • This greatly simplifies the task of finding inefficient or time-consuming queries.

  • By default, the slow query log is disabled

How to enable?

  1. Login to MySQL, database abc_prod

     shell> mysql -u root -p abc_prod
  2. Enable slow query log

     mysql> SET GLOBAL slow_query_log = 'ON';
  3. Check path to log file

     mysql> SHOW VARIABLES LIKE 'slow_query_log_file';
     +---------------------+-----------------------------+
     | Variable_name       | Value                       |
     +---------------------+-----------------------------+
     | slow_query_log_file | /var/lib/mysql/db3-slow.log |
     +---------------------+-----------------------------+
     1 row in set (0.00 sec)
  4. Change long query time to 5 seconds - default is 10 seconds

     mysql> SET GLOBAL long_query_time = 5;
  5. Logout MySQL session then login again

  6. Take a small test to ensure slow query log is enable

     mysql> SELECT SLEEP(10);
  7. Check sleep on slow query log

     root@db3:/home/ubuntu:~$ cat /var/lib/mysql/db3-slow.log
     /usr/sbin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
     Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
     Time                 Id Command    Argument
     # Time: 170321  7:15:52
     # User@Host: root[root] @ localhost []  Id:  1160
     # Query_time: 7.000249  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
     SET timestamp=1490080552;
     SELECT SLEEP(7);
     # Time: 170321  7:17:31
     # User@Host: root[root] @ localhost []  Id:  1161
     # Query_time: 10.000215  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
     use abc_prod;
     SET timestamp=1490080651;
     SELECT SLEEP(10);

Most important:

Using mysqldumpslow tool for summarize slow query log files

root@db3:/home/ubuntu:~$ mysqldumpslow /var/lib/mysql/db3-slow.log
Reading mysql slow query log from /var/lib/mysql/db3-slow.log
Count: 2  Time=8.50s (17s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
  SELECT SLEEP(N)

How to turn it off?

mysql> SET GLOBAL slow_query_log = 'OFF';
PreviousInnoDB - innodb_file_per_table parameterNextMySQL - export large tables

Last updated 6 years ago