High processor - what to do

Question

I have a high processor problem with MYSQL using "top" (linux) shows 90% CPU peaks.

I tried to find the source of the problem, turned on the general log and the slow query log. The slow query log did not find anything.

Db contains several small tables and one large table containing almost 100 thousand rows, Database Engine - MyIsam. the strange thing I noticed is that on a large table, select, paste very quickly, but the update takes 0.2 - 0.5 seconds.

optimization and repair are already being used and are not improving.

the table is updated frequently, can this be a source of high CPU%?

What can I do to improve this?

Answer 1

Are ganglia settings set on your MySQL server? Regular ganglion metrics along with the mysql_stats ganglia plugin can show what's happening.

Answer 2

I found mytop extremely useful.

Answer 3

First of all, could you determine which request is server overload? In this case, please insert it here, and perhaps we can give you a hand. Also pay attention to the structure of the table. Tables with many indexes are likely to have slow refresh intervals. I also recommend that you provide us with more information about the problem.

Hope this helps,

Answer 4

The first thing that comes to mind is indexing, but that doesn’t work, as your selections and inserts are fast. It usually inserts and updates, which will slow down in the "overindexed" table. This leaves the triggers ... do you have an update trigger on this table that can do a lot of work and cause a splash?

Answer 5

a request that takes 0.5 seconds will not be displayed in the upper processor 100%. Too small. Also try "show a complete list of processes"; check my.cnf and try to reduce the slow request timeout. A slow query log can catch anything that is slow enough.

Answer 6

Any update statement in this table based on the table key is slow. e.g. clients UPDATE SET CustMoney = 1 WHERE CustUID = 'someid'

CREATE TABLE IF NOT EXISTS `customers` ( `CustFullName` varchar(45) NOT NULL, `CustPassword` varchar(45) NOT NULL, `CustEmail` varchar(128) NOT NULL, `SocialNetworkId` tinyint(4) NOT NULL, `CustUID` varchar(64) character set ascii NOT NULL, `CustMoney` bigint(20) NOT NULL default '0', `LastIpAddress` varchar(45) character set ascii NOT NULL, `LastLoginTime` datetime NOT NULL default '1900-10-10 10:10:10', `SmallPicURL` varchar(120) character set ascii default '', `LargePicURL` varchar(120) character set ascii default '', `LuckyChips` int(10) unsigned NOT NULL default '0', `AccountCreationTime` datetime NOT NULL default '2009-11-11 11:11:11', `AccountStatus` tinyint(4) NOT NULL default '1', `CustLevel` int(11) NOT NULL default '0', `City` varchar(32) NOT NULL default '', `State` varchar(32) NOT NULL default '0', `Country` varchar(32) NOT NULL default '', `Zip` varchar(16) character set ascii NOT NULL, `CustExp` bigint(20) NOT NULL default '0', PRIMARY KEY (`CustUID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

Again, I'm not sure if this is causing high CPU utilization, but it seems to me that it is not normal for the update operator to take so long. (0.5 s)

The table is updated to 5 times per second at the moment, and in the future it will be updated more often.

Answer 7

What kind of server is this? I have seen slooow write and read relatively quickly on virtual machines. What does http://en.wikipedia.org/wiki/Hdparm say? What cpu / ram do you have on it? What is an accident?