MySQL Forums :: Newbie :: Optimising max_heap_table_size & tmp_table_size


Advanced Search

Optimising max_heap_table_size & tmp_table_size
Posted by: Peter Laws ()
Date: March 18, 2009 08:24AM

Server: Intel Daul Core with 2GB of ram in 32bit mode.

I would like to minimise the use of temporary tables being written to disk, however whatever size I set tmp_table_size and max_heap_table_size to (even 512M!), theres still 68%-80% of tables written to disk after 4 days.

tuning_primer.sh:
Quote

Of 17822 temp tables, 75% 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
This was when both were set to 128M by the way.

I read somewhere that tmp_table_size and max_heap_table_size should be the same, but from peoples experience (as I've seen from forums), it will create more temp tables on disk. But I thought the purpose of these settings were to prevent (most) temp files being written to disk?

Maybe my other settings are getting in the way of this by hogging memory?

Any advice would be grateful :)

Options: ReplyQuote


Subject Written By Posted
Optimising max_heap_table_size & tmp_table_size Peter Laws 03/18/2009 08:24AM
Re: Optimising max_heap_table_size & tmp_table_size Rick James 03/19/2009 07:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.