High CPU - What to do.

Question

Hello everyone i have a high CPU problem with MYSQL using "top" ( linux ) shows cpu peaks of 90%.

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

The Db contains a few small tables and one large table that contains almost 100k rows, Database Engine is MyIsam. strange thing i have noticed that on the large table, select, insert are very fast but update takes 0.2 - 0.5 secs.

already used optimize and repair and no improvement.

the table is being updated frequently, could this be the source of the high CPU% ? What can i do to improve this?

THANKS!

Answer 1

First of all, could you define which is the query that is overloading the server? In that case, please paste it here and may be we can give you a hand on it. Also, please look at the table structure. Tables with many indexes are likely to have slow updating timespans. I also recommend you to give us more data about the problem.

Hope that helps,

Answer 2

The first thing that pops into mind is indexing but that doesn't fit since your selects and inserts are fast. It's usually inserts and updates that will slow down on an "overindexed" table. That leaves triggers... do you have an update trigger on that table that could be doing a lot of work and causing the spike?

Answer 3

a query that takes .5 secs won't showup in top cpu of 100%. Its too small. Also try "show full processlist"; verify you my.cnf and even try reducing the slow query timeout. slow query log can catch anything that is slow long enough.

Answer 4

Any update statement on that table based on the table's key is slow. for example UPDATE customers 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 im not sure that this is the cause for the high CPU Usage but it seems to me that its not normal for an update statement to take that long. ( 0.5 sec)

The table is being updated up to 5 times in a sec at the moment and in the future it will update more frequently.