0

I have some tables with millions of rows (up to 6MLN) and I need to execute a delete to reduce the amount of rows. I created a procedure with a loop that delete 10k record each execution.

What I did see is that after some executions the table starts to respond very slow and the delition no longer respond, for example:

  1. First itaration, 30k records to delete, time 35 seconds
  2. Second iteration, 126k records to delete, time 3.4 mins
  3. Third iteration, 350k records to delete, time 24 mins

Every iteration i place a commit every 10k record. So first iteration need to delete 30k records and commit every 10k records.

I see that every time i execute the procedure the table grow (and does not decrease, what i was expecting...)

I'm trying to find a good solution before this post, with no luck.

I cannot delete and re-create the table with less records, so i need to find a way to lower down the records on the table.

How i can do to delete all this records maintaining the performance? Free space, "well built indexes" etc

I can give you more details if needed

1
  • try to build a new table with only the wanted data and then drop the old table and rename the new. – nbk Nov 30 '20 at 14:43
1

For a "large" delete (more than, say, half the table), do something like:

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real WHERE ...  -- what to keep
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

The INSERT..SELECT is slow; the other steps are fast. No need to "shrink" unless the original table was created with innodb_file_per_table=OFF, in which case the disk usage will increase with this process.

More big-delete tips for more cases: http://mysql.rjweb.org/doc.php/deletebig

4
  • I think the only solution I have is to perform what you say.. also if my INSERT..SELECT need to maintain 1MLN of rows.. :( I tried the steps described in the article you link, i'm trying to delete in chunks, but i dont understand why deletion become slow and slow after efery iteration. For example after deleted 500k rows the table become very.. very slow to respond – Mistre83 Dec 1 '20 at 7:52
  • @Mistre83 - DELETE (and UPDATE, in some cases) has a lot to clean up -- the previous row is kept in case of a ROLLBACK. Indexes need to be cleaned up. Etc. Hence, it is best to try to avoid deleting millions of rows except by the tricks I list. Possibly the "slower and slower" i due to the delayed secondary index cleanup. I may have more insight if you provide both SHOW CREATE TABLE and the DELETE. (OFFSET can be terribly slow.) – Rick James Dec 1 '20 at 8:13
  • I created some stats to understand how many records i need to maintain and it's about 30/40%, so i think the best solution it's to create a new table with just the needed records. After i created the table and inserted the records (as you show in your response) i need to perform OPTIMIZE TABLE NewTable or it's unnecessary? I'm using InnoDB – Mistre83 Dec 1 '20 at 10:31
  • @Mistre83 - Unnecessary. (You could look at SHOW TABLE STATUS before and after OPTIMIZE. There may be an improvement, but the new table is likely to quickly grow to consume the apparent improvement. – Rick James Dec 1 '20 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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