Deleting huge number of records in MySQL

This is a short post about DELETE data from huge table in MySQL. Most of us experienced, deleting huge record from MySQL tables taking long time, sometimes hours to complete deleting millions of records. Also, on production servers it locks the other table operations as well. Recently, I deleted around 70 million record from a production database in less than an hour. There are multiple workarounds to do this, however I am writing about the two methods which are frequently used by me for this operation. 
  • Using intermediate table.
  • Delete data in small chunks.
Before we proceed with using any of these methods, make sure the table has required indexes on where clause and you have a copy of the table as backup.

Using intermediate table:

In this method, create a new table with similar data structure and copy only required data. Rename the original table as archive or backup table and Rename the new table as original table.








Example:

Table `tabA` has 10 million record and you need only 1 million records of last 1 month. So the model query will be as follows:

CREATE TABLE `tabA_copy` as select * from `tabA` where predicate_col > curdate() - interval 30 day;
Now the table tabA_copy will have only the required data.

Rename table tabA to tabA_archive;
Rename table tabA_copy to tabA;
This method is useful when you wanted to copy only small percentage of the huge table data. Since, moving small portion of data to new table is cheaper when compared to delete huge data.









Delete data in smaller chunks:

In this method, data will be deleted as smaller chunks. Even in case of rollback also, it is cheaper to rollback smaller data sets. I have been using the following procedure to implement this method. Actually, it is a dynamic procedure with PREPARED statement to satisfy all the requirements. For simplicity, I am writing the core part of it. 

drop procedure if exists huge_delete;
delimiter //
create procedure huge_delete()
begin
 declare rows int;
 declare rows_deleted int;
 set rows = 1;
 set rows_deleted = 10000;
 while rows > 0
 do
    delete from db.tabA where predicate_col < curdate() - interval 90 day order by `id` limit 10000;
    set rows = row_count();
    set rows_deleted = rows_deleted + row_count();
    select rows_deleted as "Rows Deleted";
 end while;
end //
delimiter ;






This method is suited when you wanted delete huge set of data (in millions) from the table. Make sure, you have index on the where clause column.

Note:

I am using innodb_flush_log_trx_commit = 2 to reduce the total response time. There are certain points to be considered before setting the innodb_flush_log_at_trx_commit as 2. Take a look at this article before you proceed with this value.


This setting runs with autocommit = 1 value which is default. In case if you run with transactions, make sure you commit the transaction at every iteration.

Caution:

If you are running on Replication master server make sure, slave is in synchronization with master. In my recent implementation, the slave is very long time behind the master. I fixed it by skipping some transactions and later used compare utility to make it sync.

I recommend you to test this steps and confirm that, it is working as you expected before using it on production server. I hope this post will help you to get the things done!.






