using tmpfs for mariadb tmp tables

Question

My server is writing about 50% of temp tables to disk, even when max_heap_table_size and tmp_table_size are ridiculously high (500M). Yesterday I ran a script all day that monitored the tmp folder and the total space never exceeded 100M.

My theory is big columns such as long text are forcing tmp tables to disk. I have no control over the queries or this part of the database design.

If I pointed tmpdir to a 500M tmpfs file system I could speed up these queries. I can only think of 2 reason why not to do this.

1. It takes away from memory that could be allocated to innodb buffer pool
2. Infrequent operation such as table copy, optimise, alter etc can use huge amounts of tmp space

Regarding reason 1, I'm thinking the performance gains would trump and gains by allocating more memory to innodb buffer pool, but I need to test.

Regarding reason 2, if I pointed tmpdir to a tmpfs file system and innodb_tmpdir to a large disk based system, will that avoid filling up tmpfs when doing table copy, optimise, alter etc?

Answer 1

500M for tmp_table_size is really dangerous, but not for the reason you might think.

tmp_table_size and max_heap_table_size work together. When a SELECT needs to store intermediate results for, say, a sort (ORDER BY), it first tries to do it in RAM. There are several cases where this won't work:

• A TEXT or BLOB column is being fetched. It will bypass RAM and go straight to disk. (tmp_table_size is irrelevant in this case.)
• It starts to use RAM but the temp table grows bigger than either of those two settings. At this point, it copies what it has so far to disk. (Having tmp_table_size big will actually hurt for large temp tables.)

If you have several SELECTs running at the same time, and doing the second bullet item above, that is potentially several allocations of 500M. If you run out of RAM, the system will start swapping. Swapping is much worse than spilling tmp tables to disk sooner.

Note further, that a single SELECT can cause more than one temp table to be used! One example is GROUP BY something ORDER BY some_other_thing

Bottom Line: Set tmp_table_size and max_heap_table_size to no more than 1% of RAM.

Now, back to 50% of tmp tables being on disk. Sometimes that is necessary. But there are some common cases where disk can be avoided.

• Don't ever use TINYTEXT.
• Don't use TEXT if a modestly sized VARCHAR will suffice.
• Don't blindly say VARCHAR(255); make them reasonably sized. (Before spilling to disk, they are stored in a MEMORY table, which turns VARCHAR into CHAR. Note: 8.0 fixes that.)
• Poor indexes.
• Poor formulation of queries.

For the last two, let's see a couple of the queries that are likely to be spilling to disk. I can often spot what to do.

Filesystems

• I dislike chopping disk up into filesystems. The inevitable result is running out of disk on one FS while there is lots of room on another FS.

• I dislike using ramdisk for anything; MySQL does a lot of intelligent work to make use of the RAM it is given; don't rob it for transient use.

• I strongly agree with your Reason 1 (give RAM to the buffer_pool).

• As for reason 2... Note that any DDL (Alter/Optimize/...) that needs to copy a table over would prefer to do it in the same FS so that the final step is mv, not cp + rm.

• As for OPTIMIZE TABLE, I frequently rant about how that is virtually useless for InnoDB; don't use it.