How often does the slow_log table get updated in MySQL?

Question

I've changed the long_query_time from 10 secs to 5 secs. I haven't seen any queries logged for a few hours now. Am I supposed to tweak my database for the slow_log table to gets updated more often?

There were slow queries that took longer than 10 seconds and I've already dealt with them. However, the first byte time for my WordPress website is still > 3 seconds according to tools.pingdom.com.

Updates:

I've just changed the long_query_time to 1 sec and still, I haven't seen any slow queries logged yet. Could it be possible that I now have no queries that takes longer than 1 sec to execute (that seems too good to be true, doesn't it?)?

I've seen queries that took longer than 1 sec now so everything is working fine for me - I see the slow queries that I need to see.

*How I got the slow queries to be logged in the slow_log table in mysql database?* I'm using Amazon RDS MySQL, so as soon as I changed slow_query_log to 1, the slow queries appear in the slow_log table.

Answer 1

The slow query log gets updated once a query is completed and the query time is beyond or equal to value set in long_query_time.

So it could mean either good or bad. The good is all the queries completed within long_query_time. The bad is the slow query is still running.

To experience a slow query you can try subqueries with multiple level.

Answer 2

By default, slow queries are not recorded anywhere.

There are three(3) ways to activate the Slow Query Log

ACTIVATION #1 : Text File

To log slow queries to a text file, please add the following to the /etc/my.cnf

[mysqld]
long-query-time=5
log-output=FILE
slow-query-log
slow-query-log-file=/var/log/mysql-slow-queries.log

ACTIVATION #2 : CSV Table (mysql.slow_log)

To log slow queries to mysql.slow_log, please add the following to the /etc/my.cnf

[mysqld]
long-query-time=5
log-output=TABLE
slow-query-log

ACTIVATION #3 : Both Methods (Text File and Table)

[mysqld]
long-query-time=5
log-output=FILE,TABLE
slow-query-log
slow-query-log-file=/var/log/mysql-slow-queries.log

SUMMARY

Once you edit /etc/my.cnf (or my.ini for Windows), you must restart mysql

• For Linux, service mysql restart
• For Windows
• net stop mysql
• net start mysql

If you would like to test the slow log after the mysql restart, simply run this:

SELECT SLEEP(12);

If long-query-time >= 12, this statement should land the the slow log.

Here are the variables used in the above methods

• long-query-time (Set this to 0 so everything and its grandmother lands in the Slow Query Log)
• log-output
• slow-query-log
• slow-query-log-file

For more information on Slow Query Logging, please read the MySQL Documentation

• The Slow Query Log
• Selecting General Query and Slow Query Log Output Destinations

CAVEAT

If you decide to use mysql.slow_log, keep in mind that the default mysql.slow_log is a CSV table. You can query against mysql.slow_log, but the table has no index.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.05 sec)

mysql>

If you would like the slow log to be a MyISAM so you can query it like any other table, please run the following:

ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);

Here is what you should see afterwards:

mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mysql.slow_log ADD INDEX (start_time);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  KEY `start_time` (`start_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

mysql>

You can read my past answers on this:

• Nov 18, 2011 : Create a custom log table in MySQL
• Mar 30, 2012 : MySQL slow log configuration