Good day community, my issue with my database is the following:
In my RDS A I have several databases but I'm only interested in migrate just one database, the biggest of them all (aprox 95GB).
The database has around 700 tables, 3000 procedures and a few triggers and functions (less of 20). Using DBForge Studio 2020 for MySQL it takes about 1.5 hours the backup and 10+ hours the restore in the RDS B (I got similar time results with mysqldump).
Both RDS have MySQL engine 8.0.23, 8 CPU and 32GB of RAM (db.m5.2xlarge), the only difference is the storage and it's the main reason of the migration, first one has 900GB and the second has 300GB and that's because I deleted some useless databases and I have so much extra space, I cannot downgrade the RDS storage and I want to save money, so, the only way is migration.
Now my question: Is there any faster and more efficient way to restore a database between 2 MySQL RDS than described in 2?
I'm open to your suggestions and gladly will test your solutions and share the results. Thank you in advance.
As suggestion of Wilson Hauck, I'm sharing additional information of my RDS B:
RDS B tables from the query
COUNT(*) FROM information_schema.tables
Well in this case, RDS B has not additional databases, only the basic system databases created by the Amazon RDS setup and that's because I want restore the heaviest database at first.
Suggestions to consider for your RDS B Parameters Group
innodb_io_capacity=500 # from 200 to use more of available IOPS based on leased 300GB
read_rnd_buffer_size=128K # from 512K to conserve RAM per connection and handler_read_rnd_next count
innodb_lru_scan_depth=100 # from 1024 to conserve 90% of RAM cycles used for function
innodb_flush_neighbors=2 # from 0 to push all rows for EXTENT in 1 sweep
innodb_buffer_pool_size=22G # from ~ 48G for ~ 70% of available 32G on B server
innodb_change_buffer_max_size=50 # from 25 percent for higher rows added per second
innodb_concurrency_tickets=20000 # for reduce reque frequency
There are more opportunities to improve performance, we have free downloadable Utility Scripts to assist with performance tuning, see profile.