1

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!

  • are you sure your mysql not in swap completely? or it error in server configuration? I mean for just a 2GB RAM server you have 6Gb distributed in settings. check with command - free, what is your memory usage. – a_vlad Dec 7 '18 at 13:39
  • 1
    @mehltreter Your innodb_log_buffer_size and innodb_log_file_size are BACKWARDS. buffer_size should always be smaller than file_size. – Wilson Hauck Dec 13 '18 at 21:44
  • 1
    @WilsonHauck: same behaviour with innodb_log_buffer_size = 128M. – mehltreter Dec 19 '18 at 12:49
  • 1
    @mehltreter - how many tables? – Rick James Dec 19 '18 at 15:59
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

  • thanks for reply. i adjuted the values in my.cnf without any better performance. the database i used for testing has only two tables "authors" and "posts" and is generated with random data. only a single foreign key exists between this both tables. no stored programs or any other fancy stuff. i will create a bug report. – mehltreter Dec 10 '18 at 8:14
  • @mehltreter - please add a link to the bug, so others can follow its progress. – Rick James Dec 10 '18 at 16:31
  • @mehltreter Your innodb_log_buffer_size and innodb_log_file_size are BACKWARDS. buffer_size should always be smaller than file_size. – Wilson Hauck Dec 13 '18 at 22:00
  • i checked several configurations and finally created the following bug report: bugs.mysql.com/93684 – mehltreter Dec 19 '18 at 12:52
0

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

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.