MySQL Forums
Forum List  »  Newbie

Re: MySQL eats all of RAM memory
Posted by: Rick James
Date: March 25, 2010 07:09AM

It may not be a problem...

Only one setting is excessive --
thread_cache_size = 384
I would set that to 10 or 20. It is the number of "extra" processes to hang onto. With max_connections = 330, the most you could ever cache would be 330 (if you had a flood of connections, then they all went away -- an unlikely case).

What OS? I find that some Operating systems "lie" about memory usage. This seems to be because they include cached disk blocks.

MyISAM uses RAM these ways (roughly in order, largest first)
* The 'rest' of RAM is available for caching _data_. This is handled by the OS.
* key_buffer - you have 64MB, tiny for a 4GB machine -- 640MB would be reasonable.
* "threads" - there is one OS Process per connection (up to 330, in your case). Each of these takes about 200KB when idle (you could have lots of idle ones, due to the thread_cache_size). A busy one takes a variable amount that is a function of several of the other settings, none of which seem excessive. If you have more than about 10 threads active at once, that is reason for concern, and we should look at what queries are running so long, and contending for resources so actively.

I often see a system with no active threads (when I glance at SHOW PROCESSLIST), yet it is completing _hundreds_ of queries per _second_.

Sometimes I see dozens, or hundreds, of active queries (in PROCESSLIST), perhaps with most of the "Locked". The oldest one that is not Locked is usually the villain; it needs to be optimized.

Also, check the slowlog to see if you have some repeat offenders.

Options: ReplyQuote


Subject
Written By
Posted
March 24, 2010 03:33AM
Re: MySQL eats all of RAM memory
March 25, 2010 07:09AM


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.