Tmpfs for mysql tmp dir questions I have a 768M tmpfs dir that I use for mysql temp directory. It occasional fills up when running reports on large date ranges. When this happen the report fails and I get an error 28.

This actually seems acceptable to me because it prevents a query from running too long and put load on the server. (I think)

The advantage to using tmpfs is queries that use temporary tables run 50% faster.

My question is: is the tmpfs fills up while an insert or replace into is running will this fail?

Also it seems that when mysql fills the directory up the files are then deleted when the query fails. Will there ever be a case where it doesn't?

Do you think I could cause any issues using /tmp for MySQL temp dir? I have about 50 concurrent connections but not all of them are running reports at once.

What are the pros and cons of not using tmpfs?
If your 50 connections all want to do SELECTs that need tmp tables, even small ones, they could overflow tmpfs and fail right and left.

I prefer to let tmp tables be in the same filesystem where everything else is.  That way, it is not arbitrarily causing trouble when there is a spike in traffic.

Meanwhile, I keep track of the slowlog and aggressively tackle any slow queries.  Note that queries using big tmp table tend to be slow, so the slowlog generally catches them.

Generally, optimizing a query will provide much more than a 50% improvement.

Optimizing queries decreases the "load".

50 concurrent connections actually active simultaneously running (not just "Sleep" in SHOW PROCESSLIST) is unacceptable in my book.

When a query dies, it should remove the tmp table.  If the server crashes, there is a chance that the tmp table will remain.  This often happens with ALTER.

When a write operation dies in the middle, and the table is Engine=MyISAM, the write will be partially finished, and there is no way to know how much was done or not done.  Examples:  INSERT ... SELECT ... and DELETE ... WHERE ... SELECT ...