我要评论背景
oceanbase 单机租户允许创建的最大分区数是多少？作者通过分区超限错误排查，计算出单机允许创建的最大分区数量。
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，建议业务侧注意控制分区数量，以免超限，对业务造成影响。
以上就是oceanbase建表分区数超限报错解决分析的详细内容，更多关于oceanbase建表分区数超限报错的资料请关注代码网其它相关文章！
发表评论