Mysql CPU usuage 100%. Updates records

Hi All,

Mysql eats CPU Ususage upto 100%.
The operation going on is
read data from table1 (Around 30000 records)
Loop through the records
In loop
Check each row is present in table2 based on 2 fields. If no, insert record in table 2 else update the record in table 2

Mostly it is update operation. I have added some log information for update query started and completed.
Only two rows are updated in one second.

And checked mysql eating 100% of CPU making it very slow.

This operation is going in ajax request. When mysql cpu goes 100%, ajax returns error. However, update operation continues.

Please help me to resolve this issue.

What queries are being run when the CPU usage goes to 100%

update query…
this query is in loop.
In loop if record exists then update else insert

What indexes are in place for the tables used?

The table has columns like
ID,
Pcode,
Pname
etc

And Indexes is added for ID (Unique)
and in update where clause we have used Pcode and City

you can probably replace the very inefficient loop with an INSERT SELECT statement using ON DUPLICATE KEY UPDATE

There are some manipulations are done for some of the fields and hence loop is used for the same

manipulations are also possible with INSERT SELECT

Thanks r937. This is situation
temp table: (code, name, desc, url, city, country, imageurl,…)
Main table: (id, code, name, desc, url, city, country, etc)

temp table no indexes
main table (ID is primary key) and no other indexes.

on duplicate key update How to specify Columns - code and city as these columns do not have any indexes

Please help.

which two?

what manipulations?

also, can you do a SHOW CREATE TABLE on both temp and main tables

Check each row is present in table2 based on 2 fields.
2 Fields of main table (code and city)

There are some manipulations are done for some of the fields
Some unicode manipulations.

Table Definition

CREATE TABLE IF NOT EXISTS `temptable` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `type` varchar(50) DEFAULT NULL,
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(150) DEFAULT NULL,
  `Country` varchar(50) DEFAULT NULL,
  `City` varchar(45) DEFAULT NULL,
  `Category1` varchar(150) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `desc` text,
  `IsDelete` int(1) unsigned DEFAULT '0' COMMENT '0:Not Deleted, 1:Deleted',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Main Table Definition

CREATE TABLE IF NOT EXISTS `maintable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(50) DEFAULT NULL,
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(150) DEFAULT NULL,
  `Country` varchar(50) DEFAULT NULL
  `City` varchar(45) DEFAULT NULL,
  `Category1` varchar(150) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `url` varchar(1024) DEFAULT NULL,
  `desc` text CHARACTER SET utf8,
  `IsDelete` int(1) unsigned DEFAULT '0' COMMENT '0:Not Deleted, 1:Deleted',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

And the two fields are code and city.

insert into maintable(fields…) select (fields) from temptable where isdelete = 0 on duplicate key update – how city and code be used

ALTER TABLE maintable
  ADD UNIQUE ( code , city )
;

INSERT
  INTO maintable
     ( type
     , code
     , name
     , Country
     , City
     , Category1
     , image
     , url
     , `desc`
     , 0        -- IsDelete
     )
SELECT type
     , code
     , UNICODE(name) -- or whatever 
     , Country
     , City
     , Category1
     , image
     , url
     , `desc`
  FROM temptable    
 WHERE IsDelete = 0   
ON DUPLICATE KEY UPDATE 
       type = VALUES(type)
     , name = UNICODE(VALUES(name))  -- not sure about this
     , Country = VALUES(Country)
     , Category1 = VALUES(Category1)
     , image = VALUES(image)
     , url = VALUES(url)
     , `desc` = VALUES(`desc`) 

Thanks r937.

The above query is working fine. Thanks a lot. The query executed just in 2 seconds with 28000 records.

Can we get how many records inserted and how many records updated?

I appreciate your help.

Hi r937.

After executing this query, duplicate records gets inserted.
I checked these duplicate records and found that datetimestamp and ID fields are different.

Let me know if this is because ID field (auto-increment)

certainly better than a loop that does two rows per second

check da manual… i think mysql’s ROW_COUNT() function, or php’s affected_rows function

based on what? not code and city because you declared that combination unique, right?

yes.

There is also ID as Primary key. But that is not used in insert query.
So, I really am not sure why duplicate records gets inserted.

please help.

Sorry r937.

On local server it was running fine as I added index as you suggested.

On stage server, I was executing query without adding index. Now I have added index and it will work fine.

One query:
on duplicate key update … followed by update field1 = values(field1), field2 = values(field2)…
Do we need to specify all the fields ? field1 = values(field1), field2 = values(field2)…fieldn = values(fieldn)

Let me know.

i would suggest that you set up a couple of test tables, and try it for yourself to see what happens

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.