MySQL Forums
Forum List  »  Performance

Re: Out of memory - on any moderate query
Posted by: Rick James
Date: May 24, 2014 03:18PM

> Why the huge tmp_table size and max_heap_table_size? By themselves they've eaten more than half your mem.

Worse than that! These are allocated as needed -- possibly more than one at a time per connection. Lower those values! 64M might be reasonable. Note: for automatically generated temp tables, they will spill to disk, so you do not have to make these values ultra large. For MEMORY tables, you only need to set a suitable max_heap_table_size just before CREATEing each ENGINE=MEMORY table.

> key_buffer_size = 16G

That's for caching INDEXes (.MYI files). Peter's query finds the combined space for all the .MYI files; the key_buffer does not need to be bigger than that. But also it should not be "too big". The rest of free memory (less than 42-16 = 26G) is for caching data (.MYD files). Don't starve the data caching by setting other things so high.

> query_cache_type = 1
> query_cache_size = 1G

Those are really bad. When _any_ insert occurs, it has to scan through the 1GB to purge _all_ entries for the table being inserted into. Regardless of how much RAM you have, limit query_cache_size to perhaps 50M.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Out of memory - on any moderate query
862
May 24, 2014 03:18PM


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.