0

After I set my.ini file following the answer by Rolando here : https://dba.stackexchange.com/a/83385/191579 , 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
16GB RAMs
MariaDB 10.3

Server 2 - import 20+GB data only managed to import less than half in 24hr:
Intel Xeon E3 3.40 Ghz
16GB RAMs
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?

Edit: I was looking around and found this : https://stackoverflow.com/a/12439368/10910692

Use:

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:

[mysqld]

innodb_buffer_pool_size=1024M
innodb_file_per_table=1
skip-host-cache
skip-name-resolve

innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite=0
Improve this question
9
  • An import is quite complex special, when you did not disabled check constraint. Also the sheer size of d adumb file, says nothing about its internal structure. also heck the error log of mysql to see if something causes a prpblem
    – nbk
    Commented Mar 10, 2020 at 16:19
  • @nbk I did have a look in error log but there's nothing.. it's just too damn slow while importing. I assume that maybe because of the RAM and cpu usage on that particular server are at 50%-60%. If I calculated the average rows imported per second is around 800+. That translate into around 3 million per hour and to complete import a 200million rows will take about 66 hours!
    – FanoFN
    Commented Mar 11, 2020 at 0:29
  • yes, a high and moderate load would make it much slower. Can't you disable at least temporary all other tasks. i really don't think that to insert va backup, should anything time consuming be running. Ans also did you have a look at [bulk insert] (dev.mysql.com/doc/refman/8.0/en/…)
    – nbk
    Commented Mar 11, 2020 at 0:36
  • Also look at the comments of this question. If you have 16G of ram use 10G as the innodb_buffer_pool_size (assuming the ram available). Including exact my.ini settings in this question would be useful.
    – danblack
    Commented Mar 11, 2020 at 0:43
  • 1
    A lot of other processes won't necessarily explain it.
    – Rick James
    Commented Mar 21, 2020 at 3:37

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.