Tiflash 内存溢出

大飞飞呀 · 2024 年10 月 16 日 09:36

【 TiDB 使用环境】生产环境
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
报错时候也是没有并发执行的，只是串行
我有个SQL,偶尔会报Tiflash内存溢出，正常消耗内存不到100G，但是偶尔会报错如下：
是什么原因呢，像这种自己执行不报错，晚上定时偶尔报错，在哪能看到当时执行失败的SQL的执行计划呢

 "Creating join.  throw exception: Code: 0, e.displayText() = DB::Exception: Code: 0, e.displayText() = DB::Exception: Memory limit (total) exceeded caused by 'out of memory quota for data computing' : would use 246.95 GiB for data computing (attempt to allocate chunk of 1084000 bytes), limit of memory for data computing: 226.57 GiB, e.what() = DB::Exception, e.what() = DB::Exception In 26.824516509 sec. "] [source="MPP<query:<query_ts:1729541805072414544, local_query_id:1949603, server_id:1124321, start_ts:453389006933590118>,task_id:17>"] [thread_id=1045]

【遇到的问题：问题现象及影响】
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】

xfworld · 2024 年10 月 17 日 02:19

你都知道是哪个 SQL 了，可以分析下看看

这明显是内存不足了
would use 249.51 GiB for data computing (attempt to allocate chunk of 1614458 bytes), limit of memory for data computing: 226.57 GiB

cchouqiang · 2024 年10 月 17 日 02:54

是不是并发执行了啊

有猫万事足 · 2024 年10 月 17 日 13:07

之前有人报告过类似的问题，单个跑不报错，是因为没有并发。

https://docs.pingcap.com/zh/tidb/stable/tiflash-spill-disk

还是考虑使用落盘来解决这个问题。

哈喽沃德 · 2024 年10 月 18 日 05:57

调整 TiFlash 内存限制：如果资源允许，可以适当提高 TiFlash 的内存限制，以支持更复杂的查询。
优化 SQL 查询：分析可能导致内存高消耗的 SQL 查询，并尝试进行优化，例如：

减少 JOIN 数量
避免不必要的 GROUP BY 操作
使用更高效的 WHERE 子句过滤数据

分批处理数据：如果定时任务处理的数据量很大，可以考虑将数据分批处理，减少单次查询的数据量。
监控与调优：定期监控 TiFlash 的内存使用情况，并根据实际使用情况调整配置参数。
更新统计信息：确保 TiDB 的统计信息是最新的，使用 ANALYZE TABLE 命令更新表的统计信息，以帮助优化器生成更合适的执行计划。

zhaokede · 2024 年10 月 18 日 07:20

从报出来的日志看，还是内存超了。看是否在业务上可以优化SQL，或者是分段执行

FutureDB · 2024 年10 月 20 日 06:48

这种回答怎么感觉都是来自大模型…

gcworkerishungry · 2024 年10 月 20 日 07:28

1，一般不会误报；top捕捉实际内存的开销，可能看监控不一定准；查看dmesg -T 看是否OOM
2，另外看下系统除了Tiflash有哪些占用内存，有时候混部内存参数没有设置，tikv和tidb都会占用不少内存。
3，缩减范围，减小并发。

koby · 2024 年10 月 21 日 00:27

1、看监控TOP SQL；
2、看系统资源；
3、不够资源就扩容吧。

洪七表哥 · 2024 年10 月 21 日 06:00

提高内存试试

大飞飞呀 · 2024 年10 月 21 日 06:13

为啥监控上看着内存完全没问题呢

大飞飞呀 · 2024 年10 月 21 日 06:14

内存已经提高到200多G了

大飞飞呀 · 2024 年10 月 21 日 07:06

串行执行

像风一样的男子 · 2024 年10 月 21 日 09:29

执行计划能发下吗。看看有没有优化空间

小鱼吃大鱼 · 2024 年10 月 21 日 09:40

不一定是那个sql问题吧，是那个时间段的内存不足了，再执行这个sql已经不满足需要的内存，直接就kill了，查下，那个时间段执行的sql有哪些，占内存大的优化下

大飞飞呀 · 2024 年10 月 21 日 10:22

