I'm having trouble with a DELETE hanging, even for a small number of rows (10) in a large MariaDB InnoDB table.
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.
To simplify the archive script, I put the whole process into a stored procedure. The steps in that procedure are:
- Flatten and copy up to N rows from the live DB to the archive DB, where the rows are older than 1 month
- Make a list of archived IDs that are probably in the live DB. Store these in a temporary table A
Filter temp table A to only include row IDs that are actually in the live DB. Store these in a temporary table B
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
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 220.127.116.11 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
CREATE TEMPORARY TABLE temp_table_B ( test_id int(10) unsigned NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1