MySQL Forums
Forum List  »  Performance

MyISAM INSERT ON DUPLICATE KEY on huge table consumes 100% CPU
Posted by: Timothy Mifsud
Date: July 16, 2015 07:50AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
MyISAM INSERT ON DUPLICATE KEY on huge table consumes 100% CPU
2025
July 16, 2015 07:50AM


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.