Tag: mysqldumpslow

  • How to enable slow query log in MySQL/MariaDB

    To enable MySQL slow query login, edit my.cnf file

    vi /etc/my.cnf

    For MariaDB, edit

    vi /etc/mysql/mariadb.conf.d/50-server.cnf

    Add

    slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

    Or

    log_slow_query_file    = /var/log/mysql/slow.log
log_slow_query_time    = 2
log_slow_verbosity     = query_plan,explain
log-queries-not-using-indexes
log_slow_min_examined_row_limit = 1000

    Restart MySQL

    systemctl restart mysql

    On CentOS, the command is “systemctl retstart mysqld”. If you are using MariaDB, use “systemctl restart mariadb”.

    You can use mysqldumpslow command to view slow queries.

    mysqldumpslow /var/log/mysql/slow.log

    To test, you can run the command

    SELECT SLEEP(10);

    This will simulate a query, that take 10 seconds.