My website is randomly very slow by high MYSQL CPU, and I have no idea what the problem is. Hopefully I will come closer to the solution with this post.
If I check the mysql processes, I see a lot of open sleep commands when the website is slow. When there are none open sleep commands, there is no problem. So probably the problem lies there.
Curiously, this problem only happens sometimes. Sometimes I have no problems for 4 weeks, now the website is already 3 days in a row slow.
I already set the time_out mysql settings to 30 secs, but that does not help to solve the problem. So now I try to figure out what the problem is. Here are the alert status variables. Perhaps that gives some extra insights.
Could it be spambots/brute-force attacks?
Heavy/slow query's in the website?
Bad server settings?
Sql injections?
Other things that this problem can cause? What is the best way to tackle this problem now? Sending any direction will already help.
Best Answer
IMHO,reason can be one of the following
i) Inline query :It is prone to sql injection attack.but not necessarily website will be slow.It is Advisable to use parameterise proc.
ii) Unpotimize query : Yes.shortlist those page and related query/proc.
iii) CSRF Attack : your pages can be attack by malware or web crawler.There are various way to overcome it,one of them is Captcha, but not always.google it.
iv) Network/server configuration :this should point i).check with your admin.
v) check for infinite and uncessary loop in pages.Infact identiyfy pages which are slow then examine both code and query.