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?
[mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * 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 skip-external-locking skip-name-resolve 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_lock_wait_timeout=120 innodb_io_capacity = 400000 max_heap_table_size = 5G max_allowed_packet=1G sort_buffer_size=9M join_buffer_size=9M # * 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 #log-queries-not-using-indexes # # 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
Best Answer
Restoring from an SQL dump will be always slow. You can make it faster by:
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.innodb_flush_log_at_trx_commit
to 0 or 2.innodb_log_file_size
to several GB. 4GB will be OK.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).