MySQL – How Often is Slow_Log Table Updated?

MySQLperformanceslow-log

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.

Best Answer

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.

Related Solutions

Mysql – Why are the queries getting really slow

Take a look the queries in your question

/* Execution time 7.0095062E-5 */
SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND tt.count > 0 ORDER BY t.name ASC

/* Execution time 5.3167343E-5 */
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (77375,77379,77381)

/* Execution time 9.2029572E-5 */
SELECT wp_posts.* FROM wp_posts WHERE ID IN (77381,77379,77375)

Look at the execution time above each query

7.0095062E-5 is .000070095062 seconds
5.3167343E-5 is .000053167343 seconds
9.2029572E-5 is .000092029572 seconds

The SQL queries themselves are fast.

Please read any release notes on WordPress 3.4.1 to see if WordPress upgrade installed any DB-related modules that need tuning or deactivation. Also, check for any data that was added during the upgrade process that may have bloated your tables.

Mysql – Enabling “log_queries_not_using_indexes” disables “long_query_time”

That is expected behavior. Refer to the docs online, but in summary:

long_query_time is the threshold for query execution time beyond which it is logged. Any queries taking longer than the threshold are logged, regardless of whether they use an index or not.
log_queries_not_using_indexes tells MySQL to additionally log all queries that do not use an index to limit the number of rows scanned. Logging on this condition happens regardless of execution time.

Hope that helps explain what you are seeing. It seems like you probably just want long_query_time and not log_queries_not_using_indexes if your goal is to only capture queries that take longer than a particular threshold.

Doc links: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-queries-not-using-indexes

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_long_query_time

Best Answer

Related Solutions

Mysql – Why are the queries getting really slow

Mysql – Enabling “log_queries_not_using_indexes” disables “long_query_time”

Related Question