Server issue : Mysql overloaded (100%cpu)

Question

Hi OCS Community,

Since a week my OCS server is unstable. MySQL become overloaded after the auto inc reach an high value on software table. I made a cleanup on this table with a script that create new software table and copy old datas in.

As it was not working well so I chose to restore the server from a 1week old backup (it's a VM) but problem appears again after 30min ! Was my backup inconsistent ? Have you some experiences in this kind of problem and have you some tips to identify the root cause ?

It's a MySQL 5.5.34 , Apache 2.2.22 on ubuntu 13.04.1 , 5000 ocs agents.

Thank you for your advises / help.

Excuse my English i'm french ;)

Answer 1

You must debug your mysql. At first, begin to look all running process in mysql with :

show full processlist;

If it come from a big table, think to enlarge memory usage for mysql and create index.

Answer 2

Hi Kapouik, 

Thank you for you answer !

Plenty of update on the softwares table. By a SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY TIME show that average time is 1 sec on query.

The thing is the main Mysql process stay stuck over 200%. If I change IP address, no OCS agent are able to connect on the server and all work nice, I'm able to use the web interface without lantencies and to query the database from access/excel ODBC. It look like agents are the root cause of this and not only too many sql queries or process.. 

Anybody already experience this ?

Thank you,

Boris