mysql - Deleting Rows from InnoDB is very slow

Question

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?

Answer 1

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

Answer 2

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