文档

在线分区维护

更新时间:

本文档主要介绍了PolarDB的在线分区维护功能。

在MySQL社区版中,对分区表数据的访问操作(DML)与分区维护操作(DDL)是相互阻塞的,这使得分区维护操作只能在业务低峰时段进行。而对分区表进行创建和删除分区操作是比较频繁的,这极大限制了分区表的使用。在线分区维护功能增强了分区的DML和特定DDL(增加、删除分区)的并行能力,更好的让用户对分区表进行Roll-In和Roll-Out。如下图所示:分区锁

前提条件

  • 集群版本需为PolarDB MySQL版8.0版本且Revision version为8.0.2.2.0及以上。您可以通过查询版本号确认集群版本。

  • 已经开启partition_level_mdl_enabled参数。具体操作请参见设置集群参数和节点参数

    参数名称

    级别

    参数说明

    partition_level_mdl_enabled

    Global

    PARTITION Level MDL功能开关,取值范围如下:

    • ON:开启PARTITION Level MDL功能

    • OFF:关闭PARTITION Level MDL功能

    说明

    修改该参数需要重启集群。

  • transaction_isolation参数的全局隔离级别需要设置为READ-COMMITTED。具体操作请参见设置集群参数和节点参数

使用限制

  • 目前支持DROP/EXCHANGE/REBUILD/REORGANIZE PARTITION操作、RANGE和LIST分区方式的ADD PARTITION操作的在线分区维护功能,其他DDL操作将在后续版本支持。

  • 由于隔离级别可以设置为session级别,如果transaction-isolation设置为REPEATABLE-READ或者更强的隔离级别,在并发执行DDL过程中,可能会报错ERROR HY000: Table definition has changed, please retry transaction,这属于正常现象。原因是事务访问到了DDL所创建出来的新分区,可通过重新执行事务解决。

使用说明及示例

在线分区维护功能,使得分区表的数据访问和分区维护相互不影响,用户可以更自由的进行分区维护,而不影响分区表业务流量,使用示例及效果如下:

#在客户端1上输出tr表结构
SHOW CREATE TABLE tr\G                                 
*************************** 1. row ***************************    
       Table: tr                                                  
