Description:
Currently we can know how many disk-based temporary tables were created while statement were running, can know about files, touched by the server. But it is hard to say how large they were and if they created simultaneously. It makes hard to troubleshoot space issues under high load.
How to repeat:
Use standard database employees.
flush status;
(select emp_no, first_name, count(*) from employees join titles using(emp_no) join dept_emp using(emp_no) group by emp_no, first_name order by last_name) union all (select emp_no, last_name, count(*) from employees join titles using(emp_no) join dept_emp using(emp_no) group by emp_no, last_name order by first_name);
show status like 'Created_tmp%';
You will find out how many tables were created, but to know their sizes you will need to watch lsof output while query is running. If many queries are running in parallel it is hard to identify which one creates larger tables.
