MySQL Bugs: #113800: Performance degradation of Blob operations comparing with MySQL 5.7

Bug #113800	Performance degradation of Blob operations comparing with MySQL 5.7
Submitted:	30 Jan 2024 5:30	Modified:	31 Jan 2024 6:16
Reporter:	MOORE MIAO	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	8.0.22, 8.0.36, 8.0.11	OS:	Linux
Assigned to:		CPU Architecture:	x86
Tags:	blob field, regression

Description:
While performing stress performance tests of our MySQL instances, we noticed that Blob operations work slower on MySQL 8.0 than on MySQL 5.7 with basically the same values of system variables and settings.

In our tests, the blob operations (including blob field insert/select/update, not  particle update) performace drop about 10-20% in MySQL 8.0 compared with MySQL 5.7. With further investigating of the performance degradation, we think that the Blob index organization on MySQL 8.0 causes the performance difference.

Are there any ideas here to optimize this issus?

How to repeat:
For the insert case:

CREATE TABLE `tb1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` blob,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB;

DELIMITER |
CREATE PROCEDURE `pop`()
BEGIN
  DECLARE i int DEFAULT 1;
  WHILE (i <= 50000) DO
    INSERT INTO `tb1` (`b`) values (REPEAT('a', 20000));
    SET i = i + 1;
  END WHILE;
END |
DELIMITER ;

CALL pop();

On MySQL 5.7:
mysql> CALL pop();
Query OK, 1 row affected (19.80 sec)

On MySQL 8.0:
mysql> CALL pop();
Query OK, 1 row affected (22.79 sec)

Other scenarios are similar.

Hello MOORE MIAO,

Thank you for the report and test case.
I'm able to reproduce with default settings but would be interested to try with the configurations used by you for the tests(5.7 and 8.0 differ when it comes to binary logging as by default it is enabled in 8.0 where as disabled in 5.7 etc.). Could you please share configuration from both the instances(5.7/8.0)? Thank you.

regards,
Umesh

Hello Umesh Shastry,
 
I disabled binary logging on both version, default configures with my items: 

innodb_flush_log_at_trx_commit = 1
log_bin = OFF
innodb_doublewrite = OFF
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_chunk_size = 134217728
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 51539607552
innodb_change_buffering = none

You can also test a select case, which is not related to logs.