MySQL bulk deletions

deleteinsertMySQL

I am currently looking at how to most efficiently delete rows from a table in bulk. The database I'm using is MySQL with innodb.

The table I want to delete from can be any size, but will usually be ~10gb, and has a primary key (int) and indexed hash. This hash is non-unique, and I do drop it before proceeding with the deletes. The table has no other indexes.

I have a second table containing only the id's I wish to delete. The number of id's to delete can be anything from none, to everything.

The problem is I can't decide on a strategy on how to delete these rows quickly. I am aware of 2, which serve opposite sides of the extreme.

Option 1: The standard DELETE FROM table_data WHERE id IN table_todelete. This is good if there are very few deletions.

Option 2: Create a new table. Insert rows from table_data where id NOT in table_todelete. Drop table_data and rename the newly created table. This is obviously better when there are a large number of deletes.

I do not know enough about the performance implications of select, insert, delete, to know how fast they are compared to each other.
My question is, since my data isn't skewed towards always having many or few deletes, which approach is better in terms of performance? Is there some sort of theoretical threshold at which I can swap between the two approaches? If there is a third option I might've not considered, please let me know.

Thanks.

Best Answer

You pretty much answered your own question. Creating a new table will be more efficient when you want to delete a large portion of the table. Just counting the number of records to modify will give you a general idea of the impact. However, you also want to account for time spent updating indexes. If you are deleting a lot of records, you may be concerned about slack space in the table, meaning that each block is only partially filled. Considering all of this together, if you need to delete more than about 30% of a table, then creating a new table would be faster.

On the down-side, creating a second copy of the table will use more disk space, even if only temporarily. It will disrupt database replication, so it is generally not a good option when replication is involved.

If the goal is to minimize impact on the database, then I recommend pt-archiver, from the Percona toolkit. It deletes records in batches and keeps commits to a reasonable size. It's a good option if you want to automate your purge to run on a daily basis.

Related Solutions

Sql-server – Inserting data to a table which is being cleaned up by SQL job at the same time

If CreatedOn is not the clustered index (and especially if it is not indexed at all), and you are inserting new data sequentially (e.g. new rows have a newer CreatedOn), you should consider making that the clustered index. Then you shouldn't have any contention when you are deleting old data and inserting new data, since it will be on a completely different set of pages. Unless for some reason your inserts or the delete are escalating locks. If that is the case then instead of one big transaction:

DELETE [massive amount of rows]
  WHERE CreatedOn < [somedate];

You should consider breaking that up into chunks, as you suggested; but, just doing that in a loop and not adding any other changes won't have that much of an effect, since that may still be operating as a single transaction (and your problem might be exasperated by transaction log writes, and perhaps poor autogrowth settings for the transaction log). My method is typically as follows (with no "outer" transaction at play) - commit each set in its own transaction, and checkpoint or backup the log in between transactions. This lets user queries get in between your transactions and also reduces the impact on the log. Note that I picked 1000 arbitrarily; that might not be the right number for your scenario, but I'm pretty sure 10 is not.

DECLARE @somedate DATE = DATEADD(DAY, -1, GETUTCDATE());

BEGIN TRANSACTION;

SELECT 1;

WHILE @@ROWCOUNT > 0
BEGIN
  COMMIT TRANSACTION;

  -- if in simple recovery: CHECKPOINT;
  -- otherwise: BACKUP LOG ...;

  BEGIN TRANSACTION;

  DELETE TOP (1000) dbo.MyTable
    WHERE CreatedOn < @SomeDate;
END

But again, this will help your process most if CreatedOn is clustered, or at least indexed (having no context I don't know if your overall workload would be better off with that as clustered, but I do know that this query will work much better if it is).

Postgresql – Most efficient way of bulk deleting rows from postgres

Your second option is far cleaner and will perform well enough to make that worth it. Your alternative is to build gigantic queries which will be quite a pain to plan and execute. In general you are going to be better off letting PostgreSQL do the work here. In general, I have found updates on tens of thousands of rows in the manner you are describing to perform adequately, but there is one important thing to avoid doing.

The way to do it is to use a select and a join in your delete.

DELETE FROM foo WHERE id IN (select id from rows_to_delete);

Under no circumstances should you as follows with a large table:

DELETE FROM foo WHERE id NOT IN (select id from rows_to_keep);

This will usually cause a nested loop antijoin which will make performance rather problematic. If you end up having to go that route, do this instead:

DELETE FROM foo 
WHERE id IN (select id from foo f 
          LEFT JOIN rows_to_keep d on f.id = d.id
              WHERE d.id IS NULL);

PostgreSQL is usually pretty good at avoiding bad plans but there are still cases involving outer joins which can make a big difference between good and bad plans.

This is wandering a little more far afield, but I figure it is worth mentioning because of how easy it is to go from the IN to NOT IN and watch query performance tank.

Best Answer

Related Solutions

Sql-server – Inserting data to a table which is being cleaned up by SQL job at the same time

Postgresql – Most efficient way of bulk deleting rows from postgres

Related Question