I have a MySQL (Aurora 5.7) table in data base which has
- 700 Million rows
- 9 Indexes
- 25 Columns
- 100 Million daily updates
- and 5 Million daily inserts
Overall this table is very busy and now we need to run some extracts on this data base to generate reports .
The issue is not only this but the column involved in Extract is not indexed so it takes 5 to 6 hours to perform extracts.
Extracts is just basically we get counts based on dates which is not indexed and we can not create indexes as this can impact application .
As we don't have index query goes for full table scan and impacts the Aurora cluster and breaks the Global Data base replication .
Even though we run this query in DR reader nodes it impacts the cluster and AWS Supports and Experts also has confirmed this .
So we dont have option to run extracts on main table and this is where we are exploring other options .
Option 1 :
Take daily snapshot restore and run daily report in new cluster. This option looks very good but the issue is cost we have 18 TB data base and my extras daily will run for 10 to 15 hours because 21 such reports has to be generated so monthly cost will be close to $ 7 k
Option 2 :
Takes Daily snapshot and extras only required tables for report to s3 and then run Athena query .
But the issue is export to S3 is taking long time and cost again is very very high because even though we export few tables from Snapshot we are getting charged for complete snapshot ,18 TB export which is again 6 K $ per month .
Have trigger on this bust table and inserts all the inserts and updates to new table and then run report on new table.
I need help to decide which is best option and if we go with Trigger option then there will 100 millions triggers daily and this many trigger can impacts data base ?
I have also looked at using DMS on this table and moving data to another table but that would be continuous replication which again can impact Cluster .
NOTE : Sorry to say but we can not afford any downtime for the application
Please suggest .