Projection_725, 250687847.21, root, , Column#277, Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276
└─Sort_65, 250687847.21, root, , Column#269, Column#637:desc
  └─Projection_726, 250687847.21, root, , Column#277, Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276, field(Column#277, <500, [500,1000), [1000,2000), [2000,5000), [5000,10000), [10000,90000), >=90000, 识别异常（证件号为空）, 合计)->Column#637
    └─TableReader_711, 250687847.21, root, , MppVersion: 1, data:ExchangeSender_710
      └─ExchangeSender_710, 250687847.21, mpp[tiflash], , ExchangeType: PassThrough
        └─Projection_67, 250687847.21, mpp[tiflash], , case(eq(Column#268, 0), <500, eq(Column#268, 1), [500,1000), eq(Column#268, 2), [1000,2000), eq(Column#268, 3), [2000,5000), eq(Column#268, 4), [5000,10000), eq(Column#268, 5), [10000,90000), eq(Column#268, 6), >=90000, eq(Column#268, 7), 识别异常（证件号为空）, eq(Column#268, 8), 合计, )->Column#277, Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276
          └─Union_709, 250687847.21, mpp[tiflash], , 
            ├─Projection_722, 125343488.73, mpp[tiflash], , Column#268, cast(Column#269, varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin)->Column#269, Column#270, cast(Column#271, bigint(21) BINARY)->Column#271, Column#272, Column#273, Column#274, Column#275, Column#276
            │ └─Projection_704, 125343488.73, mpp[tiflash], , Column#66, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#68, Column#67, Column#69, round(div(Column#68, Column#69), 0)->Column#273, Column#109, round(div(Column#68, Column#147), 6)->Column#275, round(div(cast(Column#67, decimal(20,0) BINARY), cast(Column#146, decimal(20,0) BINARY)), 6)->Column#276
            │   └─Projection_396, 125343488.73, mpp[tiflash], , Column#67, Column#68, Column#69, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#66, Column#109, Column#146, Column#147
            │     └─HashJoin_393, 125343488.73, mpp[tiflash], , left outer join, equal:[eq(Column#66, Column#105) eq(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id)]
            │       ├─Projection_178(Build), 100274790.99, mpp[tiflash], , Column#105, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, round(div(Column#107, cast(Column#108, decimal(20,0) BINARY)), 0)->Column#109
            │       │ └─Projection_216, 100274790.99, mpp[tiflash], , Column#107, Column#108, Column#105, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │       │   └─HashAgg_180, 100274790.99, mpp[tiflash], , group by:Column#105, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:sum(Column#106)->Column#107, funcs:count(1)->Column#108, funcs:firstrow(Column#105)->Column#105, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
            │       │     └─ExchangeReceiver_208, 100274790.99, mpp[tiflash], , stream_count: 16
            │       │       └─ExchangeSender_207, 100274790.99, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#105, collate: N/A], [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
            │       │         └─Projection_206, 100274790.99, mpp[tiflash], , div(Column#106, cast(case(eq(Column#368, 0), 1, Column#368), decimal(20,0) BINARY))->Column#106, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#105
            │       │           └─HashAgg_182, 100274790.99, mpp[tiflash], , group by:Column#618, Column#619, Column#620, funcs:count(Column#614)->Column#368, funcs:sum(Column#615)->Column#106, funcs:firstrow(Column#616)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:firstrow(Column#617)->Column#105
            │       │             └─Projection_717, 100274790.99, mpp[tiflash], , Column#102, Column#102, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, case(lt(Column#102, 50000), 0, lt(Column#102, 100000), 1, lt(Column#102, 200000), 2, lt(Column#102, 500000), 3, lt(Column#102, 1000000), 4, lt(Column#102, 9000000), 5, 6)->Column#617, case(lt(Column#102, 50000), 0, lt(Column#102, 100000), 1, lt(Column#102, 200000), 2, lt(Column#102, 500000), 3, lt(Column#102, 1000000), 4, lt(Column#102, 9000000), 5, 6)->Column#618, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
            │       │               └─Selection_184, 100274790.99, mpp[tiflash], , not(isnull(case(lt(Column#102, 50000), 0, lt(Column#102, 100000), 1, lt(Column#102, 200000), 2, lt(Column#102, 500000), 3, lt(Column#102, 1000000), 4, lt(Column#102, 9000000), 5, 6)))
            │       │                 └─Projection_195, 125343488.73, mpp[tiflash], , Column#102, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │       │                   └─HashAgg_196, 125343488.73, mpp[tiflash], , group by:Column#360, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#361)->Column#102, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
            │       │                     └─ExchangeReceiver_198, 125343488.73, mpp[tiflash], , stream_count: 16
            │       │                       └─ExchangeSender_197, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], [name: stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, collate: N/A], stream_count: 16
            │       │                         └─HashAgg_187, 125343488.73, mpp[tiflash], , group by:Column#611, Column#612, Column#613, funcs:sum(Column#610)->Column#361
            │       │                           └─Projection_716, 1184004184.63, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#610, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#612, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
            │       │                             └─Selection_194, 1184004184.63, mpp[tiflash], , eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1)
            │       │                               └─TableFullScan_193, 615501029.09, mpp[tiflash], table:a, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false
            │       └─ExchangeReceiver_177(Probe), 125343488.73, mpp[tiflash], , 
            │         └─ExchangeSender_176, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: Column#66, collate: N/A], [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A]
            │           └─HashJoin_77, 125343488.73, mpp[tiflash], , left outer join, equal:[eq(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id)]
            │             ├─Projection_110(Build), 125343488.73, mpp[tiflash], , Column#67, Column#68, Column#69, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#66
            │             │ └─HashAgg_78, 125343488.73, mpp[tiflash], , group by:Column#599, Column#600, funcs:count(distinct Column#594)->Column#67, funcs:sum(Column#595)->Column#68, funcs:sum(Column#596)->Column#69, funcs:firstrow(Column#597)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:firstrow(Column#598)->Column#66
            │             │   └─Projection_713, 125343488.73, mpp[tiflash], , stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, Column#63, cast(Column#64, decimal(20,0) BINARY)->Column#596, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, case(lt(Column#63, 50000), 0, lt(Column#63, 100000), 1, lt(Column#63, 200000), 2, lt(Column#63, 500000), 3, lt(Column#63, 1000000), 4, lt(Column#63, 9000000), 5, 6)->Column#598, case(lt(Column#63, 50000), 0, lt(Column#63, 100000), 1, lt(Column#63, 200000), 2, lt(Column#63, 500000), 3, lt(Column#63, 1000000), 4, lt(Column#63, 9000000), 5, 6)->Column#599, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │             │     └─Projection_93, 125343488.73, mpp[tiflash], , Column#63, Column#64, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │             │       └─HashAgg_94, 125343488.73, mpp[tiflash], , group by:Column#317, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#318)->Column#63, funcs:sum(Column#319)->Column#64, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
            │             │         └─ExchangeReceiver_96, 125343488.73, mpp[tiflash], , stream_count: 16
            │             │           └─ExchangeSender_95, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
            │             │             └─HashAgg_83, 125343488.73, mpp[tiflash], , group by:Column#591, Column#592, Column#593, funcs:sum(Column#590)->Column#318, funcs:count(1)->Column#319
            │             │               └─Projection_712, 1184004184.63, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#590, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#592, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
            │             │                 └─Selection_92, 1184004184.63, mpp[tiflash], , eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1)
            │             │                   └─TableFullScan_91, 615501029.09, mpp[tiflash], table:a, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false
            │             └─Projection_159(Probe), 125343488.73, mpp[tiflash], , Column#146, Column#147, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │               └─HashAgg_127, 125343488.73, mpp[tiflash], , group by:Column#609, funcs:count(distinct Column#605, Column#606)->Column#146, funcs:sum(Column#607)->Column#147, funcs:firstrow(Column#608)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │                 └─Projection_715, 125343488.73, mpp[tiflash], , case(lt(Column#142, 50000), 0, lt(Column#142, 100000), 1, lt(Column#142, 200000), 2, lt(Column#142, 500000), 3, lt(Column#142, 1000000), 4, lt(Column#142, 9000000), 5, 6)->Column#605, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, Column#142, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │                   └─Projection_142, 125343488.73, mpp[tiflash], , Column#142, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │                     └─HashAgg_143, 125343488.73, mpp[tiflash], , group by:Column#342, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#343)->Column#142, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
            │                       └─ExchangeReceiver_145, 125343488.73, mpp[tiflash], , stream_count: 16
            │                         └─ExchangeSender_144, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
            │                           └─HashAgg_132, 125343488.73, mpp[tiflash], , group by:Column#602, Column#603, Column#604, funcs:sum(Column#601)->Column#343
            │                             └─Projection_714, 1184004184.63, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#601, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#603, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
            │                               └─Selection_141, 1184004184.63, mpp[tiflash], , eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1)
            │                                 └─TableFullScan_140, 615501029.09, mpp[tiflash], table:a, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false
            ├─Projection_723, 125343488.73, mpp[tiflash], , cast(Column#268, bigint(1) BINARY)->Column#268, cast(Column#269, varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin)->Column#269, Column#270, cast(Column#271, bigint(21) BINARY)->Column#271, Column#272, Column#273, Column#274, Column#275, Column#276
            │ └─Projection_705, 125343488.73, mpp[tiflash], , 8->Column#268, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#187, Column#188, Column#189, Column#190, Column#230, 1.000000->Column#275, 1.000000->Column#276
            │   └─HashJoin_657, 125343488.73, mpp[tiflash], , left outer join, equal:[eq(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id)]
            │     ├─Projection_404(Build), 125343488.73, mpp[tiflash], , stat.dwd_gs_bill_order_detail_si.ch_dealer_id, Column#187, Column#188, Column#189, round(div(Column#187, Column#189), 0)->Column#190
            │     │ └─Projection_436, 125343488.73, mpp[tiflash], , Column#187, Column#188, Column#189, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │     │   └─HashAgg_406, 125343488.73, mpp[tiflash], , group by:Column#630, funcs:sum(Column#625)->Column#187, funcs:count(distinct Column#626, Column#627)->Column#188, funcs:sum(Column#628)->Column#189, funcs:firstrow(Column#629)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │     │     └─Projection_719, 125343488.73, mpp[tiflash], , Column#183, case(lt(Column#183, 50000), 0, lt(Column#183, 100000), 1, lt(Column#183, 200000), 2, lt(Column#183, 500000), 3, lt(Column#183, 1000000), 4, lt(Column#183, 9000000), 5, 6)->Column#626, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, cast(Column#184, decimal(20,0) BINARY)->Column#628, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │     │       └─Projection_419, 125343488.73, mpp[tiflash], , Column#183, Column#184, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │     │         └─HashAgg_420, 125343488.73, mpp[tiflash], , group by:Column#465, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#466)->Column#183, funcs:sum(Column#467)->Column#184, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
            │     │           └─ExchangeReceiver_422, 125343488.73, mpp[tiflash], , stream_count: 16
            │     │             └─ExchangeSender_421, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
            │     │               └─HashAgg_409, 125343488.73, mpp[tiflash], , group by:Column#622, Column#623, Column#624, funcs:sum(Column#621)->Column#466, funcs:count(1)->Column#467
            │     │                 └─Projection_718, 1184004184.63, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#621, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#623, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
            │     │                   └─Selection_418, 1184004184.63, mpp[tiflash], , eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1)
            │     │                     └─TableFullScan_417, 615501029.09, mpp[tiflash], table:a, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false
            │     └─Projection_455(Probe), 125343488.73, mpp[tiflash], , stat.dwd_gs_bill_order_detail_si.ch_dealer_id, round(div(Column#228, cast(Column#229, decimal(20,0) BINARY)), 0)->Column#230
            │       └─Projection_526, 125343488.73, mpp[tiflash], , Column#228, Column#229, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │         └─HashAgg_457, 125343488.73, mpp[tiflash], , group by:stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:sum(Column#227)->Column#228, funcs:count(1)->Column#229, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │           └─Projection_491, 125343488.73, mpp[tiflash], , div(Column#227, cast(case(eq(Column#496, 0), 1, Column#496), decimal(20,0) BINARY))->Column#227, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │             └─HashAgg_459, 125343488.73, mpp[tiflash], , group by:stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:count(Column#223)->Column#496, funcs:sum(Column#223)->Column#227, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │               └─Projection_474, 125343488.73, mpp[tiflash], , Column#223, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
            │                 └─HashAgg_475, 125343488.73, mpp[tiflash], , group by:Column#488, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:sum(Column#489)->Column#223, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt)->stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
            │                   └─ExchangeReceiver_477, 125343488.73, mpp[tiflash], , stream_count: 16
            │                     └─ExchangeSender_476, 125343488.73, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
            │                       └─HashAgg_464, 125343488.73, mpp[tiflash], , group by:Column#632, Column#633, Column#634, funcs:sum(Column#631)->Column#489
            │                         └─Projection_720, 1184004184.63, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#631, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, date_format(stat.dwd_gs_bill_order_detail_si.d_finished_time, %Y%m)->Column#633, stat.dwd_gs_bill_order_detail_si.ch_id_card_uc_encrypt
            │                           └─Selection_473, 1184004184.63, mpp[tiflash], , eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), ne(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1)
            │                             └─TableFullScan_472, 615501029.09, mpp[tiflash], table:a, pushed down filter:eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752")), keep order:false
            └─Projection_724, 869.74, mpp[tiflash], , cast(Column#268, bigint(1) BINARY)->Column#268, cast(Column#269, varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin)->Column#269, Column#270, Column#271, Column#272, Column#273, Column#274, Column#275, Column#276
              └─Projection_706, 869.74, mpp[tiflash], , 7->Column#268, stat.dwd_gs_bill_order_detail_si.ch_dealer_id, cast(Column#260, decimal(63,0) BINARY)->Column#270, 0->Column#271, cast(Column#261, decimal(42,0) BINARY)->Column#272, cast(round(div(Column#260, cast(Column#261, decimal(20,0) BINARY))), decimal(65,0) BINARY)->Column#273, 0->Column#274, 0.000000->Column#275, 0.000000->Column#276
                └─Projection_698, 869.74, mpp[tiflash], , Column#260, Column#261, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
                  └─HashAgg_699, 869.74, mpp[tiflash], , group by:stat.dwd_gs_bill_order_detail_si.ch_dealer_id, funcs:sum(Column#587)->Column#260, funcs:sum(Column#588)->Column#261, funcs:firstrow(stat.dwd_gs_bill_order_detail_si.ch_dealer_id)->stat.dwd_gs_bill_order_detail_si.ch_dealer_id, stream_count: 16
                    └─ExchangeReceiver_701, 869.74, mpp[tiflash], , stream_count: 16
                      └─ExchangeSender_700, 869.74, mpp[tiflash], , ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: stat.dwd_gs_bill_order_detail_si.ch_dealer_id, collate: N/A], stream_count: 16
                        └─HashAgg_696, 869.74, mpp[tiflash], , group by:Column#636, funcs:sum(Column#635)->Column#587, funcs:count(1)->Column#588
                          └─Projection_721, 46488962.71, mpp[tiflash], , cast(stat.dwd_gs_bill_order_detail_si.i_amount, decimal(20,0) BINARY)->Column#635, stat.dwd_gs_bill_order_detail_si.ch_dealer_id
                            └─Selection_685, 46488962.71, mpp[tiflash], , eq(mod(crc32(stat.dwd_gs_bill_order_detail_si.ch_dealer_id), 10), 0), eq(stat.dwd_gs_bill_order_detail_si.i_is_test, 0), eq(stat.dwd_gs_bill_order_detail_si.i_status, 1), in(stat.dwd_gs_bill_order_detail_si.ch_ref_type, "wx", "wx_mix", "ext", "ext_mix", "bank", "bank_mix", "ali", "ali_mix", "corporate", "ent_collect", "ent"), lt(stat.dwd_gs_bill_order_detail_si.d_finished_time, 2024-10-21), not(in(stat.dwd_gs_bill_order_detail_si.ch_dealer_id, "06337857", "06609648", "09536011", "huoshan", "03846658", "20366765", "09934414", "04236904", "04742796", "05554802", "07903142", "06093840", "04060592", "23874410", "05697813", "07450151", "03879479", "01169402", "23846077", "05959070", "03011133", "09504240", "04383882", "05124946", "09697747", "04821802", "21384089", "04312435", "08668757", "03005770", "05437145", "01784779", "09504167", "01394097", "04665044", "05247922", "07984138", "01406467", "02796689", "03301116", "07498026", "09195944", "07625695", "02718761", "05578413", "05455881", "08513438", "00209456", "00780532", "02164142", "04344824", "05268695", "00623752"))
                              └─TableFullScan_684, 70897182.98, mpp[tiflash], table:t1, pushed down filter:eq(stat.dwd_gs_bill_order_detail_si.i_user_paid_age, -1), keep order:false

xfworld · 2024 年10 月 21 日 14:14

从我标记的这个地方起，上行就用 tidb 聚合了，这么多数据… 2亿多条，

不知道你开了落盘没… 内存肯定扛不住…

大飞飞呀 · 2024 年10 月 22 日 02:21

所以内存溢出是tidb溢出的？不应该呀

大飞飞呀 · 2024 年10 月 22 日 02:22

失败之后立马重试，又会成功了，这是为什么呢，而且观察tidb内存监控，也没啥变化

xfworld · 2024 年10 月 22 日 04:27

多观察下吧，这个执行计划感觉不准