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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Tomas Helgi JohannssonCommented:
Hi!

Any luck with improving the import speed ? :)

Regards,
    Tomas Helgi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ldvhaiAuthor Commented:
Sorry for late response. This works well, reduce a bit more around 5 minutes, thank you for your support.
Sincerely.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.