MySQL Forums
Forum List  »  Optimizer & Parser

Re: CPU load very very high !!!!, Please assist !!
Posted by: Rick James
Date: January 22, 2013 11:32PM

High CPU --> Poor indexes and/or poorly formulated queries. Period.

> "SELECT vtiger_contactdetails.firstname "
Let's see the whole statement. (Use SHOW FULL PROCESSLIST, if necessary. Or look in the slowlog.)

It is probably a missing index. Or possibly a naughty subquery. Oh, they can be really naughty! When you find His Naughtiness, EXPLAIN him. (Or Her?) And provide SHOW CREATE TABLE for each table in the SELECT.

The tunables seem reasonable (assuming you have at least 6GB of RAM). Oh, you are the naughty one. You have overcommitted RAM. You have only 3GB of RAM, 2GB of which is for the buffer_pool, and most of the rest is consumed by other things. That will lead to swapping.

But... Swapping is not your main symptom. Hmmm...

Meanwhile, lower the buffer_pool to 1500M.

> Concurrent connections reach to 200
That is just a side effect -- The queries are not finishing quickly, so they are piling up.
> max_connections = 100
Hmmm... can't have 200 if the limit is 100. Oh, well, that is not important (yet).

> #thread_stack= 150M
Do NOT uncomment that! Each stack should be more like 256K.

> query_cache_size = 256M
That probably hurts. See
http://mysql.rjweb.org/doc.php/memory

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: CPU load very very high !!!!, Please assist !!
3029
January 22, 2013 11:32PM


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.