Horizontal Scaling of Hive Metastore Service at Vivo: Evaluation, TiDB Migration, and Lessons Learned

In this article, the Vivo Internet Big Data team (author Wang Zhiwen) shares the exploration process of horizontally scaling the Hive Metastore Service (HMS) used in their offline data warehouse.

HMS stores all metadata required by compute engines such as Hive, Spark and Presto. Vivo’s Hadoop cluster is built on CDH 5.14.4, and HMS runs version 1.1.0‑cdh5.14.4. Initially the metadata was persisted in MySQL, but rapid business growth pushed the number of rows to the hundred‑million level (e.g., PARTITION_PARAMS ≈ 8.1 × 10⁸, PARTITION_KEY_VALS ≈ 3.5 × 10⁸, PARTITIONS ≈ 1.4 × 10⁸). Under this load the MySQL backend became a performance bottleneck, leading to query time‑outs and even full HMS outages.

To relieve the bottleneck, a horizontal‑scaling solution was required. Two mainstream directions were investigated: (1) sharding the MySQL backend, exemplified by the open‑source Waggle‑Dance gateway, and (2) replacing MySQL with a high‑performance distributed engine, namely TiDB.

Waggle‑Dance provides a unified entry point that routes Metastore client requests to multiple underlying Metastore instances, effectively aggregating several Hive catalogs. Its key advantages include seamless client compatibility, rapid stateless service startup, and the ability to add custom extensions such as authentication or firewall filters.

TiDB is an open‑source distributed HTAP database from PingCAP. It offers horizontal scalability, strong consistency, MySQL‑compatible protocol, and cloud‑native deployment. After testing TiDB 4.x, the team found that it satisfied HMS query‑performance requirements and decided to adopt it as the new metadata store.

Comparative tests on multiple dimensions (performance, scalability, operational complexity, and cost) showed that TiDB outperformed Waggle‑Dance, leading to the final decision to migrate to TiDB.

The migration plan consisted of a pre‑switch data‑synchronisation architecture that kept MySQL as a read‑only fallback while TiDB was populated. Critical TiDB configuration included:

tidb_skip_isolation_level_check = 1 (to avoid MetaException on HMS startup)

tidb_txn_mode = pessimistic (to strengthen transaction consistency)

Transaction size limit = 3 GB (adjustable per workload)

Connection limit = 3000 (adjustable per workload)

After the data‑sync reached consistency, all HMS nodes were stopped, the TiDB primary was switched to writable, and HMS services were restarted with the new JDBC URL.

Post‑migration performance indicators demonstrated:

100 % compatibility with HQL syntax.

Average query latency improved by ~15 % and DDL latency reduced by 80 %.

Disk usage stayed below 10 % and CPU average around 12 %.

Linear horizontal scalability with one‑click scaling.

Robust disaster‑recovery via TiDB binlog and HA options (LVS/HAProxy).

Various API latency charts (omitted here) confirmed the gains.

Issues and solutions encountered during the rollout:

4.1 Primary‑key conflict when rolling back to MySQL – TiDB’s auto‑increment cache caused gaps that made the SEQUENCE_TABLE values diverge from the actual maximum IDs. The fix was to reset SEQUENCE_TABLE entries to the current maximum values before switching back.

4.2 Index selection for PARTITION_KEY_VALS – The following three‑level partition query originally suffered from full‑table scans:

#以下查询为查询三级分区表模板，且每级分区都有过来条件
SELECT PARTITIONS.PART_ID
FROM PARTITIONS
INNER JOIN TBLS ON PARTITIONS.TBL_ID = TBLS.TBL_ID
AND TBLS.TBL_NAME = '${TABLE_NAME}'
INNER JOIN DBS ON TBLS.DB_ID = DBS.DB_ID
AND DBS.NAME = '${DB_NAME}'
INNER JOIN PARTITION_KEY_VALS FILTER0 ON FILTER0.PART_ID = PARTITIONS.PART_ID
AND FILTER0.INTEGER_IDX = ${INDEX1}
INNER JOIN PARTITION_KEY_VALS FILTER1 ON FILTER1.PART_ID = PARTITIONS.PART_ID
AND FILTER1.INTEGER_IDX = ${INDEX2}
INNER JOIN PARTITION_KEY_VALS FILTER2 ON FILTER2.PART_ID = PARTITIONS.PART_ID
AND FILTER2.INTEGER_IDX = ${INDEX3}
WHERE FILTER0.PART_KEY_VAL = '${PART_KEY}'
AND CASE
  WHEN FILTER1.PART_KEY_VAL <> '__HIVE_DEFAULT_PARTITION__' THEN CAST(FILTER1.PART_KEY_VAL AS decimal(21,0))
  ELSE NULL
END = 10
AND FILTER2.PART_KEY_VAL = '068';

Adding an index on PARTITION_KEY_VAL caused the optimizer to choose the index even when the column’s cardinality was low, leading to inefficient scans for daily partitions (≈260 k rows per day). The team decided not to deploy the index in production.

Another typical daily‑partition query (two‑level) was also shown, but the same conclusion applied.

4.3 TiDB memory spikes – The Dashboard’s slow‑query analysis loaded all slow‑query logs into memory, causing sudden memory growth and occasional leaks. Mitigations included provisioning larger‑memory nodes, raising the slow‑query threshold to 3 s, and rotating slow‑query logs regularly.

4.4 locate() function causing full scans on TiKV – Queries that used locate() on PART_NAME could not use indexes, leading to full scans and high TiKV load. Rewriting the condition with LIKE enabled the UNIQUEPARTITION index, halving TiKV CPU usage and reducing network traffic from ~1 GB to ~200 MB.

4.5 get_all_functions API slowdown – The original JDO‑based implementation joined multiple tables per function, resulting in 40‑90 s latency. Replacing it with a direct‑SQL query cut the response time to under 1 s:

select FUNCS.FUNC_NAME,
  DBS.NAME,
  FUNCS.CLASS_NAME,
  FUNCS.OWNER_NAME,
  FUNCS.OWNER_TYPE,
  FUNCS.CREATE_TIME,
  FUNCS.FUNC_TYPE,
  FUNC_RU.RESOURCE_URI,
  FUNC_RU.RESOURCE_TYPE
from FUNCS
left join FUNC_RU on FUNCS.FUNC_ID = FUNC_RU.FUNC_ID
left join DBS on FUNCS.DB_ID = DBS.DB_ID;

Conclusion – After more than a year of production use, TiDB’s memory usage stays below 10 %, TiKV CPU remains stable (<30 cores), and HMS API performance meets business needs. The architecture will continue to support Vivo’s big‑data platform, with ongoing evaluation of newer TiDB versions and further HMS optimisations.