MariaDB/MySQL: Import from dump (.sql) file remotely via network

Posted on

Question :

After I set my.ini file following the answer by Rolando here : , today I managed to import about 40GB+ sqldump file in around 2-3 hours only. While on a different server; where I have set similar setting yesterday and importing around 20+GB data is still running today and I check it only managed to import less than half of the data inside (70+ million from a 200+ million rows).

Specs for both server are following:

Server 1 – import 40+GB data in 2-3 hours:
Intel Xeon E3 3.00 Ghz
MariaDB 10.3

Server 2 – import 20+GB data only managed to import less than half in 24hr:
Intel Xeon E3 3.40 Ghz
MariaDB 10.3

Clearly both server have almost similar specs but I don’t understand why the other take so long to dump. Both of these database are not production/live db.

I usually opt to do import/dump locally for big files because I’m on the impression that it’s much faster that way (obviously) but this case made me think of the otherwise. I’m currently taking the dump file into my PC to split it in smaller chunks and while waiting, I’m just wondering whether if I do import from a different pc via network (LAN etc.), will the process depending on the remote server pc specs or the local pc specs?

I was looking around and found this :


mysqldump --extended-insert=FALSE

Be aware that multiple inserts will be slower than one big insert.

The 20+GB I dumped with this setting. Could it be the cause?

my.ini setting on Server 1 & Server 2:



innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

Answer :

After a few tests, I conclude that the server itself (where I want to import) is slow due to having a lot of process going on. I think that was obvious from the beginning but I was just frustrated that I’ve set identical setting on both machines and getting a significantly different benchmarks.

The last test that I did was the following, 200MB sql dump file size I import to the destination server (server 2) using two method; locally & remotely; bench marked against another server unit I have locally (server 1). Few more details:

  1. Server 1 – CPU load 20% – 30% and RAM load 50% – 60%
    Import on MariaDB 10.3 for 200MB sql dump file took less than 2 minutes.

  2. Server 2 – CPU load 50% – 60% and RAM load 50% – 60%
    Import on MariaDB 10.3 for 200MB sql dump file took up to 8 minutes locally.
    Import on MariaDB 10.3 for 200MB sql dump file took up to 18 minutes remotely.

Like I mentioned, both servers have similar my.ini settings. I did not investigate further why Server 2 is slower but the import perform miles better when I discard mysqldump --extended-insert=FALSE setting on my dump files after finding that this causes extremely slow inserts!. The other obvious probable reason would be the server is busy (hint: CPU load). Also, the data that I need to import is actually for another party to use but since the time zone difference between us and them is 6 hours (I just found out about this!), I didn’t pursue this aggressively.

Leave a Reply

Your email address will not be published. Required fields are marked *