0

I'm using MySQLWorkbench (8.0.25) in order to migrate data from a database to another database on the same server and it takes 10mins+ to do so, which seems like a very long time.

The database, on AWS, is a single AZ db.t2.micro database located in Paris, eu-west-3.

Using speedtest.net, it seems I have 25Mb/s download and around the same upload speed, and it does feel like it as I'm browsing. I am very far from the datacenter (I'm in Buenos Aires and it's in Paris) but I always have a VPN turned on whose t2.small server is located in the same datacenter as the database (eu-west-3). In particular, the VPN was on during the test, so the 25Mb/s should be representative of what I can expect during a migration of the eu-west-3 database.

The database is of reasonable size: when I export my database from MySQL workbench, the total dump size is 26Mb.

I'm currently living in Buenos Aires. When I was in France, this migration would take around 30s (I had 150Mb/s download back then and probably like 50Mb upload).

Could you help me understand why it takes so much time despite the still decent internet I have here? Thanks in advance.

Kévin HERRERO
  • 229
  • 2
  • 10
  • First of all, your operation doesn't depend on "your" internet speed. You are migrating data from one table to another, it's not like you download the data locally and then upload it. I don;t know how MySQLWorkbench works, so I would suggest to copy the data via cli and check if this issue is related to MySQLWorkbench or the RDS. – Riz Jan 24 '22 at 16:28
  • Thanks for your answer. I've edited my question since I made a mistake. I'm actually transferring a whole database with many tables to another database on the same server. Sorry about that. – Kévin HERRERO Jan 24 '22 at 17:24
  • And I get similarly long times with other RDS instances so I'm assuming the problem comes from my internet connection or MySQLWorkbench. But Workbench worked fine back in France... – Kévin HERRERO Jan 24 '22 at 17:35

1 Answers1

1

I can't write all in a comment so I am gonna reply here. I am not familair with MySQLWorkbench so I don't know how it does the migration. I would like you to do the migration through cli and see if the issue really is RDS.
I have this script(taken from this forum a while ago and don't remember the source) which does this migration very easily.
If the destination database is present, you need to either rename it(here is how) or delete it all together(drop database $dbname;).
Create a bash script in the instance from where you can access the RDS and copy the following into it.

    #!/bin/bash
    start=`date +%s`
    set -e 
     
    mysqlconn="mysql -u $rootuser -ppassword -h $hostname"
    olddb=$1
    newdb=$2
    $mysqlconn -e "CREATE DATABASE $newdb"
    params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \
                               WHERE table_schema='$olddb'")
    for name in $params; do
          $mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name";
    done;
    $mysqlconn -e "DROP DATABASE $olddb"
    end=`date +%s`
    runtime=$((($(date +%s)-$start)/60))
    echo "Total time taken is ${runtime} minutes"

You can run it as $scriptname.sh $olddb $newdb. If it takes more time then you try with nohup $scriptname.sh $olddb $newdb &.
This way you can be sure that the culprit is mysqlworkbenchand not RDS.

Riz
  • 1,131
  • 1
  • 9
  • 12
  • I used your script, modifying a little bit since, once again, my question wasn't clear. I wanted to copy the data, not migrate it. It took 6mins to run, which is an improvement but still seems like a lot. But when improving it by creating one huge sql statement instead of many small ones, it runs in 1min. I still don't understand what happens there. There are 120 tables. – Kévin HERRERO Jan 25 '22 at 18:52
  • I don't know much about databases so I would leave it to someone with more knowledge. In your case through, your internet connection doesn't make any difference(apart from the obvious, logging into your ec2 or connecting to your db). Once you are connected to your db, the speed will be similar no matter if you are sitting outside the AZ which hosts your db or in Siberia(because both databases are in the same RDS/machine/server) ;P. – Riz Jan 25 '22 at 20:57