High load of CPU on MySQL and There is No Active Query Process

tarzan1234

Verified User
Joined
Nov 4, 2012
Messages
22
Hi All,
There is high load of CPU on MySQL and when i use show processlist to find out what query making that load, there is no query active in the processlist and evry thing seems to be normal, but when i get "top" list mysql is on the top of the list with CPU load between 100% to 150%.

its really driving me crazy and i dont know how to fix it.

thanks in advanced
 

zEitEr

Super Moderator
Joined
Apr 11, 2005
Messages
14,255
Location
GMT +7.00
Hello,

You might need to enable logging of slow queries, and give more RAM to your MySQL server.
 

tarzan1234

Verified User
Joined
Nov 4, 2012
Messages
22
Hello,

You might need to enable logging of slow queries, and give more RAM to your MySQL server.
hi,
i have 2 GB ram on server and i have this server for more than a year and i never face problem like this before, and occur since 2 weeks ango, i also optimize the MySQL with /etc/my.cnf and the problem still is there.

when i restart the MySQL service every thing will go back to normal but only last for couple of minuts and again the CPU load will go to 100%
 

zEitEr

Super Moderator
Joined
Apr 11, 2005
Messages
14,255
Location
GMT +7.00
Open Webalizer/AwStats/GoogleAnalytics and try to find out what has changed... check hardware specially HDDs, as they from time to time are dying.... And as I previously wrote, you might need to enable slow query logging
 

futurevision

Verified User
Joined
Nov 26, 2008
Messages
51
My load isn't as extreme as tarzan but when the server is mainly idle (load of 0.05) the mysqld process is still consuming ~40% of cpu usage. Is this a bad thing? There are multiple sites from my clients but none of them seem to have slow queries (nothing is logged yet). How to better log which user is causing this load? Showing what queries are running only seems to return my own query, not others.
 

futurevision

Verified User
Joined
Nov 26, 2008
Messages
51
Good tip, tnx. Too bad the same is happening there as when I run SHOW PROCESSES in phpMyAdmin... the only thing I see is my own query and no others while the query counter is increasing. Still no clue which user is causing the most traffic.

I guess its mainly the same small short queries over and over again by apps without caching. I see 40% of the queries are 'DESCRIBE' queries used by some ORM scripts (Zend Framework, Doctrine, Propel), developers should cache this data.

update: turns out my load was caused by the leap second bug. After running "date -s now" the load immediatly dropped :)
 
Last edited:

zEitEr

Super Moderator
Joined
Apr 11, 2005
Messages
14,255
Location
GMT +7.00
My load isn't as extreme as tarzan but when the server is mainly idle (load of 0.05) the mysqld process is still consuming ~40% of cpu usage. Is this a bad thing? There are multiple sites from my clients but none of them seem to have slow queries (nothing is logged yet). How to better log which user is causing this load? Showing what queries are running only seems to return my own query, not others.

New versions of phpMyAdmin have a nice monitor, which could help you with understanding the issue. It shows several graphs, which are changed in real time. Try to use it, just login as da_admin user into there.
 

futurevision

Verified User
Joined
Nov 26, 2008
Messages
51
My server is synced with ntp, that wasn't the issue, it was a known bug which even occurs when its properly synced.
 

zEitEr

Super Moderator
Joined
Apr 11, 2005
Messages
14,255
Location
GMT +7.00
Why the bug appeared now, as it seems it should had happened on June 1 2012? What do I miss?
 
Top