PolarDB:DDL read-ahead

Prerequisites

For information about how to query the cluster version, see Query the engine version.

Considerations

After you enable DDL read-ahead, the read-ahead threads increase I/O resource usage. This may affect other SQL operations that run at the same time. (Recommended) Use DDL read-ahead during off-peak hours.

Usage

• loose_innodb_polar_ddl_build_index_readahead

  Use the following loose_innodb_polar_ddl_build_index_readahead parameter to enable the DDL read-ahead feature:

  Parameter	Level	Description
  loose_innodb_polar_ddl_build_index_readahead	Global	Specifies whether to enable the DDL read-ahead feature. Valid values: ON: enables the DDL read-ahead feature. OFF: disables the DDL read-ahead feature. This is the default value.

• loose_innodb_polar_ddl_build_index_readahead_page_num

  You can also use the following loose_innodb_polar_ddl_build_index_readahead_page_num parameter to set the data volume for DDL read-ahead:

  Parameter	Level	Description
  loose_innodb_polar_ddl_build_index_readahead_page_num	Global	The number of pages for DDL read-ahead. Each page is 16 KB in size. Valid values: 32 to 256. Default value: 64.

Performance test

• Test environment

  • A PolarDB for MySQL 8.0 cluster with 8 cores and 32 GB of memory.

  • The cluster has 50 TB of storage space.

• Test table schema

  Create a table named table_1 by using the following statement:

  CREATE TABLE `table_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_id` bigint(20) DEFAULT NULL,
  `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  `update_time` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;

• Test table data

  Generate test data by using the following statements:

  delimiter ||
  CREATE PROCEDURE populate_0(IN NUM INT)
  BEGIN
  DECLARE sid INT;
  DECLARE suffix_name INT;
  DECLARE i INT;
  SET sid=1000;
  SET suffix_name=10;
  SET i=1;
  START TRANSACTION;
  WHILE i <= NUM
  DO
  INSERT INTO table_1(seller_id,seller_name,gmt_create,update_time) VALUES(sid,CONCAT('sellername',suffix_name),NOW(),NOW());
  SET suffix_name=suffix_name+1;
  SET sid=sid+1;
  SET i=i+1;
  END WHILE;
  COMMIT;
  END ||
  delimiter ;
  CALL populate_0(100000000) ;

• Test method and results

  After the data is inserted, restart the PolarDB process to make sure that the memory buffer pool contains no cached data. Then compare the DDL execution efficiency with and without DDL read-ahead to measure the improvement.

  • Run the SQL statement alter table table_1 add index name_index (seller_name); to compare the DDL execution efficiency:

    DDL read-ahead feature status	Time (seconds)
    DDL read-ahead feature disabled	485
    DDL read-ahead feature enabled (loose_innodb_polar_ddl_build_index_readahead_page_num is set to 256)	412

  • Run the SQL statement alter table table_1 add column c1 varchar(100) after id; to compare the DDL execution efficiency:

    DDL read-ahead feature status	Time (seconds)
    DDL read-ahead feature disabled	264
    DDL read-ahead feature enabled (loose_innodb_polar_ddl_build_index_readahead_page_num is set to 256)	159

Contact us

If you have questions about DDL operations, Contact us.