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 the fact that 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.
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):
[email protected], 2006-11-16 04:11:16+03:00, [email protected] +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[email protected], 2007-01-03 14:45:26+03:00, [email protected] +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 the 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 the on-disk temporary table could be good to add but that should have been another variable.
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.
Yes quite possible.
In fact I remember there were few other cases when non documented parameters are taken into consideration
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.
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 🙂
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.
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.
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.
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
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.
tmp_table_size=45M , if its increse or decrese what will the effect of mysql database
Hi again
last comment was on 2007. Do you know if bug is fixed now? Peter says that “I constantly see temporary tables sized in gigabytes”. Could you be so kind to explain to us how you see it?
Thanks!!
Hi, excuse me for ask before find it better. I read this on MySQL,
“ChangeSet@stripped, 2007-01-03 14:45:26+03:00, ted@stripped +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 I understand this buf was fixed to 5.X and tmp_table_size is now deprecated, isn’t it?
Thanks!
mysqlreport reports:
__ Created Temp ________________________________________________________
Disk table 112 0.1/s
Table 332 0.2/s Size: 512.0M
File 88 0.1/s
I read on the mysqlreport doco that ‘Disk table’ is used when a temporary table will not fit in the allocated tmp_table_size (in this case 512MB).
My first question is: what is the difference between internal memory (tmp_table_size) and explicit memory (max_heap_table_size)?
My second question is: how can you determine the
1.largest
2.most frequent
table size created on disk so that tmp_table_size (or is it max_heap_table_size, or both?) can be adjusted upwards in size?
As it happends, we are running into performance issues when we set both of these variables to 128M on our MySQL server instead of leaving these values untouched (they were not set in my.cnf so 16M is the default). Alot of simple “SELECT RowById WHERE ColumnA IN (SELECT Values From TEMPTABLE)” queries suddenly took up to 10x as long as before.
When we change the MySQL config back, everything was back to normal.
Can anybody hint me to an explanation of some sort?
Thanks!
Hi everyone
I am using wordpress and drupal, and i converted databases from MyISAM to InnoDB, mysql tunning script gives below results for temp table , server has 4G ram, which almost uses, as many sites of both drupal and wordpress are running, can any one please guide me, is it safe values ? or can i increase (max_heap_table_size & tmp_table_size) , what values you experts are recomended ?
TEMP TABLES
Current max_heap_table_size = 128 M
Current tmp_table_size = 128 M
Of 21559 temp tables, 44% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.
@Murzi
you can not say that that value is okay.Its totally based on your test cases for the website.For some cases it will be 32M and in some case it will be 128M.Its totally based on your test case for optimization.
i am write query but query use order by than take a time
but same query without order by 0.012 sec. than use a order by take a 80.567 sec
give any solution .. increase a perforation