i set up a new sql-server, ubuntu 18.4 (MYSQL 5.7), 24 core (48 threads), 256G RAM, System is SATA Raid1 XFS SSD /var/lib/ is mounted on Optane SSD XFS.

In restore i can see that the server has unter 1.0 load so it is quit sleeping. I dont know what i can improve to speedup the restore.

Dump size is 30 GB gzip about 160gb 300 mio entries, attached my mysql.cnf It is a stand alone server just for mysql.

The old server ist mysql 5.6, i dont know if it is able to copy the ibdata files?

socket      = /var/run/mysqld/mysqld.sock
nice        = 0

# * Basic Settings
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp = 1
key_buffer_size     = 6M
#max_allowed_packet = 16M
#thread_stack       = 192K
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 10000
table_open_cache       = 15000
open_files_limit= 17000
innodb_doublewrite = 0
log_error_verbosity = 1
innodb_buffer_pool_size = 220G
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_buffer_pool_instances = 48
innodb_io_capacity = 400000
max_heap_table_size = 5G
# * Query Cache Configuration
query_cache_limit   = 16M
query_cache_size        = 512M
query_cache_type    = 1
# * Logging and Replication
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
# Error log - should be very few entries.
log_error = /var/log/mysql/error.log
# Here you can see queries with especially long duration
#slow_query_log     = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
# * InnoDB
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# * Security Features
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
  • I advise against query_cache_size = 512M. The QC scales poorly above about 50M. Also, max_connections = 10000 is dangerously high. (These comments do not have any impact on the "restore"; they are for later.) – Rick James Feb 1 at 18:59

Restoring from an SQL dump will be always slow. You can make it faster by:

  • Increasing innodb_buffer_pool_size to size of the old datadir or what available RAM allows. 220G in your config is a good value, so this option not for you.
  • Setting innodb_flush_log_at_trx_commit to 0 or 2.
  • Increasing innodb_log_file_size to several GB. 4GB will be OK.
  • Disable query_cache.
  • disable binlogs.

Do not disable innodb_doublewrite - unsafe.

Yet, I doubt you'll gain much.

To restore the database faster use XtraBackup or cold copy (if you shutdown the old database and copy datadir to the new server it will pick it up w/o problems assuming same MySQL version).

|improve this answer|||||
  • Thanks very much :-) Yes doublewrite i know but i found this here, it is just for restoring. dba.stackexchange.com/questions/83125/…. The query cache is needed later in my application however if i use big lists of data is takes minutes with query cache just seconds, is there an impact of query cache in restore? – Peter Jan 30 at 10:15
  • @NorbertSehm - Since the QC applies to SELECTs, and there are no SELECTs when loading a dump, turning it off should have no impact. – Rick James Feb 1 at 18:58

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.