Magnus: Intelligent Data Optimization Service for Iceberg Tables in Bilibili's Lakehouse Platform

Background

Bilibili's lake‑warehouse integrated platform stores about 60 PB of data and serves BI reports, metric services, A/B testing, audience selection, and log analysis. Daily query volume exceeds 4 million, with a P99 response time of roughly 3 seconds. The platform uses Apache Iceberg as the table format, leveraging its independent metadata management and strong community support in Spark, Flink, and Trino.

Iceberg Advantages

Flexible and safe schema evolution.

Transactional support and multi‑version data.

File‑level metrics enable efficient predicate push‑down.

Magnus Service Overview

Magnus is a self‑developed intelligent data‑optimization service for Iceberg tables. Its core functions include metadata query and display, table management, data optimization, and intelligent recommendation.

1. Table Management

Magnus schedules maintenance operations to reduce storage pressure and improve metadata query performance. The three main operations are:

ExpireSnapshots : Periodically removes obsolete snapshots that are no longer needed for rollback, typically on an hourly basis.

DeleteOrphanFiles : Cleans up files not referenced by any metadata, usually on a daily basis.

RewriteManifests : Rewrites large or fragmented manifest files to improve the planFiles cost. It addresses three scenarios: oversized manifests from a single commit, many small manifests from frequent writes, and manifests that do not group files of the same partition.

2. Data Optimization

Data optimization is the core of Magnus. It implements multi‑dimensional distribution, various indexes (BloomFilter, Bitmap, Token‑based, N‑gram), and aggregation indexes (pre‑computed cubes). Over 50,000 optimization tasks are submitted daily with a success rate above 99.9% and a latency of 15‑30 minutes, enabling second‑level responses for million‑scale queries.

The optimization workflow consists of task generation and task submission.

2.1 Task Generation

When an ETL job commits data, a commit event containing table name, snapshot ID, and other metadata is sent to Magnus. Based on the changed partitions and file statistics, Magnus creates optimization tasks. Five optimization types are supported: small‑file merging, sorting, distribution, index creation, and aggregation index creation. A hierarchical (Level 0‑2) strategy balances latency and effectiveness:

Level 0: Small‑file merge only, triggered by file‑count thresholds.

Level 1: Sorting and distribution, triggered when data size exceeds 2 GB.

Level 2: Higher‑cost optimizations applied after every ten Level 1 runs to avoid over‑optimization.

2.2 Task Submission

Generated tasks are submitted to Spark via a task submitter that maintains a priority queue. The submitter enforces:

Service‑level concurrency control for spark‑submit processes.

Table‑level concurrency limits.

Total resource caps to protect ETL workloads.

Priority for real‑time data optimization.

2.3 Optimization Result Display

Optimization metrics (file count, size, optimization ratio, last write time) are shown at the partition level to monitor performance improvements.

3. Intelligent Recommendation

Magnus automatically recommends optimization configurations based on historical query patterns, reducing the expertise required from users.

3.1 Query Collection

Real‑time query logs are collected from Trino and stored in an Iceberg table. Each query record includes basic info (timestamp, user, SQL), performance metrics (latency, scanned bytes), query patterns (filters, order‑by, aggregations), and data‑filter metrics.

3.2 Analysis & Recommendation

Aggregated query statistics are combined with partition‑level table statistics. Rule‑based logic derives a set of recommended optimizations (distribution, indexes, aggregation). Recommendations are refreshed weekly per table.

3.3 Outcomes

Over 30 tables have been optimized via the recommendation feature, achieving a 28% reduction in total scanned data over 30 days, with more than 60% of tables reducing scan volume by over 30%.

4. Future Outlook

The team plans to improve the robustness of commit‑event reporting, enable horizontal scaling of the task submitter, merge cross‑table optimization tasks, and push task status to users for better risk alerts. Additionally, the recommendation engine will incorporate richer statistics and explore machine‑learning models to boost accuracy for complex query patterns.