mysqld : multiple tmpdir & balancing

Question

Our 1Tb tempdir can sometimes be completely used by mysqld, resulting in disk-full and query errors. This can be due to lots of mid-size queries, or couple of very big queries.

We have a 5Tb raid drive I could use to expand this tempdir.

The manual says the /etc/my.cnf --tmpdir option can be set to a list of several paths, and that it can spread the load effectively.

If I set --tmpdir=/my1TbTemp/:/my5TbTemp/ I am wondering how mysqld will manage these two tempdirs. If the 1Tb disk is nearly full: will mysqld nicely switch to the 5Tb drive?

I've run some tests on a small virtual machine, but I'm not sure I can fully replicate the behavior of our production machine.

score 2 · Accepted Answer · answered Oct 16 '14 at 13:17

2

it is round robin so the first query will go to my1TbTemp and the next will go to my5TbTemp. If the first is full it will then move to the my1TbTemp for the query.

answered Oct 16 '14 at 13:17

Mike

21,910
7
55
79

1

Thanks Mike - So I assume there's still a chance that a very big query tempfile will land on the 1Tb and crash it. – Olivier Delrieu Oct 16 '14 at 13:29
1

yes it is possible – Mike Oct 16 '14 at 16:00

mysqld : multiple tmpdir & balancing

1 Answers1