Good article on MEMORY temp tables in MySQL
* This could be particularly relevant to Drupal.org if we are still *>* getting temp tables written to disk. It turns out that even when we *>* specify MEMORY for temp tables, MySQL still has the option of writing to *>* disk if other buffers aren't large enough.*
This problem has been driving me totally nuts for months now. Until I read this I've assumed that it was something that I was missing/doing wrong. Thanks for sharing the 411.
It is interesting actually, when you create a table with engine=memory..if the table is bigger than max_heap_table_size it goes to disk. However, if it is an internal temp table it looks at both max_heap_table_size and tmp_table_size and goes with the smaller. In 5.1-bk they introduced a new one too, "disk-tmp-table-size"...I'm still not sure what that is really supposed to do. Cross that bridge when the rc comes out. Oh and btw, in pre 5.0.44 if you have an internal heap table that grows over the min(tmp_table_size,max_heap_table_size) and is converted to a disk table AND the disk happens to be full at the time (say a tmpfs mount). The server will crash. :) Oh and if you upgrade to 5.0.44 you may notice that it no longer respects tmpdir, but creates the .frm files in tmpdir and the actual data files in the working directory. (this should be fixed in the next version, my bug has a patch for it committed to -bk) I love MySQL so very much... On Sat, 2007-08-18 at 11:43 -0700, Caleb Gilbert wrote:
This could be particularly relevant to Drupal.org if we are still getting temp tables written to disk. It turns out that even when we specify MEMORY for temp tables, MySQL still has the option of writing to
disk if other buffers aren't large enough. This problem has been driving me totally nuts for months now. Until I read this I've assumed that it was something that I was missing/doing wrong. Thanks for sharing the 411. -- Narayan Newton Database Administrator OSU Open Source Lab
participants (2)
-
Caleb Gilbert -
Narayan Newton