Options for archiving old MySQL 5.7data

Question

I have an web application that has a 1TB MySQL 5.7 production database.

We are running into performance issues due to table data size. We are planning to archive old data from different tables. Checking for best options.

Requirement

Users do CRUD operations every day with minimal data so we plan to remove old data (1 year back data) to separate archive DB server and remove the old data from the production DB, which will make the users CRUD operations fast.

Challenge

Production DB to archive separate DB sync will happen every day. Production DB to separate Archive DB server sync only Create, Updated data should sync. Where users use separate archive DB server when required to see all data.

Answer 1

You need not move the data into another server just for performance improvement.

You could just move the data into another table (named sometable_20201023, assuming the archive/purge is happening on 2020/10/23 and you are OK with such a naming convention) and delete from the existing table. Since the original table is small, performance should improve.

Performance takes a hit when active data set does not fit in memory and disk I/Os are triggered. Having inactive table(s) in your server will not cause any significant performance issue - your data dictionary size may go up by a bit.

In case you are concerned that your back-up/recovery time is also going up and want to fix it, this approach is not the best way. Moving the data to another server is best.