Hello all,
I am a newbie MySQL admin and i plan to use maat script to analyze my server in some time. But at the moment i am having some issues and i hope some one can assist me.
This is a dedicated MySQL server and if you look at the CPU utilization, it is above 90% and this is my problem 
top - 12:06:47 up 12 days, 4:02, 3 users, load average: 14.46, 13.09, 11.94
Tasks: 200 total, 2 running, 198 sleeping, 0 stopped, 0 zombie
Cpu(s): 92.6%us, 2.7%sy, 0.0%ni, 4.4%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 32945432k total, 4790872k used, 28154560k free, 107364k buffers
My MySQL server specs are as follows :-
Mysql ver :- mysql Ver 14.14 Distrib 5.1.61
DB Name=newcrmdb
DB Engine = INNODB
DB size = 7.9MB
DB Rows = 580
I just have one Db that is being continuously hammered by number of users, For reference i am attaching the “show processlist” txt file attached along with this message.
Can any one please go through it and suggest me what can i do to get the load down on the Db ?
Hi,
Sending data usually means that MySQL is reading or sending rows, so the SELECT query is being processed. The query is not complete so I don’t know if you are doing GROUP BY, ORDER BY or have a WHERE clause. To get the full query from the processlist you should do:
SHOW FULL PROCESSLIST;
Seems that there are two queries that could be causing the high load on your server:
SELECT vtiger_contactdetails.firstname, vtiger_contactdetails.lastname, vtiger_contactdetails.mobile…
SELECT vtiger_leaddetails.lead_no, vtiger_leaddetails.lastname, vtiger_leaddetails.firstname, vtiger…
I recommend you to capture those queries using slow query log and then check if an optimization is possible on them. If the problem happens again you should also check the output of SHOW ENGINE INNODB STATUS\G and get storage I/O usage statistics using pt-diskstats:
http://www.percona.com/doc/percona-toolkit/2.1/pt-diskstats. html
I’m sorry to not be very helpful here, but we don’t have too much information. Anyway, my guess is that the problem could be solved optimizing those queries.
Hello miguelangelnieto,
Thank you so so very much for taking time out and giving me pointers for optimization.
Since, We had restarted the MySQL service yesterday, The load on the server today is very optimal and not causing any issues.
But as per your suggestion, I have also run “SHOW ENGINE INNODB STATUS\G” and i have attached the results along with this mail for your reference, Can you look into this also and see if all is ok ?
Thank you so much once again.