Thesql innodb dump restore slows down after upgrade thesql 5.7 to 8.0

MySQLmysql-5.7mysql-8.0mysqldump

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!

Best Answer

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

Related Solutions

Mysql – Error 1236 ‘Found old binary log without GTIDs’ after master restart

Something is not right about your process.

Usually, when I see error 1236, such the following I used in my old post How can you monitor if MySQL binlog files get corrupted?

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236).
[ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
111014 20:25:48 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001067', position 183468345.

here was the situation: When doing MySQL Replication without GTID, the IO Thread examines the position from the latest Master binlog. If Read_Master_Log_Pos is bigger than the actual filesize of the binlog, you get error 1236.

When doing MySQL Replication with GTID, the situation is somewhat similar. The IO Thread is looking for some kind of closure with regard to the GTID it was last using. When you restarted MySQL on the Master, you closed the last binlog on the Master and opened a new binlog upon startup. The IO Thread on the Slave was still active. Thus, the same error number is coming up.

The next time you restart a Master, remember the Slaves are active.

The slave should have reconnected after a minute, but that is not happening for you.

To play it safe, you should do the following

On the Slave, STOP SLAVE;
On the Master, service mysql restart
On the Slave, START SLAVE;

You should not have to do this. As an alternative, try setting up replication with heartbeat set at one tenth of a second:

CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 100;

This should make the IO Thread on the Slave a little more sensitive

MySQL upgrade from 5.6 to 8.0

Don't use / (or certain other things that could confuse the file manager) in a table name.

Perhaps you can rename it?

Update

OK, S is the database name, AD is the table name.

No, you cannot move .ibd files around. There is critical information in ibdata1 that will get confused.

It is possible to do

RENAME TABLE X.AD TO Y.AD

(At least that was possible before 8.0. The internals of 8.0 are radically different; if such a cross-db RENAME TABLE fails, file a bug report with bugs.mysql.com )

Best Answer

Related Solutions

Mysql – Error 1236 ‘Found old binary log without GTIDs’ after master restart

MySQL upgrade from 5.6 to 8.0

Related Question