Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

To remove duplicate rows in a table I did the following (about 200k rows)

table1:

id_a int
id_b int
val_a int
val_b int
pk (id_a, id_b), index id_a, index id_b

Algorithm:

created table2 as select from table1;
drop table1;
created table1 as select from table2 (with removed dublicates);
created indexes for table1;
drop table2 ;

After that, mysqld started to use 40-140% CPU at VPS with any query that uses that table (0% idle). Strange, that CPU usage increased even if I query another tables, that were not changed. It all started after those changes.

SHOW PROCESSLIST shows only "sleep" and "show processlist".

I've tried to run mysqlcheck and tried to restart mysql-server - no errors are reported.

I enabled slow query logs, and they appeared there. Even those, which ran fast one day ago.

What could be wrong and what else can I do to diagnose the cause?

share|improve this question

migrated from stackoverflow.com Sep 5 '11 at 23:26

This question came from our site for professional and enthusiast programmers.

1  
What storage engine you're using (InnoDB, MySQL), if InnoDB are you using innodb_file_per_table option? Give as more information. –  ludesign Sep 5 '11 at 23:22
    
It could be that loading the data into a new non-temporary table filled up your database on disk and then once you removed it you've left mysqld with space to garbage collect and reuse, and that's what's taking the CPU? Why not instead build t2 as a temporary table of the duplicates and then use that to delete rows from t1 in-place without destroying and rebuilding the table? –  Rup Sep 5 '11 at 23:23
    
MyISAM engine. Of course, I could do that with tmp tables, but that's not a problem. The problem is how to solve that issue. I thought about garbage collector, but idle 0% and I restarted service few times. Also I tried to use another database from original dump - same problem. After 8 hours it started to increase only to 15-40%, but it still isn't normal. –  Wayne Sep 6 '11 at 5:03

1 Answer 1

First thing I noticed is that you have a redundant index.

This is your original table1:

id_a int
id_b int
val_a int
val_b int
pk (id_a, id_b), index id_a, index id_b

You do not need the index on id_a since it is already the first column of the primary key. That's 200k rows less of index entries to process. Just run this to get rid of it:

ALTER TABLE table1 DROP INDEX id_a;

Another step you will need is to reload the table and disable non-unique indexes while reloading (This is MyISAM only). In addition, you need a cleaner algorithm. I have two algorithms. Here is the first one:

ALGORITHM #1

CREATE TABLE table2 LIKE table1;
ALTER TABLE table2 DISABLE KEYS;
INSERT INTO table2 SELECT * FROM table1;
ALTER TABLE table2 ENABLE KEYS;
ALTER TABLE table1 RENAME table0;
ALTER TABLE table2 RENAME table1;
DROP TABLE table0;

Since DISABLE KEYS leave the primary key open for updating, the primary key is loaded during the INSERT INTO table2. The ENABLE KEYS step rebuilds the id_b index.

Now, here is the second algorithm:

ALGORITHM #2

OPTIMIZE TABLE table1;

A whole lot shorter, isn't it ? The second algorithm (using OPTIMIZE TABLE) does everything the first did plus recompute index statistics, do internal table repairs, and index reorganization.

UPDATE 2011-09-07 17:38 EDT

The reason these two algorithms should improve things is the fact MySQL indexes are now processed linearly.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.