OceanBase建表分區(qū)數(shù)超限報錯解決分析

更新時間：2023年09月07日 11:27:12 作者：愛可生云數(shù)據(jù)庫

這篇文章主要為大家介紹了OceanBase建表分區(qū)數(shù)超限報錯解決分析,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪

背景

OceanBase 單機租戶允許創(chuàng)建的最大分區(qū)數(shù)是多少？作者通過分區(qū)超限錯誤排查，計算出單機允許創(chuàng)建的最大分區(qū)數(shù)量。

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

創(chuàng)建表報錯，雖然是內(nèi)部錯誤，但是錯誤信息是指：創(chuàng)建了太多了分區(qū)。

[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 檢查參數(shù)

檢查每個 OBServer 上可以創(chuàng)建最大的分區(qū)數(shù)量，當前是 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 |
+-------+----------+---------------+----------+-------------------------------+-----------+--------+--------------+---------------------------------------------+-------------+----------+---------------+---------+---------+-------------------+

檢查當前分區(qū)數(shù)量的和，目前并沒有超過這個限制（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)

檢查回收站中是否存在未刪除的分區(qū)表？

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)

如果存在，需要和業(yè)務(wù)側(cè)溝通是否可以清理?；厥照镜谋砬謇砗螅l(fā)現(xiàn)分區(qū)表數(shù)量減少，但是創(chuàng)建表依舊報錯。

查看回收站中中對象保留天數(shù)。

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 時，表示關(guān)閉自動 Purge 回收站功能。
當其值不為 0s 時，表示回收一段時間前進入回收站的 Schema 對象。

2.3 檢查租戶內(nèi)存

找到分區(qū)數(shù)最多的 10 個租戶。

[root@observer04 ~]# mysql -h10.186.64.125 -P2883 -uroot@sys#hwc_cluster:1682755171 -p"xxxx"
// 1. 找到分區(qū)數(shù)最多的 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)

計算租戶需要擴容內(nèi)存大小。

租戶當前分區(qū)總數(shù) num=107853/副本數(shù)
租戶可用內(nèi)存上限=(1-memstore_limit_percentage)*租戶 unit 的內(nèi)存大小 =(1-0.8)*24GB=4.8GB
單個副本分區(qū)所需總內(nèi)存 partition_mem=128k*(107853/3)+max(1000,(107853/3)/10)*400k=5.75GB

注意：單個副本分區(qū)所需總內(nèi)存 > 租戶可用內(nèi)存上限,租戶所需內(nèi)存超限,需要對租戶內(nèi)存進行擴容。

根據(jù)租戶內(nèi)存計算最大分區(qū)數(shù)量。
單機租戶允許創(chuàng)建的最大分區(qū)數(shù)量=(max_memory-memstore_limit)/partition_mem_n
單機租戶允許創(chuàng)建的最大分區(qū)數(shù)量=(24-24*0.8)/(5.75/(107853/3))=4.8/(5.75/(107853/3))=30011
partition_mem_n：指的是單個分區(qū)所需總內(nèi)存

臨時處理方案： 擴容租戶內(nèi)存。

根源治理： 不可能無限擴內(nèi)存；給出業(yè)務(wù)方合理的分區(qū)數(shù)量限制，建議業(yè)務(wù)側(cè)合理使用分區(qū)表，制定合理的定期清理策略。