Scan database dump how to do it right?

0 like 0 dislike
Good afternoon. Never did scan the database from dumps, but came need. The size of the dump - 40 GB, after the sweep should be 200GB. You need to deploy it on the laptop to optimize it. But faced with too long a scan, for 14 hours only 1.3 GB(pace scan will go 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;

Can anyone suggest how to speed up the scan?(at least up to 3 days, the AMD FX-9830P, 3000-3700 MHz
RAM 16GB, DDR4-2400).
by | 29 views

2 Answers

0 like 0 dislike
To start:
install in the server settings innodb_flush_log_at_trx_commit = 0
check the value of innodb_buffer_pool_size - you can put 12GB safely

well, in General, the 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
separate support individual tablespace files for InnoDB tables (enable innodb_file_per_table)
support either high performance or high durability (ACID Compliance)
Size up the Increase innodb_log_buffer_size in conjunction with the number of transactions per second (perhaps 32M)
Increase to 64 innodb_read_io_threads
Increase innodb_write_io_threads to 64
Increase innodb_io_capactity to 10000

are You doing all right, most likely all the disk rests on the laptop - in this case innodb_flush_log_at_trx_commit = 0 innodb_buffer_pool_size and a large can help
0 like 0 dislike
14 hours only 1.3 GB this fiasco.... I weight tekdok exported during this time.... with the help heidysql maybe you have one insert one line in database you can dump increase the size of the insert

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
3 answers
0 like 0 dislike
3 answers
0 like 0 dislike
1 answer
110,608 questions
257,187 answers
40,796 users