Speed Up MySQL Import Speed

Hi Experts.

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.
LVL 2
ldvhaiAsked:
Who is Participating?
 
Tomas Helgi JohannssonCommented:
Hi!

Any luck with improving the import speed ? :)

Regards,
    Tomas Helgi
0
 
Tomas Helgi JohannssonCommented:
Hi!

If you are using the mysqlimport then I suggest you use the compress option to compress data between client and server.
Other parameter that you should increase to speed up the import is the max_allowed_packet parameter and set it to >512MB in the my.cnf and restart the server.
I usually set it to 1- 1.5 GB to be able to restore 5-6 GB backups fast enough.
Other thing you should do is install the mysqltuner script and run it regularly to see what paramters you should tune. It should be available through your systems repository.

See further here
https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
https://dev.mysql.com/doc/refman/5.7/en/packet-too-large.html

Regards,
     Tomas Helgi
1
 
ldvhaiAuthor Commented:
Sorry for late response. This works well, reduce a bit more around 5 minutes, thank you for your support.
Sincerely.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.