I'm having a sql file about 3.5GB with some tables about 7 millions records , and a Ubuntu 16.04 server configured to run on a VPS with 8 vCPU, 32GB RAM. (RAM Disk)
The RAM Disk mount command is:
# sudo mount -t tmpfs -o size=25G tmpfs /opt
When importing that sql file using default MySQL configuration, its took about 3 hours and a half to finish.
After using this optimized MySQL configuration, it's took only about 20 minutes:
innodb_buffer_pool_size = 8G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
When importing, import speed goes around 1MiB/s to 7MiB/s. In the end, the average importing speed with this new parameters is 2.5 MiB/s.
My question is, which parameter should i optimize to increase average importing speed, in order to reduce import time from 20 minutes to about 10-12 minutes?
Hope to see experts's answers soon, thank you.