MySQL Forums
Forum List  »  General

Re: Import of SQL file slow or freezes
Posted by: Craig Eidson
Date: August 26, 2018 06:07PM

Good catch. 8M! Yikes! In response, I read the articles on regarding "Configuring InnoDB Buffer Pool Size". Once I figured out that windows looks for the my.ini file under C:\ProgramData folder I adjusted my values to:

innodb_buffer_pool_size = 8G
innodb_buffer_chunk_size = 128M
innodb_buffer_pool_instances = 64

No significant change was seen. (I've now switched from mysql client and workbench over to HeidiSQL since neither Mysql product shows me status or transfer rate).

The biggest change I've been able to achieve is by temporarily removing autocommit, unique checks and foreign key checks by following "8.5.5 Bulk Data Loading for InnoDB Tables". I've also exported a smaller dataset (80 mB) from my master database and was able to upload this to my sandbox database on my laptop in about an hour. Still painfully slow but some speed improvements with the trade off of risking duplicate inserts.

Options: ReplyQuote

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.