5

I have to delete about 28 million records from a InnoDB table in MySQL. I am wondering what happens if I am doing a select statement on the same table during the delete operation. I guess the delete will take some time to complete.

I am using MySQL version 5.6. I don't want to delete all records I will have a where condition or maybe having. Thank you for your time.

  • Please do not use one sql to delete more than million of recrods, that sql will cost a lot of time. If the database crash duration that process, the mysql recover will need a lot of time (may be 1 hour). The select after that delete sql will also cost a lot of time (may be 10 min) even if you only select 1000 records. I am using mysql 5.7 with inndb. You should use limit in your delete in a loop, and if it delete records number less than the limit, you can exit your loop. – bronze man Nov 9 '17 at 6:21
  • In the end I deleted them from the programing language side using a limited loop like you suggested. And we implemented a task that will run daily to remove the unused ones. – Robert Gabriel Nov 10 '17 at 1:03
  • But, I needed to delete first the 28 million once. So, in my case there wasn't a problem with locking the system for one hour. – Robert Gabriel Nov 10 '17 at 1:05
  • I found that my previous solution has a problem that if you have a long transaction (like 1 hour select) when the delete loop is running, because of the mvcc of mysql, your delete 1000 row sql will slower and slower (like 1min) and the total running time of that delete loop still cost a long time. Once that long transaction finish, everything become fast again. – bronze man Nov 13 '17 at 0:46
  • The perfect solution for me right now is use a loop select 10k primary key order by asc to be deleted and use where k> the last key in the next select, and delete those rows by primary key with mulit delete (DELETE FROM xxx where k in (?,?,?,...). This solution will not slow down by a long select transaction and This solution will not have any long recover time issues. – bronze man Nov 13 '17 at 0:49
8

Instead of deleting a large percentage of the rows you might better negate your WHERE-condition and INSERT/SELECT the remaining rows into a new table. Then DROP the original table and RENAME new to old (or TRUNCATE and re-INSERT). This way there's only a very small period (DROP/RENAME) where the table is not available. It's a very common process in a Data Warehouse where you deal with really large numbers of rows.

You need to run both the INSERT/SELECT and the DROP TABLE within a single transaction, so no other session is able to do any DML between the INSER and the DROP. I don't know the exact mySQL/InnoDb syntax and transaction behavior, but this is a skeleton (of course you need to test before):

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 
START TRANSACTION;
LOCK TABLES newTab WRITE; 
INSERT INTO newTab SELECT * FROM oldTab; 
DROP TABLE oldTab;  -- might need a COMMIT?;
RENAME newTab TO oldTab;
COMMIT; -- ?
  • Thank you. I think this is the way to go. I am using the table in all the requests and locking the table for a large period of time means app down-time. But, I have one more question. If during the move new records are added to the first table that means that I will end up with inconsistent data. – Robert Gabriel Sep 21 '15 at 12:35
  • You need a READ (or SHARED) lock for the SELECT, normally this should be the default. This will prevent new insert/updates. – dnoeth Sep 21 '15 at 12:52
  • You cannot run the DROP in the same transaction as DDL is not transactional in MySQL - it will issue an implicit commit and theoretically some other transaction might slip in there. If the table is supposed to be updated then I would suggest deleting with a limit or other way of batched deletes. – jkavalik Sep 21 '15 at 15:34
  • Suggest the DROP+RENAME be changed to: RENAME TABLE oldTab TO x, newTab TO oldTab; DROP TABLE x; That way, oldTab is always visible. – Rick James Oct 25 '15 at 20:17
  • The answer here is a good one when deleting most of a table. Other techniques for other cases are discussed in my delete blog. – Rick James Oct 25 '15 at 20:18
0

I use a method where you select 1.000 => 10.000 first, after it i delete every single one, you can delete more of same time, but what i want is not to block eny thing inside your database.

i try to remove logs from our database, we got like 450 milions ( 240gb ) of data, if i just use DELETE * FROM table WHERE date > toDate the database block.

so i resoved it create a script there select a amount of rows out and delete every single one, we use InnoDB and its take a long time, right now we can remove 1milion a /day becures we get perfomes issues.

hope you can use this method to remove all your rows you do not need enymore.

remeber if you run a script to do this, remeber to start the loop agin when its hit your row count, to get the next rows out and starting to delete it.

0

Depending on your table, your could make usage of TRUNCATE TABLE. Of course, this would delete all data from table, but this is what I understood you are planning.

However, behaviour of MySQL is controlled by so called ISOLATION LEVEL. As this is not an very easy topic, I would recommend to read documentation for your running version of MySQL. You could minimize the blocking by using READ UNCOMMITTED -- but this might will end up in inconsistent reads (surprise) so your SELECT might return already changed values (e.g. when transaction of delete needs to be rolled back)

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.