Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
I suggest you set the long_query_time to 0.0 seconds (yes, zero) for a few minutes (or more but note that teh log will grow rather fast, probably to GBs size). Then you can analyze what will be essentially all queries/statements that hit the database in that period. There are some good tools for this (search for maatkit). You may find that info far more useful. It's not uncommon for a plugin to be sending hundreds of requests per second to the database. A 10ms query that is sent 100 times per second is affecting performance more than a 2 seconds query that runs once a minute. –  ypercube Jan 22 '13 at 16:00
add comment

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
2  
there's another way to get queries logged than just long-running. You can set --log-queries-not-using-indexes –  Derek Downey Jan 22 '13 at 17:05
    
Yes, you are right! –  neo Jan 22 '13 at 17:24
add comment

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

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

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:

share|improve this answer
    
The question was "How often does slow_log table gets updated in MySQL?"... –  Maxime FOUILLEUL Jan 22 '13 at 13:10
    
@mfouilleul Sorry for the overkill. Since fuzzybee did not get anything recorded, I gave an expanded overview of setting up Slow Query Logging to give fuzzybee something to compare. –  RolandoMySQLDBA Jan 22 '13 at 15:38
    
Thanks @RolandoMySQLDBA, sorry I may have not made it clear that I did get slow queries logged, just too impatient to see the next slow queries after some tuning :) –  fuzzybee Jan 23 '13 at 3:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.