MySQL Query missing from the slow query log

Posted on

Question :

I’ve been looking at queries creating many temporary tables in the performance_schema.events_statements_summary_by_digest.

FROM performance_schema.events_statements_summary_by_digest 
where SUM_CREATED_TMP_TABLES > 0 and digest is not NULL 
order by SUM_CREATED_TMP_TABLES desc limit 1G

I have a clear worst offender:

                 COUNT_STAR: 76703
             SUM_TIMER_WAIT: 96029343223000
             MIN_TIMER_WAIT: 631886000
             AVG_TIMER_WAIT: 1251963000
             MAX_TIMER_WAIT: 19597013000
              SUM_LOCK_TIME: 14475751000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1125890
          SUM_ROWS_EXAMINED: 29941322
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 76742
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 76754
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2019-09-25 00:08:30
                  LAST_SEEN: 2019-09-25 11:22:49

If my understanding is correct. The above suggests the query has been executed 76,703 times since 2019-09-25 00:08:30 and created 76,754 temporary tables. Implicitly.

What I don’t understand is that if I set the long_query_time to 0 the query does not show up in the slow query log.
I thought if the long_query_time was set to 0 all queries are written to the log.
Thanks in advance.

Answer :

Thanks to Vinicius@Percona. He explained the reason the query does not appear in the slow query log is due to the fact that existing connections are not impacted by the long_query_time being changed. Only new connections.

by 8adger on Sep 25, 2019 “I thought if the long_query_time was set to 0 all queries are written to the log.”

Wrong thought. When long_query_time = 0, NO queries are logged to the Slow Query Log.

Leave a Reply

Your email address will not be published.