In High Performance MySQL (3rd edition) they state that:
You can't tell whether a table was created in memory and then converted to on-disk or just created on-disk to begin with...
What I was wondering though is -
At the point where (the lower of)
max_heap_table_size is exceeded will a new query (thread_id) be created that will be the same query but with thread state of
Copying to tmp table on disk instead of
Copying to tmp table?
During some recent debugging we noticed a second query get created right around the 300 second mark but both were of thread state
Copying to tmp table. But occasionally we'd see the second query with thread state
Copying to tmp table on disk.
The query is the result of a report that only a particular role can request and we instructed those people not to request the report so we are fairly confident that it is not user-initiated.
We also looked at timeouts in MySQL and our application but nothing seems to align with the 300 seconds (there is a MySQL
delayed_insert_timeout that defaults to 300 seconds but we changed that value and the second query/thread still was created at 300 seconds).