Please understand a little background before getting to know the problem.
TABLE transaction ( t_id: AUTO INCREMENT BIGINT t_execute_on_date: DATE, timestamp: DATETIME, ... other columns )
transaction has 300M (three hundred million) rows and growing (1M rows daily) and growing and we need to drop old data based on the column
t_execute_on_date. I plan to drop old data (older than 2 years. approx 40M rows) on the basis of creation monthly range partitions based on KEY
t_execute_on_date. I have tested this on a test server while using the procedure suggested in @RickJames blog post PARTITION Maintenance in MySQL: create a new partitioned table, with optimised indexes according to queries and then reinserting records, and drop old partitions, and this approach seems fine to me. Any suggestions in this approach are also welcome.
I have done this in master-master replication with one master offline, reorganising data, and recovering replication lag, and then drop the old master, bring the traffic to new one, if everything is fine.
TABLE transaction_history ( th_id: AUTO INCREMENT BIGINT, t_id: FOREIGN KEY (t_id of TABLE transaction) timestamp: DATETIME, ... other columns )
This table has 1.5B+ (One billion five hundred million) rows growing 15M+ daily.
This table has related history of transaction and contains multiple rows corresponding to one
Once old transaction data from table
transaction is dropped, I would like to drop the same from this table
transaction_history as well.
This table does not have any key like
t_execute_on_date based on which I can partition.
This table has 1.5B+ rows I think that adding an additional column
t_execute_on_date is just not possible.
Like the approach discussed above would I have to manually pull data from table
transaction_history based on
t_id of TABLE transaction (reorganised ,old data dropped) and reinserting only the selected records into the new table
The PROBLEM I face is how to purge old data from table
Using MYSQL 5.7 Running on [2.8T HDD, Amazon Linux AMI 2017.03, 16 core, 63GB RAM]