SoFunction
Updated on 2025-03-02

Analysis on the solution of OceanBase table partition number exceeding limit error

background

What is the maximum number of partitions allowed by a stand-alone tenant? The author calculates the maximum number of partitions allowed to be created by a stand-alone machine through partition limit error troubleshooting.

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

An error was reported when creating a table. Although it was an internal error, the error message refers to: too many partitions were created.

[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 = 'Logistics Order Form'
MySQL [lss]> ERROR 1499 (HY000): Too many partitions (including subpartitions) were defined

Next, let’s analyze the cause of the problem.

Troubleshooting

2.1 Check parameters

  • Check that the maximum number of partitions that can be created on each OBServer is currently 500,000.
[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 |
+-------+----------+---------------+----------+-------------------------------+-----------+--------+--------------+---------------------------------------------+-------------+----------+---------------+---------+---------+-------------------+
  • Check the sum of the current number of partitions, and this limit is not currently exceeded (500000).
MySQL [oceanBase]> select count(*) from v$partition;
+----------+
| count(*) |
+----------+
|   421485 |
+----------+

2.2 Check the recycling bin

  • Check if the recycling bin is open?
[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)
  • Check if there are undeleted partition tables in the recycle bin?
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)

If it exists, it is necessary to communicate with the business side whether it can be cleaned up. After cleaning up the tables in the Recycle Bin, it was found that the number of partitioned tables decreased, but the creation of the table still reported an error.

Check how many days an object is retained in the recycle bin.

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)

Configuration Itemsrecyclebin_object_expire_timeThe value of  is described as follows:

  • When its value is 0s, the automatic Purge recycling bin function is turned off.
  • When its value is not 0s, it means that the Schema object that entered the recycling bin a period of time ago is recycled.

2.3 Check the tenant memory

Find the 10 tenants with the largest number of partitions.

[root@observer04 ~]# mysql -h10.186.64.125 -P2883 -uroot@sys#hwc_cluster:1682755171 -p"xxxx"
// 1. Find the 10 tenants with the largest number of partitionsSELECT 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)

  • Find out how many tables the tenant has.
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)
  • Calculate the memory size of the tenant.
  • The current total number of partitions of tenants num=107853/number of replicas
  • The upper limit of available memory for tenants = (1-memstore_limit_percentage)*The memory size of tenant unit = (1-0.8)*24GB=4.8GB
  • Total memory required for a single replica partition partition_mem=128k*(107853/3)+max(1000,(107853/3)/10)*400k=5.75GB

Note: Total memory required for a single replica partition > The upper limit of available memory for tenants, the memory required for tenants exceeds the limit, and the tenant memory needs to be expanded.

  • Calculate the maximum number of partitions based on tenant memory.
  • The maximum number of partitions allowed by a stand-alone tenant = (max_memory-memstore_limit)/partition_mem_n
  • The maximum number of partitions allowed by a stand-alone tenant = (24-24*0.8)/(5.75/(107853/3))=4.8/(5.75/(107853/3))=30011
  • partition_mem_n: refers to the total memory required for a single partition

Temporary treatment plan:Expand tenant memory.

Root governance:It is impossible to expand memory infinitely; give a reasonable limit on the number of partitions for the business party, and it is recommended that the business side use the partition table reasonably and formulate a reasonable regular cleaning strategy.

Summarize

Based on the above calculation, it is concluded that the maximum number of partitions allowed by a stand-alone tenant is 30011. It is recommended that the business side pay attention to controlling the number of partitions to avoid exceeding the limit and affecting the business.

The above is the detailed content of the analysis of the OceanBase table building partition number exceeding the limit error. For more information about OceanBase table building partition number exceeding the limit error, please pay attention to my other related articles!