66 comments:

  1. Replies
    1. This comment has been removed by the author.

      Delete
    2. This comment has been removed by the author.

      Delete
  2. Replies
    1. I am in need of deleting data. Thanks for your sharing on this content. See more useful programs at Mobdro pc :https://mobdroportugal.net/mobdro-para-pc/

      Delete
  3. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.

    If you want know about which is the best WhatsApp mod try this one YOWhatsApp

    ReplyDelete
  4. Mcafee.com/activate is the official site for Mcafee thing. Where you can purchase your Mcafee Product and recuperate your 25

    alphanumeric Mcafee commencement code from your retail card. What's more, moreover You can download Mcafee plan by entering your

    introduction key. You need to make a Mcafee account By using your email id and mystery word. Also, start protecting your

    contraption from contaminations, malwares and other online threats by McAfee antivirus.
    visit:
    mcafee.com/activate

    ReplyDelete
  5. Norton products is Norton VirusScan, Norton SiteAdvisor, Confirm, Norton E-Business Server, Norton Anti-Malware Engine Core. Norton Antivirus is also known as it’s high-class security properties.

    norton.com/setup

    ReplyDelete
  6. Go to Office Setup Installation File from office.com/setup, Install and update Office with peace of mind and with great security. Now you are good.if you have purchased the Office product Offline, then you can contact now.
    office.com/setup

    ReplyDelete
  7. Today most of people from all over the world are using the internet from different devices like PC, laptops or smartphones, etc. The internet has given birth to many online crimes, known as cyber-crimes, created by expert hackers. This program is designed to restrict this kind of activity.

    mcafee.com/Activate

    ReplyDelete
  8. thanks for sharing this. this problem faces to every one nowadays. because every one phone or tablets has the huge amount of data in it. then it takes much time to delete them. thanks for this post to delete it easily.
    Here is a app Trainz simulator Apk is one of the most amazing applications designed for players who love to play racing games. The simulator lets the user learn the method by which he can make his railway with full control. The game is amazingly designed with full controls feature. You can easily control the train tracks, railways, roads, designing of the plate-form and everything that you need to customize.
    trainz simulator latest 2020
    NFS most wanted apk
    hala play apk 2020

    ReplyDelete
  9. This is one of the best website i have ever seen, we have some whatsapp alternative that has more features than orginal one.

    ReplyDelete
  10. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.gb whatsapp new version

    ReplyDelete
  11. You completely match our expectation and the variety of our information.

    download notepad for windows 8

    ReplyDelete
  12. Here at this site really the fastidious material collection so that everybody can enjoy a lot.

    average rice purity test

    ReplyDelete
  13. Great web site you've got here.. It’s difficult to find quality writing like yours these days. I honestly appreciate individuals like you! Take care!!
    GBWhatsApp Latest Version

    ReplyDelete
  14. This is a topic which is near to my heart... Best wishes! Exactly where are your contact details though?
    Download GBWhatsApp

    ReplyDelete
  15. This article content is really unique and amazing.This article really helpful and explained very well.So i am really thankful to you for sharing keep it up





    Popcorn Time Apk
    Lucky Patcher Apk free
    Anonytun Pro Apk download

    ReplyDelete
  16. Here at this site really the fastidious material collection so that everybody can enjoy a lot.

    Instagram Plus Apk Download
    GBWhatsapp with features download

    ReplyDelete
  17. You completely match our expectation and the variety of our information.

    GBWhatsapp new theme download
    GBWhatsapp Downlaod for Iphone

    ReplyDelete
  18. Here at this site really the fastidious material collection so that everybody can enjoy a lot.

    Showbox apk official for PC
    Showbox Downlaod for Smart TV

    ReplyDelete
  19. You completely match our expectation and the variety of our information.

    Download Showbox for Roku
    Showbox Download for firestick

    ReplyDelete
  20. You completely match our expectation and the variety of our information.

    Showbox apk download for android
    Showbox Windows 10 Download

    ReplyDelete
  21. Here at this site really the fastidious material collection so that everybody can enjoy a lot.

    Share it PC latest PC version download
    Share it download for android

    ReplyDelete
  22. I would recommend my profile is important to me, I invite you to discuss this topic…free download gbwhatsapp latest version

    ReplyDelete
  23. Great post. I am dealing with many of these issues as well..

    GBWhatsapp 2020

    ReplyDelete
  24. 123movies. If sales are thin more spend may be required

    ReplyDelete
  25. 123movies. thin more spend may be required, but the rise in cost per

    ReplyDelete
  26. download kinemaster pro apk is a full-featured free video editing app for android users. It is very easy and simple to use. It has lots of powerful tools that help you to make a professionals video.

    ReplyDelete
  27. movies123 If sales are thin more spend may be required, but the rise in cost per acquisition should be contained, by avoiding entering a bidding war.
    Another helpful byte coming up!

    ReplyDelete
  28. Before you can install it on your phone you will need to make sure that third-party apps are allowed on your device.GBPlus.Org

    ReplyDelete
  29. Data re not getting deleted, but script is running without any error
    Please help!!

    ReplyDelete
    Replies
    1. If I am not wrong, it is due to WHERE clause. Please check the WHERE condition of DELETE query.

      Delete
  30. Thanks for sharing such a nice content. It is amazing. and also recommend for download dragon city from getapkmod.

    ReplyDelete
  31. WoW!!! Thanks for sharing this amazing idea. I love your article. kevin 32 inches smart tv review

    ReplyDelete
  32. Awesome article, WoW!!! Thanks for sharing this amazing idea i like it love you. CLICK HERE TO DOWNLOAD

    ReplyDelete
  33. https://cracksray.com/
    Such a great High PR Blog commenting Sites List you have provided here thanks to letting us know these lists. It will help us to a large extent.

    ReplyDelete
  34. Thanks for sharing this amazing article. Keep up the good work. I would love to check some more related posts on your site. Also, check out Kinemaster Diamond

    ReplyDelete