Scanning a database dump, how to do it right?

Question

SQL

Good afternoon. I have never dealt with deploying a database from dumps, but the need has come. The dump size is 40 GB, after the scan it should be 200 GB. You need to deploy it on a laptop to optimize it. But I ran into too long a scan, in 14 hours only 1.3 GB (At this rate, the scan will take months ...).
Deployed through the console using the code:

SET NAMES utf8;
DROP DATABASE `tecdoc`;
CREATE DATABASE `tecdoc` DEFAULT CHARACTER SET utf8;
USE `tecdoc`;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; 
SET autocommit=0;
SET foreign_key_checks=0;
SET unique_checks=0;
SOURCE path/file_name.sql;
COMMIT;
SET autocommit=1;
SET foreign_key_checks=1;
SET unique_checks=1;

Who can tell me how to speed up its scan? (at least up to 3 days, Processor AMD FX-9830P, 3000-3700 MHz
RAM 16GB, DDR4-2400).

Answer 1

To get started:
set innodb_flush_log_at_trx_commit = 0 in the server settings,
check the value of innodb_buffer_pool_size - you can safely set 12GB
and, in general, advice from Google:

Make InnoDB access all your cores
Increase innodb_buffer_pool_size to 12G
Increase innodb_buffer_pool_instances to 2 (First run numactl --hardware to determine the number of Physical CPUs. What every number of CPUs it reports, use that number. I learned this recently in Jeremy Cole's Blog)
Increase Log File Size (innodb_log_file_size) to 2047M
support separate tablespace files for individual InnoDB tables (enable innodb_file_per_table)
support either high performance or high durability (ACID Compliance)
Increase Size up the innodb_log_buffer_size in conjunction with the number of transactions per second (perhaps 32M)
Increase innodb_read_io_threads to 64
Increase innodb_write_io_threads to 64
Increase innodb_io_capactity to 10000

you are doing everything right, most likely everything rests on the disk on the laptop - in this case innodb_flush_log_at_trx_commit = 0 and a large innodb_buffer_pool_size can help

Answer 2

14 hours is only 1.3 GB this is a fiasco.... I exported the weight of the tekdoc during this time.... with the help of heidysql you can have one insert one line of the database try to increase the size of the insert when creating the dump