Description:
In 8.0.13 the following optimization was implemented which helped improve Load times for bulk imports:
"The line buffer had a small initial size and was growing with small increments. Allocate larger initial line buffer when processing SQL scripts: 16 MB instead of 512 bytes. And grow it in increments of 16MB instead of 4K when processing SQL scripts in non-interactive mode."
https://github.com/mysql/mysql-server/commit/333972b4566ff18359b21d9e6ab3a1a5b046245b
In my testing I have found that this issue also effects MySQL 57. In my test case with binlog replay, the MySQL 57 client can drastically decrease replay performance for large binlog payloads.
After back porting/testing the above commit on 57 locally, Binlog replay speed for a 100 M event improved from nearly 30 mins on the default MySQL 57 client to 7 seconds on a patched 57 client.
Command:
date;sudo mysqlbinlog -D --force-if-open --local-load=/tmp ./mysql-bin.000007 | mysql -uroot;date;
### Without fix
Start : Sat Jun 6 23:39:01 UTC 2020
Fin : Sun Jun 7 00:06:20 UTC 2020
### With fix
Start : Sun Jun 7 01:01:10 UTC 2020
Fin : Sun Jun 7 01:01:17 UTC 2020
How to repeat:
1. create a table with a large blob field:
CREATE TABLE if not exists `blob_test` (
`id` bigint(11) primary key AUTO_INCREMENT,
`col1` longblob
) ENGINE=InnoDB;
2. Flush binary logs to rotate log file and note binlog file. This will be used to replay later.
3. Insert a large row. (attached 100mb)
4. Truncate table created in 1.
5. Attempt to reload binlog file.
/usr/local/mysql/bin/mysqlbinlog -D --force-if-open --local-load=/tmp ./mysql-bin.000007 | /usr/local/mysql/bin/mysql -uroot
6. You should see it takes a VERY long time to load. Per the example above, times can differ from 30 mins without patch to 7 seconds with the patch.
OR
1. create a table with a large blob field:
CREATE TABLE if not exists `binlogtest`.`blob_test` (
`id` bigint(11) primary key AUTO_INCREMENT,
`col1` longblob
) ENGINE=InnoDB;
2. Attempt to load attached binlog file with and without the patch.
/usr/local/mysql/bin/mysqlbinlog -D --force-if-open --local-load=/tmp ./mysql-bin.000007 | /usr/local/mysql/bin/mysql -uroot
my.cnf
binlog_format=ROW
max_allowed_packet=1073741824
Suggested fix:
Since the fix was verified and implemented in MySQL 8, can it also be backported to 57? This will greatly improve load times for binary logs and bulk inserts in 57 deployments.
https://github.com/mysql/mysql-server/commit/333972b4566ff18359b21d9e6ab3a1a5b046245b
I will try upload a sample large insert statement and sample binlog for you to easily repro
