SQL 查询导致磁盘空间不足报错 -4184

问题描述

OCP 监控平台告警报磁盘剩余空间不足 disk is almost full(ret=-4184。具体信息如下：

生成时间：xxxx-xx-xx Txx:xx:xx+08:00  ob_cluster=dp_ob_cluster_01:host=xx.xx.xx.xx:server_type=observer:ob_error_code=4184 
详情：集群：dp_ob_cluster_01 主机：xx.xx.xx.xx
日志类型：observer 错误码：4184 TraceId：Y0-0000000000000000 日志名称：OB_CS_OUTOF_DISK_SPACE 日志级别：error 日志详情：[xxxx-xx-xx xx:xx:xx.xxxxx] ERROR [STORAGE] check_disk_full (ob_store_file.cpp:1147) [37222][868][xxxxx-xxxxx] [lt=10] [dc=0] disk is almost full(ret=-4184, required_size=0, required_count=0, free_count=137037, used_percent=90, super_block_={header:{super_block_size:135, version:2, magic:1012, attr:0}, content:{create_timestamp:xxxxx, modify_timestamp:xxxxx, macro_block_size:2097152, total_macro_block_count:1250045, free_macro_block_count:137037, total_file_size:2621534371840, replay_start_point:ObLogCursor{file_id=106, log_id=48317862, offset=23781376}, macro_block_meta:{block_index:637952, log_seq:0, file_id:0, file_size:0}, partition_meta:{block_index:638566, log_seq:48127611, file_id:0, file_size:0}, table_mgr_meta:{block_index:638832, log_seq:48317860, file_id:0, file_size:0}, tenant_config_meta:{block_index:638840, log_seq:16233199, file_id:0, file_size:0}}}) BACKTRACE:0xcc24b9a 0xcb43f12 0x6fff1ee 0x6fffa89 0x6fe78f1 0x6fdba45 0x6fdbacb 0x80627be 0xcb7cc83 0x37df1f9 0xc9c3e87 0xx9c0d70 0xc9bfc0f

适用版本

OceanBase 数据库 V2.x、V3.x 版本。

问题原因

1. 产生告警的原因说明。

   当 used_percent 大于 data_disk_usage_limit_percentage (默认 90) 时，就会触发上述告警。其中 used_percent = 1 - 空闲的宏块数量（free_count）/文件中总的宏块数量。一天只会告警一次。 ​

2. 查看宏块的使用情况。

   通过 observer.log 日志 搜索 free_block_cnt 关键字，可以看到宏块剩余的变化，从日志中看到短时间内，宏块从剩余 2 万多到剩余 1 万多宏块。

   observer.log:xxxxx:[xxxx-xx-xx xx:xx:xx.xxxxx] INFO  [STORAGE] ob_store_file.cpp:670 [7431][2922][xxxxx-xxxxx] [lt=11] [dc=0] Async write macro block, (macro_block_id=1095908-18-0-0[xxxxx], free_block_cnt=226825)
   observer.log:xxxxx:[xxxx-xx-xx xx:xx:xx.xxxxx] INFO  [STORAGE] ob_store_file.cpp:670 [7431][2922][xxxxx-xxxxx] [lt=12] [dc=0] Async write macro block, (macro_block_id=1095926-18-0-0[xxxxx], free_block_cnt=226824)
   .............
   observer.log:xxxxx:[xxxx-xx-xx xx:xx:xx.xxxxx] INFO  [STORAGE] ob_store_file.cpp:670 [7431][2922][xxxxx-xxxxx] [lt=12] [dc=0] Async write macro block, (macro_block_id=710746-19-0-0[xxxxx], free_block_cnt=115558)
   observer.log:xxxxx:[xxxx-xx-xx xx:xx:xx.xxxxx] INFO  [STORAGE] ob_store_file.cpp:670 [7431][2922][xxxxx-xxxxx] [lt=16] [dc=0] Async write macro block, (macro_block_id=710747-20-0-0[xxxxx], free_block_cnt=115557)
   

3. 观察该 trace 打开宏块情况。

   查看调用栈信息。

   observer.log:xxxxx:[xxxx-xx-xx xx:xx:xx.xxxxx] INFO  [STORAGE] ob_macro_file.cpp:230 [7431][2922][xxxxx-xxxxx] [lt=3] [dc=0] succeed to open macro file.(fd=150282, common::lbt()="0xcc24b9a 0x7cadfb4 0x7ca7a99 0x7caa8c9 0xa3434d7 0xa3438fc 0xa343f12 0xa3442ab 0xab8d866 0xab8b1f9 0xa883c34 0xaf500bf 0xb118540 0x931c418 0x931cdf6 0x9de61fb 0xcd73192 0x9e78952 0x9e70ffa 0x9e7272b 0xc9c3e87 0xc9c0d70 0xc9bfc0f")
   

   使用 addr2line 工具解析堆栈信息。

   addr2line -pCfe /home/admin/oceanbase/bin/observer 0xcc24b9a 0x7cadfb4 0x7ca7a99 0x7caa8c9 0xa3434d7 0xa3438fc 0xa343f12 0xa3442ab 0xab8d866 0xab8b1f9 0xa883c34 0xaf500bf 0xb118540 0x931c418 0x931cdf6 0x9de61fb 0xcd73192 0x9e78952 0x9e70ffa 0x9e7272b 0xc9c3e87 0xc9c0d70 0xc9bfc0f
   

   输出结果如下。

   oceanbase::common::lbt() at ??:?
   oceanbase::blocksstable::ObMacroFile::init(int, oceanbase::common::ObIAllocator&)::{lambda(char const*)#1}::operator()(char const*) const [clone .constprop.266] at ob_macro_file.cpp:?
   oceanbase::blocksstable::ObMacroFile::init(int, oceanbase::common::ObIAllocator&) at ??:?
   oceanbase::blocksstable::ObMacroFileManager::open(int&) at ??:?
   oceanbase::sql::ObChunkRowStore::write_file(void*, long) at ??:?
   oceanbase::sql::ObChunkRowStore::dump(bool, bool) at ??:?
   oceanbase::sql::ObChunkRowStore::switch_block(long) at ??:?
   oceanbase::sql::ObChunkRowStore::add_row(oceanbase::common::ObNewRow const&, oceanbase::sql::ObChunkRowStore::StoredRow**) at ??:?
   oceanbase::sql::ObHashGroupBy::load_data(oceanbase::sql::ObExecContext&) const at ??:?
   oceanbase::sql::ObHashGroupBy::inner_get_next_row(oceanbase::sql::ObExecContext&, oceanbase::common::ObNewRow const*&) const at ??:?
   oceanbase::sql::ObPhyOperator::get_next_row(oceanbase::sql::ObExecContext&, oceanbase::common::ObNewRow const*&) const at ??:?
   oceanbase::sql::ObExecuteResult::get_next_row(oceanbase::sql::ObExecContext&, oceanbase::common::ObNewRow const*&) at ??:?
   oceanbase::sql::ObResultSet::get_next_row(oceanbase::common::ObNewRow const*&) at ??:?
   oceanbase::observer::ObSyncPlanDriver::response_query_result(oceanbase::sql::ObResultSet&, bool, bool&, long) at ??:?
   oceanbase::observer::ObSyncPlanDriver::response_result(oceanbase::observer::ObMySQLResultSet&) at ??:?
   oceanbase::observer::ObMPQuery::process() at ??:?
   oceanbase::rpc::frame::ObReqProcessor::run() at ??:?
   oceanbase::omt::ObWorkerProcessor::process(oceanbase::rpc::ObRequest&) at ??:?
   oceanbase::omt::ObThWorker::worker(long&, long&, int&) at ??:?
   oceanbase::omt::ObThWorker::run(long) at ??:?
   oceanbase::lib::CoSetSched::Worker::run() at ??:?
   oceanbase::lib::CoRoutine::__start(boost::context::detail::transfer_t) at ??:?
   finish at co_local_storage.cpp:?
   

   调用栈信息显示是在执行 OceanBase HashGroup By 时发生了磁盘写入的操作，可以推断是在 SQL 执行的过程中产生了临时数据的落盘操作，怀疑有大 SQL 执行。

解决方法

• 如果是 SQL 计划不优，需要优化 SQL。

• 应急方法: 通过 hint 提示 /+no_merge/ 规避优化器估行不准导致中间结果放大导致落盘临时文件。