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.
- It takes away from memory that could be allocated to innodb buffer pool
- 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?