RDBMS/MYSQL archival of a large database with billions of records and TBs of data

Problem Statement

While working on E-commerce SAAS based product we have client installation which is having Terabytes of data growing approximately 2x yearly, having billions of records till now. Even after upgrading the DB server(AWS instance) to a fairly large instance type still, we were getting query performance issues, frequent OOMs were occurring and we did try to optimize slow queries but at last, we need to upgrade the instance(That happens many times). So, majorly our infra cost was a big concern.

The worst comes when there is an E-commerce sale and we have to upgrade our server to a pretty large AWS instance keeping in the mind we are going to have 8x-10x traffic during peak hours.

Possible solutions

• Identify slow queries — Improve them
• Infra Upgrade (Large Cost factor)
• Data archival

After doing the first two steps for a while finally, we decided to go for archival. We decided with client consent to keep the last 2 year data in the Master DB and the rest older data will go into cold storage like S3(DB backup) or a separate DB that can be accessed on-demand.

ROI & Benefits

• Infra Cost:- Data ingestion velocity is quite fast. Every year data was growing at a 2x rate.
• By deleting the data every year, we can reduce the amount of RAM/Disk required to be increased every in order to keep our DB performant.
• Also, it will save cost in term of Disk space usage as well.
• Long Term Benefit:- Long term benefit is that if do not archive the DB at all then we might end up using a very high-cost infra and maybe after 2–3 years we might hit the upper limit in recommended DB instance type e.g. Currently, we are using r4 series. r5 is recommended for in-memory DB. Long term benefit will be there if we do this exercise every year once or more frequently.

InnoDB buffer pool comparison and calculation:-

Formula for calculating the upper limit of the InnoDB buffer pool is given below. We either need to set the innodb_buffer_pool_size in GB by given formula if it is greater than your machine then 75–80% of the machine RAM size.

Archival Challenges

• Table Order/dependencies Identification/Deletion Performance:- Here, the real challenge was to make sure order of deletion of tables are in right order such that cascade delete constraints do not affect the child tables when a parent is deleted and analysing the dependencies of multiple tables was really a complex task. To solve this I have used Graph visualization d3.js applications as well as Topological sort(tsort utility) in order to find the right cascade safe order of deletion of tables.

Based on the initial estimate it would have taken more than 55–60 hrs to delete the whole set data in an archival process. So to improve this, we have applied multi-threading to delete data from multiple independent tables in parallel. After this optimisation, it took 32 hrs to run the whole deletion.

• Zero Downtime:- We have minimised the downtime to almost zero. Here are the main steps which resulted in really less downtime.

By creating a separate MYSQL COPY machine on which archival will be running.(Using Xtrabackup(percona) Utility )

Once archival(deletion of data) is over, we sync new data with PROD MYSQL(Using Mysql master -> slave replication)

Once Archival DB is in sync with PROD MYSQL then

We stop the services

Swap the DNS mapping for PROD MYSQL and ArchivalDB then start the services.

Design Decisions

Data deletion:-

Ordered & Batch deletion(Preferred Approach):- A Python script is created which calls deletion stored procedures in batches for a particular entity also in an ordered way (Identified using Table relationship analysis using Topological Sort).

• Order of deletion is started from child to parent so as to avoid cascade deletion.
• Also, for some unrelated tables, we can parallel deletion.
• Batch deletion Algorithm:- Reason to go with this approach is, deleting millions of rows from the table together can cause resource crunch in INNODB which might result in a lot of I/O ops (log files and undo logs creation)
• Example Script: This script just picks up the min and next 1000 element id and it makes sure we just scan 1000 rows only at a time. This result in better performance. Also, we can execute multiple batches of delete in parallel. (e.g. 1–1000, 1001–2000 and so on in parallel).

Table Partitioning(Not preferred approach):- Partitioning could be one of the approaches to delete the table data in an easy way but there are a lot of restrictions in case of partitioning to work.

