Starrocks 写入报错 primary key memory usage exceeds the limit

背景

本文基于 StarRocks 3.3.5
单个Starrocks BE配置是 16CU 32GB
在Flink Yaml CDC 任务往 Starrocks写数据的过程中，突然遇到了primary key memory usage exceeds the limit 问题，具体如下：

java.lang.RuntimeException: com.starrocks.data.load.stream.exception.StreamLoadFailException: Transaction prepare failed, db: xxx, table: xxxx, label: flink-960f94fc-6fb1-43e4-aaa1-4c3938241ffa, 
responseBody: {
    "Status": "MEM_LIMIT_EXCEEDED",
    "Message": "primary key memory usage exceeds the limit. tablet_id: 479203, consumption: 15928614825, limit: 15790082457. Memory stats of top five tablets: 4258582(314M)4258578(272M)4258340(230M)2957546(190M)2957590(190M):  be:xxx.xxx.xxx.xxx"
}
errorLog: null
	at com.starrocks.data.load.stream.v2.StreamLoadManagerV2.AssertNotException(StreamLoadManagerV2.java:427)
	at com.starrocks.data.load.stream.v2.StreamLoadManagerV2.write(StreamLoadManagerV2.java:252)
	at com.starrocks.connector.flink.table.sink.v2.StarRocksWriter.write(StarRocksWriter.java:143)
	at org.apache.flink.streaming.runtime.operators.sink.SinkWriterOperator.processElement(SinkWriterOperator.java:182)
	at org.apache.flink.cdc.runtime.operators.sink.DataSinkWriterOperator.processElement(DataSinkWriterOperator.java:178)
	at org.apache.flink.streaming.runtime.tasks.CopyingChainingOutput.pushToOperator(CopyingChainingOutput.java:75)
	at org.apache.flink.streaming.runtime.tasks.CopyingChainingOutput.collect(CopyingChainingOutput.java:50)
	at org.apache.flink.streaming.runtime.tasks.CopyingChainingOutput.collect(CopyingChainingOutput.java:29)
	at org.apache.flink.streaming.api.operators.StreamMap.processElement(StreamMap.java:38)
	at org.apache.flink.streaming.runtime.tasks.OneInputStreamTask$StreamTaskNetworkOutput.emitRecord(OneInputStreamTask.java:245)
	at org.apache.flink.streaming.runtime.io.AbstractStreamTaskNetworkInput.processElement(AbstractStreamTaskNetworkInput.java:217)
	at org.apache.flink.streaming.runtime.io.AbstractStreamTaskNetworkInput.emitNext(AbstractStreamTaskNetworkInput.java:169)
	at org.apache.flink.streaming.runtime.io.StreamOneInputProcessor.processInput(StreamOneInputProcessor.java:68)
	at org.apache.flink.streaming.runtime.tasks.StreamTask.processInput(StreamTask.java:616)
	at org.apache.flink.streaming.runtime.tasks.mailbox.MailboxProcessor.runMailboxLoop(MailboxProcessor.java:231)
	at org.apache.flink.streaming.runtime.tasks.StreamTask.runMailboxLoop(StreamTask.java:1071)
	at org.apache.flink.streaming.runtime.tasks.StreamTask.invoke(StreamTask.java:1020)
	at org.apache.flink.runtime.taskmanager.Task.runWithSystemExitMonitoring(Task.java:959)
	at org.apache.flink.runtime.taskmanager.Task.restoreAndInvoke(Task.java:938)
	at org.apache.flink.runtime.taskmanager.Task.doRun(Task.java:751)
	at org.apache.flink.runtime.taskmanager.Task.run(Task.java:567)
	at java.lang.Thread.run(Thread.java:879)
Caused by: com.starrocks.data.load.stream.exception.StreamLoadFailException: Transaction prepare failed, db: xxx, table: xxx, label: flink-960f94fc-6fb1-43e4-aaa1-4c3938241ffa, 
responseBody: {
    "Status": "MEM_LIMIT_EXCEEDED",
    "Message": "primary key memory usage exceeds the limit. tablet_id: 479203, consumption: 15928614825, limit: 15790082457. Memory stats of top five tablets: 4258582(314M)4258578(272M)4258340(230M)2957546(190M)2957590(190M):  be:xxx.xxx.xxx.xxx"
}
errorLog: null
	at com.starrocks.data.load.stream.TransactionStreamLoader.prepare(TransactionStreamLoader.java:221)
	at com.starrocks.data.load.stream.v2.TransactionTableRegion.commit(TransactionTableRegion.java:247)
	at com.starrocks.data.load.stream.v2.StreamLoadManagerV2.lambda$init$0(StreamLoadManagerV2.java:210)
	... 1 more

除此之外，我们的业务场景就是会 更新 以往 的历史数据，且这样类似的任务有很多。我们的表结构是主键表。

分析

上述报错，其实是BE报出来的，每次进行数据更新的时候，SR都会加载对应的tablet对应的主键索引，导致我们这边的BE占用的内存比较大，如下所示：

。经过分析我发现我们这边的分区是以月维度划分的，而且bucket的个数为2，这样每次写入数据的时候，就会把一个月的的数据的索引加载到内存中，这样就会导致BE的内存占用越来越大，

PARTITION BY date_trunc("month",created_time)
DISTRIBUTED BY HASH(xxx) BUCKETS 2

所以我们进行了bucket调整，

ALTER TABLE xxxx DISTRIBUTED BY HASH(xx) BUCKETS 50;

调整之后，对比了一下BE所占用的内存，如下：

内存占用节约了5GB。

其他

可以通过如下命令查看 索引所占用的内存

curl -XGET -s http://BE:8040/metrics | grep  "update_mem_bytes"

curl -XGET -s http://BE:8040/metrics |grep 'update_primary_index_bytes_total'

具体的指标参考:StarRocks metrics