mysql innodb dump restore slows down after upgrade mysql 5.7 to 8.0

Question

I'm struggeling the last two days with the following issue:

Restoring a ~1GB database dump with only innodb tables on a mysql 5.7 server takes about 5 minutes. After upgrading the same server to mysql 8.0(.13) it takes about 40 minutes (with exactly the same configuration). Are there any changes in mysql 8.0 that can cause the performance lack?

The dump was created local on a mysql 5.7 server with the following command:

$ mysqldump --opt -uuser -p database > database.sql

The dump was restored locally with the following command:

$ mysql -uuser -p database < database.sql

The first few lines of database.sql:

-- MySQL dump 10.13  Distrib 8.0.13, for Linux (x86_64)
--
-- Host: xx.xx.xx.xx    Database: xxx
-- ------------------------------------------------------
-- Server version       5.7.19-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SET NAMES utf8mb4 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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'        */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `xxx`
--
[...]

This issue is tested on a virtual testserver with exactly the same hard- and software configuration in all test cases:

OS: Debian 9.6
Disk: SSD
CPU: 4 CPU Cores
RAM: 10 GB

The my.cnf is the same for mysql 5.7 and mysql 8.0(.13) server:

[mysqld]
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
language                = /usr/share/mysql/english
skip-external-locking
skip-name-resolve

innodb_buffer_pool_size         = 2048M
innodb_log_buffer_size          = 128M
innodb_log_file_size            = 265M
innodb_flush_log_at_trx_commit  = 2
innodb_flush_method             = O_DIRECT

max_allowed_packet      = 64M
key_buffer_size         = 2048M

thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP

server-id               = 7
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_format           = MIXED

lc_messages                     = de_DE
default_authentication_plugin   = mysql_native_password

character-set-client-handshake  = FALSE
character-set-server            = utf8mb4
collation-server                = utf8mb4_unicode_ci

sql_mode        = "ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Any suggestions? Thank you folks!

Answer 1

innodb_log_buffer_size should be no more than 1% of RAM.

key_buffer_size should be only 30M, assuming you do not have any active MyISAM tables.

Those changes will let you increase innodb_buffer_pool_size to 7G, which might help.

Do you have any FULLTEXT or SPATIAL indexes? FOREIGN KEYs? Stored programs (probably not, since you did not ask for them to be dumped)?

If those don't help, file a bug with https://bugs.mysql.com

Answer 2

Your innodb_log_buffer_size and innodb_log_file_size are BACKWARDS. buffer_size should always be smaller than file_size.

Answer 3

Enable derived_merge on MySQL instance :

set global optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=ON" ; 

Now, test mysqldump on your server.

Reason : mysqldump is executing many show fields from <table>; which are slower when derived_merge is OFF.

Answer 4

I have verified this on MySQL version 8.0.15. You have to set this optimizer switch before running the mysqldump command. To only set the one switch use set global optimizer_switch="derived_merge=ON";