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!

12
  • 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, 2018 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. Dec 13, 2018 at 21:44
  • 1
    @WilsonHauck: same behaviour with innodb_log_buffer_size = 128M.
    – mehltreter
    Dec 19, 2018 at 12:49
  • 1
    @mehltreter - how many tables?
    – Rick James
    Dec 19, 2018 at 15:59

4 Answers 4

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

4
  • 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, 2018 at 8:14
  • @mehltreter - please add a link to the bug, so others can follow its progress.
    – Rick James
    Dec 10, 2018 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. Dec 13, 2018 at 22:00
  • i checked several configurations and finally created the following bug report: bugs.mysql.com/93684
    – mehltreter
    Dec 19, 2018 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.

0

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.

0

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";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.