TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
We all know disk based temporary tables are bad and you should try to have implicit temporary tables created in memory where possible, do to it you should increase tmp_table_size to appropriate value and avoid using blob/text columns which force table creation on the disk because MEMORY storage engine does not support them Right ?
Wrong.
In fact setting tmp_table_size is not enough as MySQL also looks at max_heap_table_size variable and uses lower value as a limit to for in memory temporary table after which it will be converted to MyISAM.
To make things more confusing this is not what you would read in MySQL manual as far as I understand it:
From http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal tables that the server creates on the fly while processing queries. However, the two types of tables differ in that MEMORY tables are not subject to storage conversion, whereas internal tables are:
* If an internal table becomes too large, the server automatically converts it to an on-disk table. The size limit is determined by the value of the tmp_table_size system variable.
* MEMORY tables are never converted to disk tables. To ensure that you don’t accidentally do anything foolish, you can set the max_heap_table_size system variable to impose a maximum size on MEMORY tables. For individual tables, you can also specify a MAX_ROWS table option in the CREATE TABLE statement.
For me this description looks as there are two types of in memory tables where internal ones are controlled by tmp_table_size and explicit ones use max_heap_table_size value.
Interesting enough there is 2.5 years old bug on this matter which just recently started to get attention. I understand it could be complex to fix but why real behavior was not documented in the manual at least ?
But what surprises me the most is how this issue was fixed (patch pending):
ChangeSet@1.2311, 2006-11-16 04:11:16+03:00, ted@ted.mysql.internal +6 -0
BUG #4291 fix: new configuration option “disk-tmp-table-size”
introduced to set maximum expected on-disk temporary table size
and avoid mix-up of tmp_table_size and max_heap_table_sizeChangeSet@1.2358, 2007-01-03 14:45:26+03:00, ted@ted.mysql.internal +7 -0
BUG #4291: max_heap_table_size affects creation of disk-based temporary tablefix: the new system variable memory_tmp_table_size is introduced;
it stands now for the exact purpose the Manual says
tmp_table_size used to do.tmp_table_size retains to (give a hint about a)
limit of the on-disk temporary table size. The limit imposed upon
the disk-based temporary tables is still quite relative due to MyISAM
current implementation restrictions.
So now we’re getting 4 variables instead of two ?
It is unclear about tmp_table_size - if it is going to be read only variable to tell you what maximum temporary table size is or is it going to limit on disk table size ? Any of behaviors have nothing to do with previous behavior and second one would break a lot of things.
In my opinion it would be much better to change it to match what users know about it, what is documented in the manual, config files, tons of books and articles on the web - you will have users expecting old behavior for years.
The size restriction of on disk temporary table could be good to add but that should have been another variable.
Print This Post
del.icio.us :: 
digg Comment RSS :: Trackback URI
11 Comments
I remember looking in the code, that tmp_table_size is also used for certain internal data structures, and setting it higher was a small tweak that one could do for overall performance gain. Now there is this, good to know.
Comment :: January 20, 2007 @ 5:10 pm
Yes quite possible.
In fact I remember there were few other cases when non documented parameters are taken into consideration
Comment :: January 20, 2007 @ 5:20 pm
The description is slightly wrong.
The lower value from tmp_table_size and use max_heap_table_size was used to define when temporary table was converted from memory to disk.
Also tmp_table_size was used to limit the size of the temporary table - no matter whether it’s in memory or on disk.
And max_heap_table_size was also used to limit the size of explicitly created HEAP tables.
The fix is to introduce another variable memory_tmp_table_size and use it instead of the “lower value …”, to define when a temporary table is converted to MyISAM. Thus max_heap_table_size will have no effect on temporary tables, and tmp_table_size keeps its “upper boundary” meaning.
Comment :: January 21, 2007 @ 5:18 am
Sergei,
Yes I know lower value was used but it was not documented - I quoted the manual and config files give you the same idea - want larger in memory tables increase tmp_table_size.
Now regarding “Also tmp_table_size was used to limit the size of the temporary table - no matter whether it’s in memory or on disk.” Where does this come from ?
I constantly see temporary tables sized in gigabytes while tmp_table_size is set to 32M or similar.
If that would be correct fix would be reasonable but it is not. Simply test it
Comment :: January 21, 2007 @ 2:11 pm
I saw that in the code. But now, after testing, I see that, indeed, tmp_table_size doesn’t limit the size of on-disk tables (table->s->max_rows is set to limit it, but it’s not copied to ci->max_rows for mi_create to see it) - it must be a bug too.
Comment :: January 21, 2007 @ 3:41 pm
Thank you for looking into in Sergei
While fixing bug I just would make sure there are limited behavior changes compared to old timers expectations.
Comment :: January 21, 2007 @ 3:44 pm
Peter, thanks for mentioning this. I expect that the plan will be changed now Sergei knows that it would break backwards compatibility. A new synonym for tmp_table_size that is the memory limit and a new variable for disk size would do the job without breaking existing setups. Then tmp_table_size can be deprecated.
Comment :: January 21, 2007 @ 11:51 pm
Peter, reading this I thought I recalled reading about it in the docs somewhere.
I looked around and found it here:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
(scroll down to tmp_table_size)
Not defending the behavior by any stretch; it is highly anti-intuitive.
-James Holt
Comment :: January 23, 2007 @ 12:48 pm
Thank you James,
I see it got added there. As MySQL old timer I did not get back to that page for other than new variables as I remember meaning about them.
The quote I gave was from this url:
http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
I guess I would expect working do be different in this place to.
It is probably hard to keep such large document synchronized.
Comment :: January 23, 2007 @ 1:03 pm
[...] så mycket information jag kan om ämnet genom att läsa mysqls optimeringskapitel, forumtrådar, blogginlägg, tricks och tricks [...]
Pingback :: September 19, 2007 @ 1:25 pm
tmp_table_size=45M , if its increse or decrese what will the effect of mysql database
Comment :: November 15, 2007 @ 3:36 am