CODEWITHSUNDEEP
mysqlperformancecpu-usage
Udi Kantzuker
Udi Kantzuker

Reputation: 53

High CPU - What to do

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?

Upvotes: 4

Views: 2380

Answers (7)

dylan
dylan

Reputation: 11

I found mytop extremely helpful.

Upvotes: 1

Abhilash Krishnan
Abhilash Krishnan

Reputation: 11

Does your MySQL server have ganglia setup on it? Regular ganglia metrics along with the mysql_stats plugin for ganglia might reveal what's going on.

Upvotes: 1

Quamis
Quamis

Reputation: 11077

What kind of server is this? I've seen slooow writes and relatively fast reads on virtual machines. What does http://en.wikipedia.org/wiki/Hdparm has to say? What cpu/ram you have on it? What the load avg?

Upvotes: 0

Udi Kantzuker
Udi Kantzuker

Reputation: 53

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.

Upvotes: 0

thevikas
thevikas

Reputation: 1639

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.

Upvotes: 0

Paul Sasik
Paul Sasik

Reputation: 81429

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?

Upvotes: 0

Ramon Araujo
Ramon Araujo

Reputation: 1738

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,

Upvotes: 0

Related Questions