1

I'm having trouble with a DELETE hanging, even for a small number of rows (10) in a large MariaDB InnoDB table.

Background

I had a large table (~400,000,000 rows) that needed to have rows archived and removed. After removing about half of the rows, my archive process started to hang on the DELETE statement. I took the time to run the (painfully slow) 11-hour OPTIMIZE (ALTER for InnoDB) query to reclaim disk space and hopefully unblock the DELETE process. This worked until I deleted about half of the remaining rows (down to about 106m out of 200m rows). I figure I could keep running OPTIMIZE on my giant table to let me remove another 1/2 of the rows, but there must be a better way...

Note that the table does get a lot of INSERT/UPDATE traffic, 1000s of changes per minute.

Archive Process

To simplify the archive script, I put the whole process into a stored procedure. The steps in that procedure are:

  1. Flatten and copy up to N rows from the live DB to the archive DB, where the rows are older than 1 month
  2. Make a list of archived IDs that are probably in the live DB. Store these in a temporary table A
  3. Filter temp table A to only include row IDs that are actually in the live DB. Store these in a temporary table B

  4. DELETE FROM live_db.test_table WHERE test_id IN (SELECT * FROM temp_table_B) ORDER BY test_id ASC LIMIT N;

Now that the table is half empty, step 4 is hanging, even for N = 10 Before the table was half-empty, it worked fine and N = 1000 took ~ 1 sec

Diagnostics

Adding an EXPLAIN in the stored procedure lets me know MySQL is planning on using the PRIMARY index for the DELETE, as I hoped it would:

id select_type         table          type   possible_keys  key      key_len  ref   rows     Extra
1  PRIMARY             test_runs      index  NULL           PRIMARY  4        NULL  10       Using where
8  DEPENDENT SUBQUERY  tests_to_drop  ALL    NULL           NULL     NULL     NULL  1        Using where

Output selected from SHOW ENGINE INNODB STATUS (some things redacted):

---TRANSACTION 6457899980, ACTIVE 14 sec starting index read, thread declared inside InnoDB 3008
mysql tables in use 2, locked 2
242717 lock struct(s), heap size 25507368, 5532268 row lock(s)
MySQL thread id 1834147, OS thread handle 0x7f0f68c4a700, query id 204328569 1.2.3.4 user123 Sending data
DELETE FROM live_db.test_table
WHERE test_id IN (SELECT * FROM temp_table_B)
ORDER BY test_id ASC
LIMIT 10
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC

(From Comment)

CREATE  TEMPORARY TABLE temp_table_B (
        test_id int(10) unsigned NOT NULL DEFAULT '0'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  • How many rows are you attempting to delete at a time? Is this chunked up or a full delete and, if chunked, how many rows are you doing in a chunk? – indiri Feb 14 '18 at 19:02
  • I'm hoping to delete at least 1000 at once, but, as mentioned in the question, even deleting 10 rows hangs. – dutchscout Feb 14 '18 at 19:08
  • By 'hang' I mean it takes a very long time to complete. In my most recent check, it took 25 minutes to delete 10 rows. – dutchscout Feb 14 '18 at 19:10
  • 1
    Using JOIN, suggested by ypercube, was also helpful in speeding up deletions substantially. It looks like the 'subquery' needed for IN (SELECT * FROM temp_table) is pretty expensive, while JOIN becomes a 'SIMPLE' query. – dutchscout Feb 16 '18 at 14:44
  • 1
    Yes, IN (SELECT ...) is an anti-pattern in MySQL. At least in older versions, it often leads to poor execution plans. – ypercubeᵀᴹ Feb 16 '18 at 14:57
1

I found out what's going on here:

Because of how I was choosing test_id values in steps 1 and 2, temp_table B would sometimes be empty. (see question description for step/table context)

Then, with an empty temp_table_B, I was running step 4:

DELETE FROM live_db.test_table WHERE test_id
    IN (SELECT * FROM temp_table_B) ORDER BY test_id ASC LIMIT N;

which gave

DELETE FROM live_db.test_table WHERE test_id
    IN ( <empty> ) ORDER BY test_id ASC LIMIT N;

which would hang for quite a while.

Based on this answer: https://dba.stackexchange.com/a/86103/144766 , it seems like that empty table was probably compared against each of my 106 million rows, making the do-nothing DELETE take 25 minutes.

Now that I have modified my process to detect the empty table and abort before calling DELETE, I can manually watch for the (data-specific) condition that's causing my list-of-rows-to-delete to occasionally become empty.

EDIT: Even after fixing the empty-in-list problem, my query was greatly sped up by switching from the " IN (SELECT * temp_table) " version to a JOIN version, similar to what is suggested in comments by ypercube above. This modification eliminates the 'SUBQUERY' from the execution plan and the query becomes a 'SIMPLE' query:

'IN' version:
id select_type         table           type  key     key_len rows Extra
1  PRIMARY             test_runs       index PRIMARY 4       10   Using where
8  DEPENDENT SUBQUERY  temp_table_B    ALL   NULL    NULL    10   Using where

'JOIN' version:
id  select_type  table        type   key     key_len ref                  rows Extra
1   SIMPLE       temp_table_B ALL    NULL    NULL    NULL                 10   Using where
1   SIMPLE       test_runs    eq_ref PRIMARY 4       temp_table_B.test_id 1 

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.