--- title: SQL 查询导致磁盘空间不足报错 -4184-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 SQL 查询导致磁盘空间不足报错 -4184相关的常见问题和使用技巧，帮助您快速解决 SQL 查询导致磁盘空间不足报错 -4184的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # SQL 查询导致磁盘空间不足报错 -4184 更新时间：2023-12-07 03:16 适用版本： V2.2.x、V3.1.x、V3.2.x 内容类型：Troubleshoot ## 问题描述 OCP 监控平台告警报磁盘剩余空间不足 `disk is almost full(ret=-4184`。具体信息如下： ```shell 生成时间：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 万多宏块。 ```shell 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 打开宏块情况。 查看调用栈信息。 ```shell 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 工具解析堆栈信息。 ```shell 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 ``` 输出结果如下。 ```shell 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*/ 规避优化器估行不准导致中间结果放大导致落盘临时文件。 Previous [SQL 执行报错 4002 invalid argument，observer.log 里对应 WARN 日志 ob_tmp_file 落盘 dir_id 为负](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000008211) Next [为什么 OceanBase 数据库的单表查询时 ORDER BY PK DESC 比 ORDER BY PK ASC 执行耗时长？](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000624725) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线