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.
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.