[development] Good article on MEMORY temp tables in MySQL

[Narayan Newton]

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