MyISAM INSERT ON DUPLICATE KEY on huge table consumes 100% CPU

[Timothy Mifsud]

Hi,

I have a server running Windows Server 2008 R2 and has 8GB RAM. I am running MySQL 5.1.xx.

I have the following MyISAM table:

CREATE TABLE `main`.`p` (
`ID1` int(10) unsigned NOT NULL,
`ID2` bit(1) NOT NULL,
`ID3` varchar(3) NOT NULL DEFAULT '',
`Count1` mediumint(8) unsigned NOT NULL,
`Count2` mediumint(8) unsigned NOT NULL,
`Count3` int(10) unsigned NOT NULL,
UNIQUE KEY `IX_Key` (`ID1`,`ID2`,`ID3`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This table contains about 128 million rows.

I am performing the following INSERT statement on this table.

INSERT INTO p
SELECT * FROM p_temp ON DUPLICATE KEY
UPDATE
p.Count1 = p.Count1 + p_temp.Count1,
p.Count2 = p.Count2 + p_temp.Count2,
p.Count3 = p.Count3 + p_temp.Count3;

p_temp is the same exact structure of p and has about 250,000 rows. The problem I am encountering is that I am hitting a 100% CPU usage when this INSERT is being executed (and it obviously takes quite some time - around 30+ minutes)

Is there any way I can prevent this high CPU usage? I do not mind it taking some time if the CPU usage can be brought down.

Thanks in advance,
Tim