CODEWITHSUNDEEP
mysqlsqlinnodb
user1938509
user1938509

Reputation: 455

mysql - Deleting Rows from InnoDB is very slow

I got a mysql database with approx. 1 TB of data. Table fuelinjection_stroke has apprx. 1.000.000.000 rows. DBID is the primary key that is automatically incremented by one with each insert.

I am trying to delete the first 1.000.000 rows using a very simple statement:

Delete from fuelinjection_stroke where DBID < 1000000;

This query is takeing very long (>24h) on my dedicated 8core Xeon Server (32 GB Memory, SAS Storage).

Any idea whether the process can be sped up?

Upvotes: 26

Views: 47830

Answers (7)

user6850438
user6850438

Reputation: 9

For such long tables, I'd rather use MYISAM, specially if there is not a lot of transactions needed.

Upvotes: -1

Uriil
Uriil

Reputation: 12618

I believe that you table becomes locked. I've faced same problem and find out that can delete 10k records pretty fast. So you might want to write simple script/program which will delete records by chunks.

   DELETE FROM fuelinjection_stroke WHERE DBID < 1000000 LIMIT 10000;

And keep executing it until it deletes everything

Upvotes: 35

user5883982
user5883982

Reputation: 53

I was having the same problem, and my table has several indices that I didn't want to have to drop and recreate. So I did the following:

create table keepers
select * from origTable where {clause to retrieve rows to preserve};
truncate table origTable;
insert into origTable null,keepers.col2,...keepers.col(last) from keepers;
drop table keepers;

About 2.2 million rows were processed in about 3 minutes.

Upvotes: 5

jmail
jmail

Reputation: 6134

Are you space deprived? Is down time impossible?

If not, you could fit in a new INT column length 1 and default it to 1 for "active" (or whatever your terminology is) and 0 for "inactive". Actually, you could use 0 through 9 as 10 different states if necessary.

Adding this new column will take a looooooooong time, but once it's over, your UPDATEs should be lightning fast as long as you do it off the PRIMARY (as you do with your DELETE) and you don't index this new column.

The reason why InnoDB takes so long to DELETE on such a massive table as yours is because of the cluster index. It physically orders your table based upon your PRIMARY (or first UNIQUE it finds...or whatever it feels like if it can't find PRIMARY or UNIQUE), so when you pull out one row, it now reorders your ENTIRE table physically on the disk for speed and defragmentation. So it's not the DELETE that's taking so long. It's the physical reordering after that row is removed.

When you create a new INT column with a default value, the space will be filled, so when you UPDATE it, there's no need for physical reordering across your huge table.

I'm not sure exactly what your schema is exactly, but using a column for a row's state is much faster than DELETEing; however, it will take more space.

Try setting values:

innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT (for non-windows machine)
innodb_buffer_pool_size=25GB (currently it is close to 21GB)
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0...1000 (try different values, beginning with 200)

References:

MySQL docs for description of different variables.

MySQL Server Setting Tuning

MySQL Performance Optimization basics

http://bugs.mysql.com/bug.php?id=28382

Upvotes: 7

borjab
borjab

Reputation: 11655

Your database may be checking for records that need to be modified in a foreign key (cascades, delete).

But I-Conica answer is a good point(+1). The process of deleting a single record and updating a lot of indexes during done 100000 times is inefficient. Just drop the index, delete all records and create it again.

And of course, check if there is any kind of lock in the database. One user or application can lock a record or table and your query will be waiting until the user release the resource or it reachs a timeout. One way to check if your database is doing real work or just waiting is lauch the query from a connection that sets the --innodb_lock_wait_timeout parameter to a few seconds. If it fails at least you know that the query is OK and that you need to find and realease that lock. Examples of locks are Select * from XXX For update and uncommited transactions.

Upvotes: 0

i-CONICA
i-CONICA

Reputation: 2379

What indexes do you have?

I think your issue is that the delete is rebuilding the index on every iteration.

I'd delete the indexes if any, do the delete, then re-add the indexes. It'll be far faster, (I think).

Upvotes: 6

AK47
AK47

Reputation: 3797

I don't know exact ans for ur que. But writing another way to delete those rows, pls try this.

delete from fuelinjection_stroke where DBID in
(
    select top 1000000 DBID  from fuelinjection_stroke 
    order by DBID asc
)

Upvotes: -8

Related Questions