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 22.214.171.124 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
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!