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 oceanBase
MySQL [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_count FROM (SELECT tenant_id, COUNT(*) AS replica_count FROM __all_virtual_partition_info GROUP BY tenant_id ORDER BY replica_count DESC LIMIT 10) t1 JOIN (SELECT tenant_id, tenant_name FROM __all_tenant) t2 ON t1.tenant_id=t2.tenant_id ORDER 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,倡议业务侧留神管制分区数量,免得超限,对业务造成影响。