2

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.

2

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" && $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.

2
  • Thank you - this helped me confirm my findings. I also found a somewhat lower tech solution, I fired off the large SQL query and ran watch du -sh /mysqltmp/ >> watch_log Once the query was complete I reviewed watch_log - in this case it was about 1.2GB, so I've created a 4GB RAM disk so there is some room to grow.
    – enochroot
    Mar 3 '15 at 23:04
  • Please note that my method, unlike yours, has into account deleted inodes. Be careful those do not bite you– percona.com/blog/2012/08/06/…
    – jynus
    Mar 4 '15 at 9:14
2

I consider it a mistake to take RAM away from MySQL's cache(s) to give them to occasionally used things such as tmp disk. That is, I think it is better to let the query use a tmp disk table than to take RAM away from the caching of blocks used to compute the query in the first place.

By "reports" do you mean summarizing data in a Data Warehouse? If so, building and maintaining "Summary Tables" is likely to speed things up even more. (I often get a 10-fold speedup.) See http://mysql.rjweb.org/doc.php/datawarehouse and its companion blob about Summary Tables.

2
  • Using RAM for temp-tables makes a lot of sense if your server demands a lot of connections (high max_connections), each of which require a modicum of sort- and join-memory. You essentially move per-connection memory to a pooled memory area.
    – Otheus
    Feb 12 '21 at 9:26
  • @Otheus - And what happens when many of the "lots of connections" decide to make a temp table on that ram "disk"? And run out of room in the ram disk? Answer: queries that would have run if using a real disk will now fail.
    – Rick James
    Feb 12 '21 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.