查询数据 limit 分页直接报错order by id limit

Viewed 52

SELECT * FROM hxk_ai_series_report_markrib order by id limit 56000,1000;

SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = (192.168.30.230)[MEM_LIMIT_EXCEEDED]Process memory not enough, cancel top memory overcommit query: <Query#Id=430e7d4fcb5744a4-98c1de41bebe6834> consumption 3.16 GB, backend 192.168.30.230, process memory used 17.70 GB exceed soft limit 50.44 GB or sys available memory 6.16 GB less than warning water mark 6.23 GB.. Execute again after enough memory, details see be.INFO.
有时候报错有时候能查询出来数据,没有任何查询的情况下top 查看 be占用还16G

ucceed to do cumulative compaction is_vertical=1. tablet=54271, output_version=[4684-5264], current_max_version=5264, disk=/home/doris/apache-doris-3.0.1-bin-x64/be/storage, segments=0, input_rowset_size=0, output_rowset_size=0, input_row_num=0, output_row_num=0, filtered_row_num=0, merged_row_num=0. elapsed time=0.000912s. cumulative_compaction_policy=size_based, compact_row_per_second=0
I20241114 11:12:22.750419 14304 workload_group_manager.cpp:238]
Workload Group normal: mem limit: 16.81 GB, mem used: 2.31 GB, weighted mem used: 12.54 GB, used ratio: 0.745978718149506, query count: 6, query_weighted_mem_limit: 2.80 GB
Query Memory Summary:
MemTracker Label=Load#Id=104ad110dfc17af3-d7b0a92f0f9cb68a, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=90be7c18824148c8-bbdf05232333d7a9, Parent Label=-, Used=889.69 MB, WeightedUsed=4.72 GB, Peak=3.93 GB
MemTracker Label=Load#Id=5b4115b7a3a097c8-d3a80373e4f62c9c, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=eb485d5d61dc1b58-f5f78b11a673068d, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=104bc5f6d08a145d-6d68c7be419d57a9, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=cfb9bd4eef8e4a65-8414221aba6e0498, Parent Label=-, Used=1.44 GB, WeightedUsed=7.82 GB, Peak=3.93 GB
I20241114 11:12:23.754521 14304 workload_group_manager.cpp:238]
Workload Group normal: mem limit: 16.81 GB, mem used: 2.31 GB, weighted mem used: 12.54 GB, used ratio: 0.7460016481639233, query count: 6, query_weighted_mem_limit: 2.80 GB
Query Memory Summary:
MemTracker Label=Load#Id=104ad110dfc17af3-d7b0a92f0f9cb68a, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=90be7c18824148c8-bbdf05232333d7a9, Parent Label=-, Used=889.69 MB, WeightedUsed=4.72 GB, Peak=3.93 GB
MemTracker Label=Load#Id=5b4115b7a3a097c8-d3a80373e4f62c9c, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=eb485d5d61dc1b58-f5f78b11a673068d, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=104bc5f6d08a145d-6d68c7be419d57a9, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=cfb9bd4eef8e4a65-8414221aba6e0498, Parent Label=-, Used=1.44 GB, WeightedUsed=7.82 GB, Peak=3.93 GB
I20241114 11:12:24.758520 14304 workload_group_manager.cpp:238]
Workload Group normal: mem limit: 16.81 GB, mem used: 2.31 GB, weighted mem used: 12.54 GB, used ratio: 0.7460018484642766, query count: 6, query_weighted_mem_limit: 2.80 GB
Query Memory Summary:
MemTracker Label=Load#Id=104ad110dfc17af3-d7b0a92f0f9cb68a, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=90be7c18824148c8-bbdf05232333d7a9, Parent Label=-, Used=889.69 MB, WeightedUsed=4.72 GB, Peak=3.93 GB
MemTracker Label=Load#Id=5b4115b7a3a097c8-d3a80373e4f62c9c, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=eb485d5d61dc1b58-f5f78b11a673068d, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=104bc5f6d08a145d-6d68c7be419d57a9, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=cfb9bd4eef8e4a65-8414221aba6e0498, Parent Label=-, Used=1.44 GB, WeightedUsed=7.82 GB, Peak=3.93 GB
I20241114 11:12:25.762378 14304 workload_group_manager.cpp:238]
Workload Group normal: mem limit: 16.81 GB, mem used: 2.31 GB, weighted mem used: 12.54 GB, used ratio: 0.7459984274051458, query count: 6, query_weighted_mem_limit: 2.80 GB
Query Memory Summary:
MemTracker Label=Load#Id=104ad110dfc17af3-d7b0a92f0f9cb68a, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=90be7c18824148c8-bbdf05232333d7a9, Parent Label=-, Used=889.69 MB, WeightedUsed=4.72 GB, Peak=3.93 GB
MemTracker Label=Load#Id=5b4115b7a3a097c8-d3a80373e4f62c9c, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=eb485d5d61dc1b58-f5f78b11a673068d, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=104bc5f6d08a145d-6d68c7be419d57a9, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=cfb9bd4eef8e4a65-8414221aba6e0498, Parent Label=-, Used=1.44 GB, WeightedUsed=7.82 GB, Peak=3.93 GB
I20241114 11:12:26.771198 14304 workload_group_manager.cpp:238]
Workload Group normal: mem limit: 16.81 GB, mem used: 2.31 GB, weighted mem used: 12.54 GB, used ratio: 0.7460028800997244, query count: 6, query_weighted_mem_limit: 2.80 GB
Query Memory Summary:
MemTracker Label=Load#Id=104ad110dfc17af3-d7b0a92f0f9cb68a, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=90be7c18824148c8-bbdf05232333d7a9, Parent Label=-, Used=889.69 MB, WeightedUsed=4.72 GB, Peak=3.93 GB
MemTracker Label=Load#Id=5b4115b7a3a097c8-d3a80373e4f62c9c, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=eb485d5d61dc1b58-f5f78b11a673068d, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=104bc5f6d08a145d-6d68c7be419d57a9, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=cfb9bd4eef8e4a65-8414221aba6e0498, Parent Label=-, Used=1.44 GB, WeightedUsed=7.82 GB, Peak=3.93 GB
I20241114 11:12:27.320567 13162 wal_manager.cpp:480] Scheduled(every 10s) WAL info: [/home/doris/apache-doris-3.0.1-bin-x64/be/storage/wal: limit 483444545126 Bytes, used 0 Bytes, estimated wal bytes 0 Bytes, available 483444545126 Bytes.];
I20241114 11:12:27.775192 14304 workload_group_manager.cpp:238]
Workload Group normal: mem limit: 16.81 GB, mem used: 2.31 GB, weighted mem used: 12.54 GB, used ratio: 0.7459968036760929, query count: 6, query_weighted_mem_limit: 2.80 GB
Query Memory Summary:
MemTracker Label=Load#Id=104ad110dfc17af3-d7b0a92f0f9cb68a, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=90be7c18824148c8-bbdf05232333d7a9, Parent Label=-, Used=889.69 MB, WeightedUsed=4.72 GB, Peak=3.93 GB
MemTracker Label=Load#Id=5b4115b7a3a097c8-d3a80373e4f62c9c, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=eb485d5d61dc1b58-f5f78b11a673068d, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Load#Id=104bc5f6d08a145d-6d68c7be419d57a9, Parent Label=-, Used=0, WeightedUsed=0, Peak=0
MemTracker Label=Query#Id=cfb9bd4eef8e4a65-8414221aba6e0498, Parent Label=-, Used=1.44 GB, WeightedUsed=7.82 GB, Peak=3.93 GB
I20241114 11:12:28.380865

1 Answers

这是内存不足的问题。sys available memory 6.16 GB less than warning water mark 6.23 GB
这台机器上还有其他的应用吗?有内存的资源监控吗?