9

I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month.

If I do DELETE FROM NOTIFICATION WHERE CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day);, all the IOPS will be taken, the process will take hours, and a lot of new entries cannot be inserted due to "Lock wait timeout exceeded; try restarting transaction".

I was trying to do the way describe in here:http://mysql.rjweb.org/doc.php/deletebig However, I am using UUID instead of increment ID.

What is the correct and efficient way to delete those rows while not affect new data being insert/update?

  • You are correct ypercube, I have rectify it. Thanks for pointing out! – Tianyi Cong Nov 20 '14 at 18:06
  • Deleting records in smaller chunks, does not impact the insert operation, I tried it with loop, and it finishes deleting 70 million record in less than an hour rathishkumar.in/2017/12/… – Rathish Dec 6 '17 at 8:13
11

Make a temp table, switch it in and out, and copy the last 30 days data into it.

#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data 
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

In your off hours, drop the old table

DROP TABLE NOTIFICATION_OLD;

Here are the Advantages to doing DELETEs like this

  1. NOTIFICATION is emptied fast by means switching in an empty table.
  2. NOTIFICATION is immediately available for new INSERTs
  3. The remaining 30 days are added back into NOTIFICATION while new INSERTs can take place.
  4. Dropping the old version of NOTIFICATION does not interfere with new INSERTs
  5. NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)

Give it a Try !!!

| improve this answer | |
  • Thanks the reply Rolando! How does MySql internally handle drop table? First delete all the columns then remove table or something else? Will it take much shorter time than delete those cloumns? – Tianyi Cong Nov 19 '14 at 22:43
  • I am applying this strategy on staging, does rename command should start with "RENAME TABLE"? – Tianyi Cong Nov 20 '14 at 18:12
  • would there be any difference if i use it inside a transaction block, keeping in mind the lock wait timeout,also would it have any cons if my actual table is real huge which is now renamed and has to be dropped – Muhammad Omer Aslam May 20 at 3:08
  • 1
    @MuhammadOmerAslam in that case, the ibdata1 file (system tablespace) would just grow in the undo logs. This is especially the case when ibdata1 would start getting bigger in filesize (See my old post dba.stackexchange.com/questions/40730/…). You need to exercise more care by using pt-archiver as mentioned in akuzminsky's answer, It could be throttled to delete rows in chunks and then use pt-online-schema-change to run ALTER TABLE ENGINE=InnoDB to shrink the table. – RolandoMySQLDBA May 20 at 13:41
  • 1
    @MuhammadOmerAslam The above approach would be perfect when you have scheduled downtime. Following the link in akuzminsky's answer (percona.com/doc/percona-toolkit/LATEST/pt-archiver.html). You can use pt-archiver to archive data or just delete data without archiving. – RolandoMySQLDBA May 20 at 16:37
3

My favorite is pt-archiver from Percona Toolkit. It takes care of MySQL load, replication lag.

| improve this answer | |
  • Thanks the reply akuzminsky! I will look into it. I used to give Percona a try when I wanted to alter this notification table with pt-online-schema-change. However, it required SUPER privilege to make the change, which is not provided by RDS. BTW do you know any good way to alter huge table? – Tianyi Cong Nov 19 '14 at 22:47
  • @TianyiCong you have a new question: please ask it as a new question and perhaps comment here with a link, don't ask it in comments that's not how this site works. – Jack Douglas Nov 20 '14 at 18:30
-2

create table notification_temp as select * from notification where CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day) ;

drop table notification;

RENAME notification_temp TO NOTIFICATION;

| improve this answer | |
  • And this won't affect new data being inserted/updated? I don't think so. – Colin 't Hart Nov 20 '14 at 11:04
  • This method has 2 problems 1) makes NOTIFICATION unavailable for the duration of the DROP TABLE. 2) INSERTs that occur during the CREATE TABLE are missed. – RolandoMySQLDBA Nov 20 '14 at 11:47
  • Another problem: it should say RENAME TABLE notification_temp ... – RolandoMySQLDBA Nov 20 '14 at 18:14

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.