MySQL Forums
Forum List  »  InnoDB

Help with high cpu usage on updates
Posted by: Matthew Ramir
Date: April 10, 2010 12:04AM

Hi, Im running a mysql server on a dedicated quad core that is mainly used as a game server. Lately my sql server has been running at about 50% cpu usage which is a problem when i have 10 other applications that normally use about 70%. I havent had a problem with this before and have tried changing the innodb options to increase the cache and buffer sizes but nothing seems to help.
Essentially I have about 10 different connections running queries like

'update scores_slayer set assists=assists+1, assists_d=assists_d+1, assists_w=assists_w+1, betrays=betrays+0, betrays_d=betrays_d+0, betrays_w=betrays_w+0, deaths=deaths+2, deaths_d=deaths_d+2, deaths_w=deaths_w+2, suicides=suicides+0, suicides_d=suicides_d+0, suicides_w=suicides_w+0, kills=kills+1, kills_d=kills_d+1, kills_w=kills_w+1, scores=scores+1, scores_d=scores_d+1, scores_w=scores_w+1, online=online+38, online_d=online_d+38, online_w=online_w+38 where name=(select id from names where name=''{RD} sick c'')'

I currently only have indexes on name and id/name from the names table.


CREATE TABLE IF NOT EXISTS `scores_ctf` (
`name` int(11) NOT NULL,
`scores` int(11) NOT NULL DEFAULT '0',
`scores_d` int(11) NOT NULL DEFAULT '0',
etc...
`assists_d` int(11) NOT NULL DEFAULT '0',
`assists_w` int(11) NOT NULL DEFAULT '0',
`online` int(128) NOT NULL,
`online_d` int(128) NOT NULL,
`online_w` int(128) NOT NULL,
`joined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and

CREATE TABLE IF NOT EXISTS `names` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(12) NOT NULL,
`server` int(11) NOT NULL DEFAULT '0',
`common` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `server` (`server`),
KEY `common` (`common`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1075793 ;


scores_ctf currently has ~63,853 rows and names has ~76,662

does anyone have any suggestions to decrease cpu usage?

Options: ReplyQuote


Subject
Views
Written By
Posted
Help with high cpu usage on updates
2284
April 10, 2010 12:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.