MySQL, RAM disk for tmpdir - how to figure out what size it should be? We regularly run a few large SQL queries to generate reports.
It seems that the bottleneck is the amount of time that is spent writing to the tmpdir, which is currently on disk.

We are upgrading to a larger server and I would like to setup tmpdir to use a RAM disk, but am not sure what a reasonable size would be to accommodate these reports.

Is there somewhere in MySQL that might log the most amount of space tmpdir it uses, or somewhere else I should be looking to calculate this.

Thanks in advance for your help.
This is a really interesting question. 

You could check the 

SHOW GLOBAL STATUS like 'Created_tmp_disk_tables';


But that only gives you the overall number of tables created (or the delta, if you subtract that value between two points of time), but not the actual size needed.

You could go the "logical" wayin MySQL 5.6, configuring performance_schema in order to log a certain amount of queries, enough to check how many temporary tables are being created. You can find those on 

performance_schema.events_statements_history/events_statements_history_long


On the CREATED_TMP_DISK_TABLES and CREATED_TMP_TABLES. That will give you more information about the type of temporary tables created, but still no information on the actual number or rows written or the size at a moment in time.

I would probably check it the physical way (assuming a UNIX-like OS), by something like this:

sudo lsof -s | awk '{if ($1 == "mysqld" &amp;&amp; $9 ~ /\/tmp/) size+=$7} END {print size}'


Checking the total size of files that the mysqld process has open on /tmp along time may give you a better indication, assuming you monitor that at your peak time, or a significant amount of time.