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
Server 2 - import 20+GB data only managed to import less than half in 24hr:
Intel Xeon E3 3.40 Ghz
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 : https://stackoverflow.com/a/12439368/10910692
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