Foreign keys not supported for partitioned InnoDB tables. Partitioned tables using the InnoDB storage engine do not support foreign keys. More specifically, this means that the following two statements are true:

No definition of an InnoDB table employing user-defined partitioning may contain foreign key references; no InnoDB table whose definition contains foreign key references may be partitioned.

No InnoDB table definition may contain a foreign key reference to a user-partitioned table; no InnoDB table with user-defined partitioning may contain columns referenced by foreign keys.

The scope of the restrictions just listed includes all tables that use the InnoDB storage engine. CREATE TABLE and ALTER TABLE statements that would result in tables violating these restrictions are not allowed.

Query cache not supported. The query cache is not supported for partitioned tables, and is automatically disabled for queries involving partitioned tables. The query cache cannot be enabled for such queries. Also, in the queries we need to include the partition key so as to take benefit of partitioning performance.

Copy table approach(Decided not to go ahead with this):- Select the rows not to be deleted into an empty table that has the same structure as the original table:

• INSERT INTO t_copy SELECT * FROM t WHERE ... ;
• Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:
• RENAME TABLE t TO t_old, t_copy TO t;
• Drop the original table:
• DROP TABLE t_old;
• The problem with this approach is in the case of multiple tables relationships this approach becomes complex to materialize.

Table Dependency Analysis:- This analysis was done using the following things:

• Finding Cascade on rules delete tables -> Helps in identifying which tables data will be deleted if we deleted from a particular table so in order to stop the ripple effect we need to delete data from child table first before “referenced_table".
• SELECT referenced_table_name, table_name FROM information_schema.referential_constraints WHERE delete_rule = 'CASCADE' and referenced_table_name='Table Name' order by referenced_table_name ;
• Finding a foreign key relationship in a table

Post Archival

• Data Validation:- You can do count based verification in order to check if the expected deleted rows count are same after the archival run is over.
• Disk De-fragmentation:- Even after deleting the data on the archival Machine disk used space will remain un-changed. So to reclaim the space. We have couple of options:
• We can take backup of the Archived DB and restore would reclaim the space. But, it might take a lot of time in case of large DB in TB(s)(As in our case)
• Better approach would be to run optimize table command separately/parallelly on the tables which were archived and it works for us.

OPTIMIZE TABLE <table_name>;

Component Design

We have used the following terms for identifying the machines used during archival.

• M1: Master Production MYSQL Machine
• S1: Replica/Slave of Master Production MYSQL Machine
• M2: Archival DB MYSQL Machine(On this MSYQL actual deletion would happen)
• S2: Replica/Slave of M2(Archival DB) MYSQL machine.

Deployment Strategies/Steps

• Our mainDB will be replicated to two DB replicaDB (One with last two year data) and archivedDB(With all the Data till now).
• We will be using two separate Machine with MYSQL which can be used for archival. After archival is done we can change it to master & SlaveDB combination and old DBS can be discarded. Detailed steps are given below.

Architecture

Script Logic

• Creating Hot Copy from master DB (Using Xtrabackup command)
• Pre-archival data creation:- You can create few tables to track the progress of archival.
• Data deletion:- You can create store procedures for deletion logic for different tables.
• Post archival data validation/verification:- You can do count based data validation.
• Data syncing from PROD after deletion till now:- You can sync the latest production data using MYSQL replication.
• Disk de-fragmentation:- You can call “optimize table” command on different tables which were archived.

Rollback Strategy

NOTE:-

• The architecture used/explained in this blog post might be specific to my use case but this will give enough information to head start your journey.
• I have omitted the details of the actual script and detailed steps because i do not want to put my specific implementation details into this blog post. Putting those details here would have introduced a lot of complexity.

References:-

1. https://dev.mysql.com/doc/refman/8.0/en/delete.html
2. https://www.percona.com/doc/percona-xtrabackup/2.4/index.html
3. http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks
4. https://dba.stackexchange.com/questions/189514/delete-from-table-in-chunks