MySQL Forums
Forum List  »  InnoDB

Re: Larger than expected tmp files querying InnoDB format database
Posted by: Rick James
Date: August 29, 2014 12:40PM

> /var partition space, 3GB to be exact. The /tmp partition only has 1GB free, so I moved MySQL over to using /var/tmp already.

I dislike having a separate partition for tmpdir. It is often small and will cause queries to unnecessarily crash.

Having only 3GB available is dangerously small. And it further indicates that we need to redesign the query to avoid using 2GB for a temp table. As you say, if two such queries ran at the same time, one would have to abort!

> since I see 2GB of RAM just snoozing away. RAM will execute the query faster, faster means resources are freed up for the next big query.

What you say is correct. How much swap space? If you allow tmp queries to take 2GB of RAM and two do happen to go in parallel, will it run out of swap space?

But all of this is moot. Because of the TEXT field(s), the tmp table cannot be done in RAM; it must be done on disk.

> If so I will take the site off-line, tinker with the settings

Good idea. I can't do that for you; I can only suggest things to try.

Another thing: Do these before and after each SELECT you experiment with.
SHOW STATUS LIKE 'Handler%';
SHOW STATUS LIKE '%tmp%';
Note that the latter will give you the information about using disk or not:
| Created_tmp_disk_tables | 4537  | -- tmp tables that had to use disk
| Created_tmp_files       | 21    | -- (irrelevant, I think)
| Created_tmp_tables      | 23245 | -- total tmp tables (disk + ram)
The Handler values give cryptic clues about whether table scans are happening, etc.
(Since the values are since startup, the diffs are needed.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Larger than expected tmp files querying InnoDB format database
1175
August 29, 2014 12:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.