CODEWITHSUNDEEP
mysqlamazon-rdsdatabase-migrationdbforge
Gutierrez
Gutierrez

Reputation: 157

Single MySQL Database Migration Between Amazon RDS With Same Specifications

Good day community, my issue with my database is the following:

  1. 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).

  2. 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).

  3. 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:

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.

Global Variables RDS B

Upvotes: 1

Views: 135

Answers (1)

Wilson Hauck
Wilson Hauck

Reputation: 2343

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.

Upvotes: 1

Related Questions