QuestDB执行数据插入时频繁出现Table Busy错误咨询

错误详情 #

Exception in thread "pool-3-thread-1" org.springframework.jdbc.UncategorizedSQLException:

Error updating database. Cause: org.postgresql.util.PSQLException: ERROR: table busy [reason=insert]
The error may involve com.secusoft.mapper.quest.CameraAccessDetailsMapper.insert-Inline
The error occurred while setting parameters
SQL: insert into camera_access_details_1_20211016 values (72, to_timestamp('2021-10-16 22:06:00','yyyy-MM-dd HH:mm:ss'),to_timestamp('2021-10-16 22:07:00','yyyy-MM-dd HH:mm:ss'), 0, 0)
Cause: org.postgresql.util.PSQLException: ERROR: table busy [reason=insert]
; uncategorized SQLException for SQL []; SQL state [00000]; error code [0]; ERROR: table busy [reason=insert]; nested exception is org.postgresql.util.PSQLException: ERROR: table busy [reason=insert]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
...

核心触发原因 #

QuestDB针对时序场景做了存储架构优化，默认单表写入采用串行化机制，同一时间仅允许一个写入操作持有表的写入权限，出现该报错均为表写入权限竞争导致，常见触发场景如下：

• 多并发线程/进程同时通过PostgreSQL协议向同一张表发起单条插入请求，写入请求排队超时
• 同一时间点同一张表既有批量写入任务，又有实时单条写入任务，出现锁竞争
• 表正在执行ALTER TABLE、OPTIMIZE TABLE、分区删除/调整等DDL操作，这类操作会持有表独占锁，阻塞所有写入请求
• 服务器磁盘IO性能不足、内存占满导致写入刷盘速度慢，上一批写入还没执行完成，新的写入请求已经到达，出现写入堆积

排查与解决办法 #

1. 调整写入并发逻辑
   确认业务代码中是否存在多线程同时向目标表写入的情况，QuestDB的PostgreSQL协议写入不支持多并发单条插入，要么将写入并发数调整为单线程，要么将单条插入聚合为批量插入后再提交。
2. 优化写入方式
   优先将单条INSERT语句替换为批量INSERT，单次提交100~1000行数据（可根据单行数据大小调整），大幅降低请求频次，减少锁竞争。如果必须保留多并发写入，建议改用QuestDB原生的InfluxDB Line Protocol(ILP)协议写入，ILP天生支持多并发无锁写入，性能远高于PostgreSQL协议写入。
3. 错开DDL与重查询执行时间
   排查报错时间点是否有DDL操作、大跨度时序聚合查询在执行，这类操作会抢占IO资源或者持有表锁，建议将这类操作调整到业务低峰期执行，同时对高资源消耗的查询做限流。
4. 优化底层硬件与配置
   检查服务器磁盘IO使用率、内存占用，如果机械磁盘IO成为瓶颈，可升级为SSD或NVME磁盘；也可以调整QuestDB配置项cairo.writer.async.flush开启异步刷盘，减少写入阻塞概率。
5. 临时降级方案
   如果业务侧无法快速调整写入逻辑，可在代码中增加重试机制，遇到table busy错误后等待10~50ms再重试，注意控制重试次数，避免请求雪崩。

内容的提问来源于stack exchange，提问作者Learzhu