1

I'm using MySQL version 5.5.45 on Windows Server 2016. I'm executing the following statement in MySQL Workbench version 6.3.

delete FROM childattendance WHERE date < DATE_SUB(NOW(),INTERVAL 2 YEAR) limit 100000;

Essentially I'm deleting any records older than 2 years. I'm not worried about which records get deleted as long as they're older than 2 years. I actually want to delete all records older than 2 years but I'm limiting to 100,000 rows at a time so that the statement actually finishes before losing connection.

The weird thing is that sometimes this statement times out, and other times it executes within a few seconds.

See this screenshot for the result, running the same query five times:

enter image description here

The first time it ran for 11 seconds before losing connection (I don't know what's causing that either) and the second time I ran it and it executed successfully in 2 seconds. Ten minutes later I ran it again, failing twice and succeeding once in between.

I've run the same statement limiting to 30000 rows and had the same issue. Sometimes it goes for 11 seconds and times out. Other times it executes in 2 seconds. And I've seen the same thing with other queries.

Coincidentally mysqldump also loses connection to the server when trying to backup this very same table, although it lasts 13 seconds before dying, and it happens 100% of the time. I don't know if that's related.

This table has approximately 6.4 million rows in 471 MB.

What causes this?

7
  • 2
    Your request (LIMIT without ORDER BY) does not make sense - it deletes random records from the list of records matched WHERE condition. Sometimes it goes for 11 seconds and times out. Other times it executes in 2 seconds. I think it can be an effect of block joining. When delete, fill factor of table/index block decreases. When its value became lower than some preset level the neighboring blocks are joined. This process needed in a lot of disk operations which are slow. – Akina Jun 9 '18 at 5:10
  • @Akina I don't understand your comment about "block joining". can you rephrase please and help me understand what I can do to mitigate this. Thank you. – Vincent Jun 9 '18 at 21:02
  • Just a suggestion... Could you copy all records that are less than 2 years, drop the entire table and dump the copied records back in? – LJ01 Jun 9 '18 at 21:29
  • That would be over 4 million records. I'm not sure how I would go about making a copy of that many rows. mysqldump fails every time on this table. – Vincent Jun 9 '18 at 22:15
  • Lost connection to MySQL server during query is almost always cause for alarm, because it suggests (though does not prove) that the query is actually causing the server to "exit" (crash) and restart. Review the MySQL Server's error log before doing anything else. If mysqldump fails, too, that's also a very bad sign. Neither of these things are normal or expected. Find and repair the problem. The performance difference is uninteresting and not meaningful until then. – Michael - sqlbot Jun 9 '18 at 22:35
1

This Answer has a mixture of explanation ("why" slow versus fast), speedup (how to be more efficient), and non-blocking (how to avoid the trouble with mysqldump).

  • If few rows are that old, the main effort is scanning the entire table. This can be helped significantly via INDEX(date)
  • If more than 100K rows are that old, then the main effort is saving 100K rows in case of a undo (rollback). This can be quite costly. Don't do more than 1K at a time.
  • If the count is somewhere in between, then there is both the table scan and some of the undo effort. Note that it may find rows to delete early in the scan, or late -- thereby making this take an unpredictable amount of time.

While the lengthy DELETE is running, other things may be adversely affected -- such as timeouts in mysqldump. So, the goal is to keep the DELETE down to only a few seconds, both by speeding it up and by decreasing how much you bite off at a time.

My Big Delete blog explains several ways to significantly improve your task.

Note that one of my techniques focuses on what to do if you are deleting more than half the table. This involves copying over the rows to keep. (See the details on RENAME TABLE, etc)

3
  • Thanks Rick. I'll take a closer look at your blog tomorrow. As for your suggestion to not do more than 1K at a time, that's just not an option when you have 2 million rows to delete. – Vincent Jun 11 '18 at 3:38
  • 1
    @Vincent - Plan A: do 100K at a time and live with the consequences; Plan B: write a stored proc that has a loop doing 1K at a time until finished. – Rick James Jun 11 '18 at 6:18
  • @Vincent - Also, you may have noticed that the second 100K ran slower than the first? If so, things may get slower and slower. That's where my technique of using the PK to "pick up where you left off" rather than scanning from the start will benefit performance a lot. – Rick James Jun 11 '18 at 6:19

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.