Resolving ERROR 1499: Too Many Partitions in OceanBase/MySQL

1 Background

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

The table creation fails because the system reports that too many partitions have been 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

We then analyze the cause.

2 Investigation

2.1 Check Parameters

Check the maximum number of partitions per OBServer, 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 current total partition count, which does not exceed the limit (421,485).

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

2.2 Check Recycle Bin

Is the recycle bin enabled?

[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)

Are there any dropped 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 such objects exist, coordinate with the business side to clean them. After cleaning, the partition count drops, but the creation error still occurs.

Check the recycle‑bin object expiration time.

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
edit_level: DYNAMIC_EFFECTIVE
1 row in set (0.02 sec)

The parameter recyclebin_object_expire_time set to 0s disables automatic purge of the recycle bin.

0s → auto purge off.

Non‑zero → purge objects older than the specified time.

2.3 Check Tenant Memory

Find the top 10 tenants with the most partitions.

[root@observer04 ~]# mysql -h10.186.64.125 -P2883 -uroot@sys#hwc_cluster:1682755171 -p"xxxx"

// 1. Find top 10 tenants by partition count
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 |
+-------------------+-----------+---------------+
10 rows in set (0.13 sec)

Count tables per tenant.

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 required memory for the tenant.

Tenant current total partitions : num = 107,853 / replica count

Tenant usable memory limit : (1‑memstore_limit_percentage) * tenant unit memory = (1‑0.8) * 24GB = 4.8GB

Memory needed per replica partition : partition_mem = 128k*(107,853/3) + max(1000,(107,853/3)/10)*400k = 5.75GB

Note: required memory per replica partition > tenant usable memory limit, so the tenant's memory must be expanded.

Calculate the maximum number of partitions a tenant can create based on memory.

Maximum partitions per tenant on a single machine : (max_memory‑memstore_limit) / partition_mem_n

Result ≈ (24‑24*0.8) / (5.75 / (107,853/3)) = 4.8 / (5.75 / (107,853/3)) = 30,011 partitions

partition_mem_n denotes the total memory required for a single partition.

Temporary solution: Expand tenant memory.

Root cause mitigation: Unlimited memory expansion is not feasible; advise the business to set reasonable partition limits, use partitions responsibly, and establish regular cleanup policies.

3 Summary

Based on the calculations, a single‑machine tenant can create at most 30,011 partitions; the business side should monitor and control partition numbers to avoid exceeding this limit and impacting services.