0

I am receiving .SQL(500mb) backup file and restoring to MYSQL (8.0.13) database.

Currently, restore is taking 5 hrs to complete. I would like to speedup the process and started working on it.

Here are the things I tried

  1. Changed innodb_flush_log_at_trx_commit=2 from 1.

  2. Changed expire_log_days from 0 to 2. My log was filling up and storing file for everyday.

  3. My .sql file already have Unique key,Foreign key checks to zero.AutoCommit=0 like below

    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /; /!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /; /!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

Here are my my.ini file settings

 max_connections=151
 table_open_cache=2000 
tmp_table_size=172M
 thread_cache_size=10 
myisam_max_sort_file_size=100G
 myisam_sort_buffer_size=334M 
key_buffer_size=8M
 read_buffer_size=64K
 read_rnd_buffer_size=256K 
innodb_flush_log_at_trx_commit=2
 innodb_log_buffer_size=1M 
innodb_buffer_pool_size=8M
 innodb_log_file_size=48M 
Innodb_thread_concurrency=17
 innodb_autoextend_increment=64 
innodb_buffer_pool_instances=8
 innodb_concurrency_tickets=5000 
innodb_old_blocks_time=1000
 innodb_open_files=300 i
nnodb_stats_on_
metadata=0
 innodb_file_per_table=1 
flush_time=0 
join_buffer_size=256K
 max_allowed_packet=4M 
max_connect_errors=100 
open_files_limit=4161
 sort_buffer_size=256K
 table_definition_cache=1400

frustrating part is sometimes restore is stuck for hours and I have to kill the connection.

Please let me know what I am doing wrong here.

Appreciate your help.

Improve this question
0

innodb_buffer_pool_size should be closer to 1G rather than 8M

also increase innodb_log_file_size to 256M as the default 48M is a bit slow.

A innodb_log_buffer_size of 48M would help rather than a very small 1M.

Improve this answer
2
  • I have followed your recommendations and performance improved at least 2x times. Thanks for your help. – Samardh Nov 30 '20 at 23:37
  • I'm glad it helped, but I'm disappointed its only 2x. Look at including advice in other answers as well. – danblack Dec 1 '20 at 2:15
0

Solution :

  • I would advice to use 8.0.17 or above, so many bugs are fixed after 8.0.17 version.

  • Disable the transaction extraction algorithm, as it is of no use unless you are doing group replication.

transaction_write_set_extraction..... OFF

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.