MySQL Performance Blog

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_size

ChangeSet@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 table

fix: 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.

It looks like it looks like there are going a lot of discussions about future of MySQL Community Binaries, see for example this post and Kaj’s clarifications.

Obviously now it grew to a lot of speculations and many comments are far from real story. It is also really interesting to watch MySQL try to balance situation of maximizing revenues and minimizing missing off community at the same time. It is moving target and balance may shift in the future one direction or another depending on how things go.

Some of confusion I guess comes from my post where I speak about recent version and so recent binaries which were not available at that point. In many cases “recent” word seems to be omitted and people are just speaking about binaries in general.

As Kaj explains now there will be official MySQL Community binaries, including Windows but they will be rarely released.

For many users running old MySQL binary is not the problem, it is however the problem if you run into the bug, which may already be fixed in newer version but binary with bugfix is not yet available. I guess having bug fixes promptly available is one of the selling points of MySQL Enterprise.

Windows build specially come into the picture as Windows users are not used to building software and most do not even have tools needed to perform the build available. Furthermore there are no vendors which will create binaries for you as happens with Linux.

The good news are – this only applies to Official binaries. I expect there would be unofficial builds popping up, we for example published our build of MySQL 5.0.33 for Linux x86-64. Scott has provided builds both for Linux i386 and Windows. I guess over next few months community will establish trusted sources of MySQL Builds and you will be able to get good builds promptly with new community releases. As long as MySQL Enterprise sources continue to be available I expect people will create builds of these as well, under different name of course.