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?

  • 768M is nothing these days. Just pay more for your virtual machine and up it significantly. – Philᵀᴹ Apr 4 '15 at 23:06
  • I understand I could pay more for it but I use managed service so it is a bit more. – Chris Muench Apr 5 '15 at 1:23

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 ...

| improve this answer | |
  • I know what query is causing it; but it is hard to optimize it because all my reporting depends on it. The problem happens when a user tries to run a report on 1-2 years at a time with a lot of data. For now I will switch to /tmp so at least I don't cause issues. I don't think it is 50 active connections in process list. My host just gave me a number and I am unsure how they calculated it. When I did show process list I saw 2 queries. – Chris Muench Apr 5 '15 at 2:03
  • Well, let's talk about Data Warehouse techniques such as Summary Tables. I often get 10-fold speed up by using such. – Rick James Apr 5 '15 at 2:12

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.