Bug #107032 The upgrade speed is slow and when there are a large number of partition tables.
Submitted: 15 Apr 2022 6:42 Modified: 15 Apr 2022 13:25
Reporter: jiwei zhangjiwei Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[15 Apr 2022 6:42] jiwei zhangjiwei
Description:
When there are a large number of partition tables, the upgrade from version 8.18 to version 8.22 is slow and high memory usage

Check the source code. During the upgrade from version 8.18 to version 8.22, the prepare_open_for_business function is invoked to modify the name and update persistent stat table for each partition table(function fil_adjust_partition_stat ). The function fil_adjust_partition_stat is submitted once for each partition. Therefore, it takes a long time for many partitions.

How to repeat:
1. Create an instance of version 8.18 that contains 1 million partition tables.
2. Upgrade the instance from version 8.18 to version 8.22 and observe the upgrade time and memory usage.

PS:SQL statement for creating  partitioned tables:
DROP DATABASE IF EXISTS `testdb`;
CREATE DATABASE `testdb`;
USE `testdb` ;

DROP PROCEDURE IF EXISTS `pro_TableCreate`;

DELIMITER $$

CREATE PROCEDURE `pro_TableCreate`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(50);
SET i = 0;

WHILE i<100000 DO
SET table_name = CONCAT('testtable',i);
SET @csql = CONCAT(
'CREATE TABLE ',table_name,'(
   `id` INT, 
   `name` VARCHAR(50), 
   `purchased` DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8
     PARTITION BY RANGE( YEAR(purchased) ) (
     PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (1995),
     PARTITION p2 VALUES LESS THAN (2000),
     PARTITION p3 VALUES LESS THAN (2005),
     PARTITION p4 VALUES LESS THAN (2010),
     PARTITION p5 VALUES LESS THAN (2012),
     PARTITION p6 VALUES LESS THAN (2014),
     PARTITION p7 VALUES LESS THAN (2015),
     PARTITION p8 VALUES LESS THAN (2016),
     PARTITION p9 VALUES LESS THAN (2017)
     );'
);

PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
SET i = i+1;
END WHILE;

END$$

DELIMITER ;

call pro_TableCreate;

Suggested fix:
Adjust the submission policy of the fil_adjust_partition_stat function. Do not submit each partition table.
[15 Apr 2022 7:27] jiwei zhangjiwei
The problem is introduced on commit 9162689906544c59ffea6af88d5686238755018b
[15 Apr 2022 13:25] MySQL Verification Team
Hi Mr. zhangjiwei,

Thank you for your bug report.

However, it is not a bug.

Simply, there were changes in the structure of the partition tables between 8.0.18 and 8.0.28, so that each partition has to be upgraded in order to work properly with 8.0.28 and later releases. If we would skip calling that function for each partition, your installation could have experienced possible corruption of data.

We the upgrade that we designed, you have to wait for it to finish and your installation is ready for production.

In this manner, each partition is upgraded and you will not encounter corruption nor other heavy problems.

Not a bug.
[15 Apr 2022 16:32] cheng zhao
In test, 50,000 partitions use 5mins to upgrade and 1000,000 partitions use 2hours.

During the process, mysql.innodb_datafiles,mysql.innodb_tablespaces,mysql.innodb_table_stats and mysql.innodb_index_stats are updated.
The modification of innodb_datafiles and innodb_tablespaces is committd per 10000 partitions, while modification of innodb_table_stats and innodb_index_stats is committed per partition.

Could the process of updating innodb_table_stats and innodb_index_stats be improved like innodb_datafiles and innodb_tablespaces?