MyISAM INSERT ON DUPLICATE KEY on huge table consumes 100% CPU
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
Subject
Views
Written By
Posted
MyISAM INSERT ON DUPLICATE KEY on huge table consumes 100% CPU
2134
July 16, 2015 07:50AM
811
July 16, 2015 09:39AM
1055
July 17, 2015 04:01PM
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.