Slow Updates , High CPU 100%
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.
this is the table structure:
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;
Any update statement on that table based on the table's key is slow. for
example:
UPDATE customers SET CustMoney = 1 WHERE CustUID = 'someid'
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 3 times in a sec at the moment and in the future it will update even more frequently.
What can i do to improve this?