MySQL Forums
Forum List  »  Backup

Re: Data Export and Import
Posted by: Peter Brawley
Date: July 27, 2021 09:26AM

Extended inserts done the way mysqldump does them can make big ram demands; 10h sounds a lot like swapping is going on.

3.8M rows isn't a lot. This is on a local MySQL instance? 10 hours is way way too long. On a modest modern machine we see row import rates of 35k/sec where average row size is 100 bytes. Is your system optimised? Is it on a modern machine with an SSD? Are the dump file and the DB on different drives? Is innodb_buffer_pool_size big_enough? Is there enough RAM for your load? Run the cheatsheet as described under that name at https://www.artfulsoftware.com/infotree/mysqltips.php, fix what needs fixing.

Once all that's sorted, diagnose the bottleneck---prepare a subset of the problem table, then while it's importing, watch for swapping, watch engine innodb status, compare extended-inserts vs not, keep going till you have best performance.

If it's still too slow, then have a look at https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html.

[Edited to update typical dump import speeds]



Edited 1 time(s). Last edit at 07/27/2021 10:17AM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
325
July 27, 2021 07:58AM
Re: Data Export and Import
187
July 27, 2021 09:26AM
177
July 27, 2021 11:03AM
158
July 27, 2021 01:12PM


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.