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
Community
  • 1
FanoFN
  • 103
  • 6
  • 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 Mar 10 '20 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 Mar 11 '20 at 00: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] (https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-bulk-data-loading.html) – nbk Mar 11 '20 at 00: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 Mar 11 '20 at 00:43
  • @nbk , looks like the cpu and RAM load is the obvious reason why this is happening. Unfortunately, despite the db that I'm working with is not a production db, it's not the only db inside the server. And unfortunately I can't do much about disabling some other tasks during daytime.. I may have to schedule a night time temporary shut down of other tasks if necessary. I'll take a look at the bulk insert link, thanks. – FanoFN Mar 11 '20 at 01:33
  • @danblack , I've updated the question with my.ini setting. Apart from innodb_buffer_pool_size (which was the default value) all of the other setting I follow Rolando's suggestion. I don't think I'm able to increase the setting to 10GB but I think to 4GB is possible – FanoFN Mar 11 '20 at 01:35
  • What's the ping time to each server? Do the dumps include multi-row INSERTs? Where indexes enabled before or after the load? Ditto for FOREIGN KEYs? Let's see SHOW CREATE TABLE. – Rick James Mar 20 '20 at 21:17
  • @RickJames , Sorry I haven't provided update on this. I actually did a few tests and conclude that the server location where I was importing have a lot of processes going on and causing the import to be significantly slow. I'll post the findings shortly – FanoFN Mar 21 '20 at 02:42
  • 1
    A lot of other processes won't necessarily explain it. – Rick James Mar 21 '20 at 03:37

1 Answers1

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.

FanoFN
  • 103
  • 6