Scan database dump how to do it right?

0 like 0 dislike
29 views
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
by
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
by

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
0 comments
40,796 users