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
query_cache_size = 512M. The QC scales poorly above about 50M. Also,max_connections = 10000is dangerously high. (These comments do not have any impact on the "restore"; they are for later.)