关于oceanbase:OceanBase-建表分区数超限报错

OceanBase 单机租户容许创立的最大分区数是多少？作者通过分区超限谬误排查，计算出单机容许创立的最大分区数量。

作者：何文超，爱可生南区交付服务部 DBA 团队成员，次要负责 MySQL 故障解决，MySQL 高可用架构革新，OceanBase 相干技术支持。喜好足球，羽毛球。
爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。
本文共 1200 字，预计浏览须要 3 分钟。

背景

ERROR 1499 (HY000): Too many partitions (including subpartitions) were defined

创立表报错，尽管是外部谬误，然而错误信息是指：创立了太多了分区。

[root@observer04 ~]# mysql -h10.186.64.125 -P2883 -uroot@wenchao_mysql#hwc_cluster:1682755171 -p"xxxx"  MySQL [lss]> CREATE TABLE `wms_order` (  `A1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'A1',  `A2` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'A2',  `A3` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'A3',  `A4` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'A4',  `A5` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'A5',  `A6` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'A6',  `A7` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'A7',  `A8` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'A8',  `A9` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'A9',  `A10` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'A10') DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = '物流订单表'MySQL [lss]> ERROR 1499 (HY000): Too many partitions (including subpartitions) were defined

接下来咱们剖析一下问题的起因。

排查

2.1 查看参数

查看每个 OBServer 上能够创立最大的分区数量，以后是 500000。

[root@observer04 ~]# mysql -h10.186.64.125 -P2883 -uroot@sys#hwc_cluster:1682755171 -p"xxxx" -A oceanBaseMySQL [oceanBase]> select * from __all_virtual_sys_parameter_stat where name like '%_max_partition_%';+-------+----------+---------------+----------+-------------------------------+-----------+--------+--------------+---------------------------------------------+-------------+----------+---------------+---------+---------+-------------------+| zone  | svr_type | svr_ip        | svr_port | name                          | data_type | value  | value_strict | info                                        | need_reboot | section  | visible_level | scope   | source  | edit_level        |+-------+----------+---------------+----------+-------------------------------+-----------+--------+--------------+---------------------------------------------+-------------+----------+---------------+---------+---------+-------------------+| zone1 | observer | 10.186.64.122 |     2882 | _max_partition_cnt_per_server | NULL      | 500000 | NULL         | specify max partition count on one observer |        NULL | OBSERVER | NULL          | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |+-------+----------+---------------+----------+-------------------------------+-----------+--------+--------------+---------------------------------------------+-------------+----------+---------------+---------+---------+-------------------+

查看以后分区数量的和，目前并没有超过这个限度（500000）。

MySQL [oceanBase]> select count(*) from v$partition;+----------+| count(*) |+----------+|   421485 |+----------+

2.2 查看回收站

查看回收站是否开启？

[root@observer04 ~]# mysql -h10.186.64.125 -P2883 -uroot@wenchao_mysql#hwc_cluster:1682755171 -p"xxxx"MySQL [lss]> show variables like '%recy%';+---------------+-------+| Variable_name | Value |+---------------+-------+| recyclebin    | ON    |+---------------+-------+1 row in set (0.01 sec)

查看回收站中是否存在未删除的分区表？

MySQL [lss]> show recyclebin;+-----------------------------------------+---------------+-------+----------------------------+| OBJECT_NAME                             | ORIGINAL_NAME | TYPE  | CREATETIME                 |+-----------------------------------------+---------------+-------+----------------------------+| __recycle_$_1682755171_1689139725669688 | mytable_1     | TABLE | 2023-07-12 13:28:45.687379 || __recycle_$_1682755171_1689139737584112 | mytable_1     | TABLE | 2023-07-12 13:28:57.584660 || __recycle_$_1682755171_1689139750594392 | t1            | TABLE | 2023-07-12 13:29:10.594118 |+-----------------------------------------+---------------+-------+----------------------------+3 rows in set (0.01 sec)

如果存在，须要和业务侧沟通是否能够清理。回收站的表清理后，发现分区表数量缩小，然而创立表仍旧报错。

查看回收站中中对象保留天数。

MySQL [lss]> SHOW PARAMETERS LIKE 'recyclebin_object_expire_time'\G;*************************** 1. row ***************************   zone: zone1  svr_type: observer svr_ip: 10.186.64.122  svr_port: 2882   name: recyclebin_object_expire_time data_type: NULL  value: 0s   info: recyclebin object expire time, default 0 that means auto purge recyclebin off. Range: [0s, +∞)section: ROOT_SERVICE  scope: CLUSTER source: DEFAULT

row in set (0.02 sec)

配置项 recyclebin_object_expire_time 的取值阐明如下：

当其值为 0s 时，示意敞开主动 Purge 回收站性能。
当其值不为 0s 时，示意回收一段时间前进入回收站的 Schema 对象。

2.3 查看租户内存

找到分区数最多的 10 个租户。

[root@observer04 ~]# mysql -h10.186.64.125 -P2883 -uroot@sys#hwc_cluster:1682755171 -p"xxxx"// 1. 找到分区数最多的 10 个租户SELECT t2.tenant_name,t2.tenant_id, t1.replica_countFROM (SELECT tenant_id, COUNT(*) AS replica_count  FROM __all_virtual_partition_info  GROUP BY tenant_id  ORDER BY replica_count DESC  LIMIT 10) t1JOIN (SELECT tenant_id, tenant_name  FROM __all_tenant) t2ON t1.tenant_id=t2.tenant_idORDER BY replica_count DESC;+-------------------+-----------+---------------+| tenant_name       | tenant_id | replica_count |+-------------------+-----------+---------------+| wenchao_mysql     |      1100 |        107853 || wenchao_01        |      1088 |         99846 || wenchao_02        |      1104 |         15873 || wenchao_03        |         1 |          3867 || wenchao_04        |      1044 |          3270 || wenchao_05        |      1066 |          2811 || wenchao_06        |      1079 |          2658 || wenchao_07        |      1103 |          2103 || wenchao_08        |      1057 |          2040 || wenchao_09        |      1016 |          1950 |

rows in set (0.13 sec)
查找租户有多少表。

select count(*),svr_Ip from __all_virtual_meta_table where tenant_id=1100 and role=1 group by svr_ip;+----------+-------------+| count(*) | svr_Ip      |+----------+-------------+|   11921  |10.186.64.103||   11868  |10.186.64.104||   12013  |10.186.64.105|+----------+-------------+3 rows in set (0.35 sec)

计算租户须要扩容内存大小。

租户以后分区总数 num=107853/正本数
租户可用内存下限=(1-memstore_limit_percentage)*租户 unit 的内存大小 =(1-0.8)*24GB=4.8GB
单个正本分区所需总内存 partition_mem=128k*(107853/3)+max(1000,(107853/3)/10)*400k=5.75GB

留神：单个正本分区所需总内存 > 租户可用内存下限,租户所需内存超限,须要对租户内存进行扩容。

依据租户内存计算最大分区数量。
单机租户容许创立的最大分区数量=(max_memory-memstore_limit)/partition_mem_n
单机租户容许创立的最大分区数量=(24-24*0.8)/(5.75/(107853/3))=4.8/(5.75/(107853/3))=30011
partition_mem_n：指的是单个分区所需总内存

长期解决计划： 扩容租户内存。

本源治理： 不可能有限扩内存；给出业务方正当的分区数量限度，倡议业务侧正当应用分区表，制订正当的定期清理策略。

总结

根据上述计算得出：单机租户容许创立的最大分区数量为 30011，倡议业务侧留神管制分区数量，免得超限，对业务造成影响。