MYSQL - High Server Load?

I see that my server loads which are normally around .24 are much higher recently. If I reboot the box or restart mysql it is okay for another day or two.

I am on a UNix box (dedicated). I do have one main site and another site with lower traffic. Last month my wordpress mu install on the small site was hacked and was creating lots of automatic posts. I deleted that and everything was fine.

When I run the TOP command I see that MYSQL is grabbing 80% of the CPU. How can I tell what MYSQL is doing?

Any help would be appreciated. Thanks!

 

You can see, which database is using more cpu and you may remove that domain if you wish

login to ssh

mysql -uroot -pROOTpASSWORD


mysql> show processlist;


You can see the process list there.

type exit to quit.

 

Thanks I will check it out!

 

or try this command on root

watch mysqladmin process


it will show what process is running

 

Thanks. We were able to work this out back in 2008 though.

 

[mysqld]
innodb_buffer_pool_size = 13M
max_heap_table_size = 48M
query_cache_limit = 1M
query_cache_size = 8M
query_cache_type = 1
table_cache = 80
thread_cache_size = 4
tmp_table_size = 48M
after setting above variable you can reset mysql

 

Mysql can overload the server and use most of the server CPU ion case one of the server databases are not optimized or some tabled in the dbs are crashed.
There is an option in WHM "Show MySQL Process" through which you will get which user is using high mysql resource.The same Mysql processes can be seen if you use ssh command : mysqladmin proc stat

To optimize your databse via SSH , use the following command: mysqlcheck --repair db_name


As MySQL does not have much of resource utilization control you can simply run heavy queries to get MySQL Server barely functional. The limits which exist are not really helpful as they do not define query complexity and resource consumption allowed for user. Heavy Disk IO queries can be one of the worse because they would both overload IO system and wipe off both MySQL and OS caches which can cause other users queries to perform order of magnitude slower than in normal case.

 

you can use Litespeed

 

Optimise your MySQL conf file usually located in /etc/my.cnf

You can use this MySQL tuner perl script to give you recommendations on what to edit / add in there.
https://github.com/rackerhacker/MySQLTuner-perl