Create Table: CREATE TABLE `tr` (                                  
  `id` int(11) DEFAULT NULL,                                       
  `name` varchar(50) DEFAULT NULL,                                 
  `purchased` date DEFAULT NULL                                    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
/*!50100 PARTITION BY RANGE (year(`purchased`))                    
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,             
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,             
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,             
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,            
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,            
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */         
1 row in set (0.00 sec)

#在客户端1上开启事务
BEGIN;                                                 
Query OK, 0 rows affected (0.01 sec)                               
                                                                  
SELECT * FROM tr WHERE purchased >= '2010-01-01';      
+------+----------------+------------+                            
| id   | name           | purchased  |                             
+------+----------------+------------+                             
|    5 | exercise bike  | 2014-05-09 |                         
|    7 | espresso maker | 2011-11-22 |                             
+------+----------------+------------+                             
2 rows in set (0.01 sec)                                          

#在客户端2上创建新分区
ALTER TABLE tr ADD PARTITION (PARTITION p6 VALUES LESS THAN (2020));
INSERT INTO tr VALUES (11, 'hope', '2017-11-04'), (12, 'carmen', '2018-06-08');

#在同一个事务内,通过客户端1可以访问到新增分区的数据
SELECT * FROM tr WHERE purchased >= '2010-01-01';
+------+----------------+------------+                             
| id   | name           | purchased  |                            
+------+----------------+------------+                            
|    5 | exercise bike  | 2014-05-09 |                            
|    7 | espresso maker | 2011-11-22 |                             
|   11 | hope           | 2017-11-04 |                             
|   12 | carmen         | 2018-06-08 |                            
+------+----------------+------------+                            
4 rows in set (0.00 sec)

#在客户端2上删除旧分区
ALTER TABLE tr DROP PARTITION p0;

#在客户端1上输出表定义,可以看到新分区p6存在,旧分区p0已不存在
SHOW CREATE TABLE tr\G                                 
*************************** 1. row ***************************     
       Table: tr                                                   
Create Table: CREATE TABLE `tr` (                                 
  `id` int(11) DEFAULT NULL,                                       
  `name` varchar(50) DEFAULT NULL,                                 
  `purchased` date DEFAULT NULL                                    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
/*!50100 PARTITION BY RANGE (year(`purchased`))                    
(PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,             
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,             
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,             
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,            
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB,             
 PARTITION p6 VALUES LESS THAN (2020) ENGINE = InnoDB) */         
1 row in set (0.00 sec)

#在客户端1上提交事务
COMMIT;

性能表现

下面针对DML和DDL交互下的两种最主要场景长事务阻塞DDL和耗时DDL,在分别开启和关闭在线分区维护功能的情况下,进行性能对比验证。

  • 场景一:长事务阻塞DDL

    此场景下对分区表做分区维护DDL操作,由于存在未提交的长事务,从而阻塞DDL操作的进行。被阻塞的DDL操作会进一步阻塞所有新产生的DML操作,导致了数据库流量跌零。长事务阻塞

    从上图可以看到,在功能关闭时,当DDL操作发生后,sysbench在该表的流量迅速跌零,数据库呈现完全不可用的状态。用户只能通过取消DDL操作或者提交所有未提交的长事务后,数据库才能恢复正常;在功能开启后,可以看到:

    • 在正常情况下,sysbench的吞吐量与在线分区维护功能关闭时完全一致,说明在线分区维护功能的引入并没有带来性能方面的损耗;

    • 未提交的长事务并不会阻塞分区维护操作的执行,同时数据库上的DML流量很稳定,几乎没有受到影响。

  • 场景二:耗时DDL

    该场景下,分区维护DDL操作虽然没有受到其他SQL语句的阻塞,但是在本身执行分区维护DDL操作耗时的情况下,对DML吞吐量有影响。耗时DDL

    从上图可以看到,在功能关闭时,长耗时DDL操作在执行期间导致了DML吞吐量的剧烈抖动;在功能开启后,长耗时DDL操作在执行期间对 DML的吞吐量几乎没有影响。

查看MDL锁状态

在线分区维护功能是通过引入分区级别的MDL锁,来降低在DML和DDL过程中获取的锁粒度,从而增加并发性来实现的。在分区维护过程中,通过performance_schema.metadata_locks表可以查看分区级别的MDL锁获取情况。示例如下:

#在客户端1上输出tr表结构
SHOW CREATE TABLE tr\G                                 
*************************** 1. row ***************************    
       Table: tr                                                  
Create Table: CREATE TABLE `tr` (                                  
  `id` int(11) DEFAULT NULL,                                       
  `name` varchar(50) DEFAULT NULL,                                 
  `purchased` date DEFAULT NULL                                    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
/*!50100 PARTITION BY RANGE (year(`purchased`))                    
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,             
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,             
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,             
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,            
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,            
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */         
1 row in set (0.00 sec)

#在客户端1上开启事务
BEGIN;                                                 
Query OK, 0 rows affected (0.01 sec)                               
                                                                  
SELECT * FROM tr WHERE purchased >= '2010-01-01';      
+------+----------------+------------+                            
| id   | name           | purchased  |                             
+------+----------------+------------+                             
|    5 | exercise bike  | 2014-05-09 |                         
|    7 | espresso maker | 2011-11-22 |                             
+------+----------------+------------+                             
2 rows in set (0.01 sec)

#在客户端1上查看MDL锁的状态
SELECT * FROM performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | test               | tr             | NULL        |       140734887898944 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6759 |              67 |             17 |
| PARTITION   | test               | tr             | p5          |       140734887896704 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6502 |              67 |             17 |
| TABLE       | performance_schema | metadata_locks | NULL        |       140734879511488 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6759 |              68 |              4 |
| SCHEMA      | performance_schema | NULL           | NULL        |       140734879511648 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | dd_schema.cc:108  |              68 |              4 |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
4 rows in set (0.02 sec)

#在客户端1上可以看到,获得tr表级的SHARED_READ锁和剪枝后实际需要访问p5分区级别的SHARED_READ锁。接下来,通过客户端2尝试将分区p5删除
ALTER TABLE tr DROP PARTITION p5;

#由于分区p5正在被客户端1问,所以上述操作会进入等待状态,通过下述命令可以看到该线程正在等待分区级别的MDL锁。
SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+-------------------------------------+----------------------------------+
| Id | User            | Host      | db   | Command | Time | State                               | Info                             |
+----+-----------------+-----------+------+---------+------+-------------------------------------+----------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 1550 | Waiting on empty queue              | NULL                             |
|  8 | root            | localhost | test | Sleep   |  426 |                                     | NULL                             |
|  9 | root            | localhost | NULL | Query   |    0 | starting                            | SHOW PROCESSLIST                 |
| 10 | root            | localhost | test | Query   |   10 | Waiting for partition metadata lock | ALTER TABLE tr DROP PARTITION p5 |
+----+-----------------+-----------+------+---------+------+-------------------------------------+----------------------------------+
4 rows in set (0.00 sec)

#通过客户端1提交事务后,客户端2能够顺利完成分区的维护。

查看在线分区维护次数

通过STATUS变量Online_altered_partition参数可以监控进行在线分区维护的次数,示例如下:

SHOW STATUS LIKE 'Online_altered_partition';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Online_altered_partition | 2565  |
+--------------------------+-------+
1 row in set (0.00 sec)

操作视频

  • 本页导读 (0)
文档反馈