Help with high cpu usage on updates
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?