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!.






27 comments:

  1. Great Article. Thank you for sharing! Really an awesome post for every one.

    IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

    ReplyDelete
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. This is one of the best website i have ever seen, we have some whatsapp alternative that has more features than orginal one.

    ReplyDelete
  9. Great Article. Thank you for sharing! Really an awesome post for every one. Also check out GBWHATSAPP PLUS

    ReplyDelete
  10. SSC Exam Result is a trending now in Bangladesh. SSC Examination was completed. All students are waiting to get their SSC Exam Result 2020. This year SSC Result 2020 will publish on 1st week in May. On the result day, after 12PM all students will get their SSC Exam Result 2020 from Bangladesh Education Board official website. Also Dakhil Result 2020 and SSC Vocational Result 2020 will publish on 6 May 2020

    ReplyDelete
  11. SSC Exam Result 2020 in Bangladesh. SSC Result coming soon. All students are waiting to get their SSC Result 2020. This year SSC Result 2020 will publish on 06 May 2020. Students can collect their SSC Result 2020 from Bangladesh Education Board official website.

    ReplyDelete
  12. HSC Examination was completed few days ago. Now HSC Result is the trending topic in Bangladesh. All candidates will check their HSC Result 2020 from Bangladesh Educational official website at educationboardresults.gov.bd.

    ReplyDelete
  13. Are you movie lover like me? If you want to download all new Hollywood and Bollywood movies. Then visit MovieRulz Website 2020. You will find here Movierulz New Link 2020. So, visit this website and watch or download all latest movies.

    ReplyDelete
  14. 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
  15. You completely match our expectation and the variety of our information.

    download notepad for windows 8

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

    average rice purity test

    ReplyDelete
  17. 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
  18. This is a topic which is near to my heart... Best wishes! Exactly where are your contact details though?
    Download GBWhatsApp

    ReplyDelete
  19. 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