1

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:

  • 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.

  • RDS B Global Variables (on pastebin link below):

Global Variables RDS B

Gutierrez
  • 157
  • 1
  • 14
  • 1
    Additional information request, FROM your RDS B, please. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; for server workload tuning analysis to provide suggestions for reducing RESTORE time required. – Wilson Hauck Nov 04 '21 at 01:35
  • 1
    Ok Wilson, I will update some of the information you suggested, thanks. – Gutierrez Nov 04 '21 at 16:39
  • 1
    Information updated, please let me know if you need more. Good day. – Gutierrez Nov 04 '21 at 22:00
  • 1
    Please post ALL the data requested, even if results are zero and post TEXT results to pastebin.com to avoid space limitation that may be bugging you being unable to store all the data requested. How can you have a 42G innodb_buffer_pool_size when you only have 32G of RAM? Do you have any SSD or NVME storage used by your RDS B instance? – Wilson Hauck Nov 05 '21 at 12:08
  • 1
    Most of the values a default values, as I said above it's a new RDS, according your comment, which value do you recommend me to set on innodb_buffer_pool_size? Anyway, I will post all variables on pastebin and update the information. – Gutierrez Nov 05 '21 at 13:09
  • 1
    Is your RDS dedicated or shared at AWS? The MODEL/TYPE at RDS? Can you share URL of site to be tuned? – Wilson Hauck Nov 05 '21 at 13:19
  • 1
    The RDS is db.m5.2xlarge in us-east-1b and I updated all the variables on pastebin in the question, some of them are blank according the query result – Gutierrez Nov 05 '21 at 14:23
  • Do you have flexibility to Skype TALK with me? View profile for contact info, please. Is your server DEDICATED or SHARED on AWS? Any SSD or NVME for storage? – Wilson Hauck Nov 05 '21 at 19:56

1 Answers1

1

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.

Wilson Hauck
  • 2,094
  • 1
  • 11
  • 19
  • Awesome, I'm gonna try your settings and I will tell you the results. – Gutierrez Nov 10 '21 at 12:40
  • Hi Wilson, I made 3 exercises with the restore and it took around 8 hours 10 minutes using your suggested configuration, as a plus I also changed innodb_log_file_size=1134217728 from 134217728, in my case it helped too. From 10+ hours to 8+ hours it really helped me. Thanks a lot. – Gutierrez Nov 16 '21 at 21:07