Dynamic LIMIT based on query time

Question

I'm writing scripts in Python for cleaning up a database. It mainly is finding and deleting billions of rows of data based on some logic. I have it running in a loop with a LIMIT that will run until there are no longer any rows affected by a DELETE. It is running on a production database that gets different load during different times of the day, so the time to do these DELETE queries varies based on the load. I don't want to lock up any tables by running a large DELETE, so I've been trying to benchmark the query times for different LIMITs. I found these times varied a lot based on the time of the day, so I started looking into making a dynamic LIMIT that increases or decreases based on the query time. It will increase or decrease between a floor and ceiling based on the the last query time. So the basic logic is

if QUERY_TIME < DECREASE_TIME and LIMIT < MAX_LIMIT:
  LIMIT = LIMIT * 10
elif QUERY_TIME > MAXIMUM_TIME and LIMIT < MIN_LIMIT:
  LIMIT = LIMIT / 10

My question is if this is a good strategy? I'm still learning a lot of optimizations for database work, so I'm not sure if this is a common strategy or what pitfalls I may encounter.

Answer 1

There may be a problem with that design. As you search for the next batch to delete, you may be skipping over previously checked rows. So, each subsequent delete will be slower. The time to finish is (sort of) quadratic.

Two better techniques:

• If there are only a "small" number of rows to keep, then build a new table with just them; then swap tables. Time: linear. Advantage: The result is already defragmented.
• Walk through the table 1K rows at a time. Use the PRIMARY KEY for tracking where you are. And remember where you left off. Time: linear.

Both of those techniques (and others) are discussed here.