mysql using up all memory

[ukscotth]

Hi,

 

Recently I have been getting emails warning me of a high system load ( up in the 20s ). I look at the processes and its always mysql thats using all the cpu. I have been fixing the problem by simply rebooting the server which obviously isnt the right thing to do.

 

Can anyone please tell me how I can find out what site and user it is that is causing the high cpu load when it happens ?

 

Many thanks,

 

Scott.

 

 

[requinix]

The title says memory but your post says CPU.

 

MySQL should be using as much memory as it possibly can. That's a good thing. If it's using too much CPU then it's probably choking on some difficult queries. Use the slow query log (or do some SHOW FULL PROCESSLISTs) to see what it's trying to work on during those high loads.

[ukscotth]

Sorry I meant CPU. How can I see the slow query log ? also I can access the processlist through phpmyadmin but only when im logged in as a user, is there a way to do it for all users ?

 

Thanks.

[requinix]

The slow query log. It's generally disabled because it can be a bit of a performance hit but at that point you're already suffering.

 

For the query, try connecting as root.

[ukscotth]

Thanks but where do i login as root, in phpmyadmin or putty ? sorry for being dumb lol

[requinix]

Log in to phpMyAdmin as root and run SHOW FULL PROCESSLIST.