MPP Error 1105: other error for mpp stream

lazzyfu · 2021 年11 月 29 日 02:46

表总大小：37GB
tidb version：5.0.4
tiflash数量：2台
tiflash配置：32核心256GB
tiflash内存参数：

tiflash:
    profiles.default.max_memory_usage: 246960619520
    profiles.default.max_memory_usage_for_all_queries: 246960619520

执行SQL

SELECT
    CH_BROKER_ID,
    CH_DEALER_ID,
    I_USER_ID,
    MIN(I_DATE) AS I_FIRST_MONTH,
    MAX(I_DATE) AS I_LAST_MONTH,
    IFNULL(SUM(I_AMOUNT), 0) AS I_AMOUNT,
    IFNULL(SUM(I_COUNT), 0) AS I_COUNT,
    IFNULL(ROUND(SUM(I_AMOUNT) / SUM(I_COUNT), 0), 0) AS I_AVG_AMOUNT,
    COUNT(DISTINCT I_DATE) AS I_MONTH_COUNT,
    IFNULL(
        ROUND(SUM(I_AMOUNT) / COUNT(DISTINCT I_DATE), 0),
        0
    ) AS I_MONTH_AVG_AMOUNT,
    CH_GENDER,
    D_BIRTHDAY,
    I_IS_CHINA_ID_CARD,
    CH_REGION_CODE,
    CH_PROVINCE_CODE,
    CH_CITY_CODE,
    CH_COUNTY_CODE,
    I_BROKER_TYPE
FROM
    tbl1
WHERE
    (
        I_DATE >= 201701
        AND I_DATE <= 202110
    )
GROUP BY
    CH_BROKER_ID,
    CH_DEALER_ID,
    I_USER_ID;

报错：

other error for mpp stream: DB::TiFlashException: Memory limit (total) exceeded: would use 230.02 GiB (attempt to allocate chunk of 134217728 bytes), maximum: 230.00 GiB

请问是什么问题？我关闭mpp是可以执行的，打开mpp不行。是哪个参数没配置正确？

songxuecheng · 2021 年11 月 29 日 02:50

看下执行计划

lazzyfu · 2021 年11 月 29 日 03:04

*************************** 1. row ***************************
           id: Projection_5
      estRows: 59701612.67
         task: root
access object: 
operator info: stat.st_broker_dealer_user_month_summary.ch_broker_id, stat.st_broker_dealer_user_month_summary.ch_dealer_id, stat.st_broker_dealer_user_month_summary.i_user_id, Column#22, Column#23, ifnull(Column#24, 0)->Column#27, ifnull(Column#25, 0)->Column#28, ifnull(round(div(Column#24, Column#25), 0), 0)->Column#29, Column#26, ifnull(round(div(Column#24, cast(Column#26, decimal(20,0) BINARY)), 0), 0)->Column#30, stat.st_broker_dealer_user_month_summary.ch_gender, stat.st_broker_dealer_user_month_summary.d_birthday, stat.st_broker_dealer_user_month_summary.i_is_china_id_card, stat.st_broker_dealer_user_month_summary.ch_region_code, stat.st_broker_dealer_user_month_summary.ch_province_code, stat.st_broker_dealer_user_month_summary.ch_city_code, stat.st_broker_dealer_user_month_summary.ch_county_code, stat.st_broker_dealer_user_month_summary.i_broker_type
*************************** 2. row ***************************
           id: └─TableReader_34
      estRows: 59701612.67
         task: root
access object: 
operator info: data:ExchangeSender_33
*************************** 3. row ***************************
           id:   └─ExchangeSender_33
      estRows: 59701612.67
         task: batchCop[tiflash]
access object: 
operator info: ExchangeType: PassThrough
*************************** 4. row ***************************
           id:     └─Projection_29
      estRows: 59701612.67
         task: batchCop[tiflash]
access object: 
operator info: Column#22, Column#23, Column#24, Column#25, Column#26, stat.st_broker_dealer_user_month_summary.ch_broker_id, stat.st_broker_dealer_user_month_summary.ch_dealer_id, stat.st_broker_dealer_user_month_summary.i_user_id, stat.st_broker_dealer_user_month_summary.ch_gender, stat.st_broker_dealer_user_month_summary.d_birthday, stat.st_broker_dealer_user_month_summary.i_is_china_id_card, stat.st_broker_dealer_user_month_summary.ch_region_code, stat.st_broker_dealer_user_month_summary.ch_province_code, stat.st_broker_dealer_user_month_summary.ch_city_code, stat.st_broker_dealer_user_month_summary.ch_county_code, stat.st_broker_dealer_user_month_summary.i_broker_type
*************************** 5. row ***************************
           id:       └─HashAgg_30
      estRows: 59701612.67
         task: batchCop[tiflash]
access object: 
operator info: group by:stat.st_broker_dealer_user_month_summary.ch_broker_id, stat.st_broker_dealer_user_month_summary.ch_dealer_id, stat.st_broker_dealer_user_month_summary.i_user_id, funcs:min(Column#36)->Column#22, funcs:max(Column#37)->Column#23, funcs:sum(Column#38)->Column#24, funcs:sum(Column#39)->Column#25, funcs:count(distinct stat.st_broker_dealer_user_month_summary.i_date)->Column#26, funcs:firstrow(stat.st_broker_dealer_user_month_summary.ch_broker_id)->stat.st_broker_dealer_user_month_summary.ch_broker_id, funcs:firstrow(stat.st_broker_dealer_user_month_summary.ch_dealer_id)->stat.st_broker_dealer_user_month_summary.ch_dealer_id, funcs:firstrow(stat.st_broker_dealer_user_month_summary.i_user_id)->stat.st_broker_dealer_user_month_summary.i_user_id, funcs:firstrow(Column#43)->stat.st_broker_dealer_user_month_summary.ch_gender, funcs:firstrow(Column#44)->stat.st_broker_dealer_user_month_summary.d_birthday, funcs:firstrow(Column#45)->stat.st_broker_dealer_user_month_summary.i_is_china_id_card, funcs:firstrow(Column#46)->stat.st_broker_dealer_user_month_summary.ch_region_code, funcs:firstrow(Column#47)->stat.st_broker_dealer_user_month_summary.ch_province_code, funcs:firstrow(Column#48)->stat.st_broker_dealer_user_month_summary.ch_city_code, funcs:firstrow(Column#49)->stat.st_broker_dealer_user_month_summary.ch_county_code, funcs:firstrow(Column#50)->stat.st_broker_dealer_user_month_summary.i_broker_type
*************************** 6. row ***************************
           id:         └─ExchangeReceiver_32
      estRows: 59701612.67
         task: batchCop[tiflash]
access object: 
operator info: 
*************************** 7. row ***************************
           id:           └─ExchangeSender_31
      estRows: 59701612.67
         task: batchCop[tiflash]
access object: 
operator info: ExchangeType: HashPartition, Hash Cols: stat.st_broker_dealer_user_month_summary.ch_broker_id, stat.st_broker_dealer_user_month_summary.ch_dealer_id, stat.st_broker_dealer_user_month_summary.i_user_id
*************************** 8. row ***************************
           id:             └─HashAgg_8
      estRows: 59701612.67
         task: batchCop[tiflash]
access object: 
operator info: group by:Column#80, Column#81, Column#82, Column#83, funcs:min(Column#68)->Column#36, funcs:max(Column#69)->Column#37, funcs:sum(Column#70)->Column#38, funcs:sum(Column#71)->Column#39, funcs:firstrow(Column#72)->Column#43, funcs:firstrow(Column#73)->Column#44, funcs:firstrow(Column#74)->Column#45, funcs:firstrow(Column#75)->Column#46, funcs:firstrow(Column#76)->Column#47, funcs:firstrow(Column#77)->Column#48, funcs:firstrow(Column#78)->Column#49, funcs:firstrow(Column#79)->Column#50
*************************** 9. row ***************************
           id:               └─Projection_43
      estRows: 219272946.50
         task: batchCop[tiflash]
access object: 
operator info: stat.st_broker_dealer_user_month_summary.i_date, stat.st_broker_dealer_user_month_summary.i_date, cast(stat.st_broker_dealer_user_month_summary.i_amount, decimal(41,0) BINARY)->Column#70, cast(stat.st_broker_dealer_user_month_summary.i_count, decimal(41,0) BINARY)->Column#71, stat.st_broker_dealer_user_month_summary.ch_gender, stat.st_broker_dealer_user_month_summary.d_birthday, stat.st_broker_dealer_user_month_summary.i_is_china_id_card, stat.st_broker_dealer_user_month_summary.ch_region_code, stat.st_broker_dealer_user_month_summary.ch_province_code, stat.st_broker_dealer_user_month_summary.ch_city_code, stat.st_broker_dealer_user_month_summary.ch_county_code, stat.st_broker_dealer_user_month_summary.i_broker_type, stat.st_broker_dealer_user_month_summary.ch_broker_id, stat.st_broker_dealer_user_month_summary.ch_dealer_id, stat.st_broker_dealer_user_month_summary.i_user_id, stat.st_broker_dealer_user_month_summary.i_date
*************************** 10. row ***************************
           id:                 └─Selection_28
      estRows: 219272946.50
         task: batchCop[tiflash]
access object: 
operator info: ge(stat.st_broker_dealer_user_month_summary.i_date, 201701), le(stat.st_broker_dealer_user_month_summary.i_date, 202110)
*************************** 11. row ***************************
           id:                   └─TableFullScan_27
      estRows: 223089445.00
         task: batchCop[tiflash]
access object: table:st_broker_dealer_user_month_summary
operator info: keep order:false
11 rows in set, 1 warning (0.00 sec)

lazzyfu · 2021 年11 月 29 日 03:09

这是其中一台的tiflash监控，另外一台也是一样，请查看下图

表总大小总共为37GB，为什么2台都会使用到140多G，就crash呢，是什么消耗了内存

Skyworker · 2021 年11 月 29 日 04:59

可以先试一下缩小一点

的范围，比如一年的情况，看看能算出来吗？先排除一下除了数据量以外其他的问题。

lazzyfu · 2021 年12 月 1 日 08:04

您好，这个表总共才37GB，我where条件可以理解为全表扫描吧，但是为什么会使用到230+的内存呢

duzq · 2021 年12 月 1 日 09:12

你好
1.我看评估全表是223089445条数据，聚合大概有59701612条，实际是这样的么？
2.执行计划有一个 warning，麻烦 show warnings看下内容

lazzyfu · 2021 年12 月 1 日 10:17

目前的解决方案是，去掉内存限制，参数max_memory_usage和max_memory_usage_for_all_queries如下：
tiflash:
logger.count: 5
logger.level: warning
profiles.default.batch_cop_pool_size: 32
profiles.default.cop_pool_size: 32
profiles.default.max_memory_usage: 0
profiles.default.max_memory_usage_for_all_queries: 0

duzq · 2021 年12 月 1 日 10:26

MPP 特性下， tiflash本地生成 hash 表后，2 个 tiflash 的hash 表还会汇聚到一个 tiflash 上进行 2 次聚合，聚合期间数据是冗余了一份的，并且 37G 的表内存占用肯定是大于 37G 的，如果单表聚合，可以把 MPP 关掉（set @@tidb_allow_mpp=0;），节省内存开销。

littlefall-PingCAP · 2021 年12 月 3 日 15:35

hi，想问一下这里的解决方案是指去掉内存限制后，即使开了 MPP 也能跑吗？
以及能否提供一下可以跑时的 explain analyze，多谢。

lazzyfu · 2021 年12 月 6 日 04:45

您好，去掉内存限制后，可以执行了。

执行计划

+------------------------------------------+--------------+-----------+-------------------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                                       | estRows      | actRows   | task              | access object                             | execution info                                                                                                  | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | memory  | disk |
+------------------------------------------+--------------+-----------+-------------------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_5                             | 59576530.17  | 78318421  | root              |                                           | time:2m35.8s, loops:76746, Concurrency:8                                                                        | stat.st_broker_dealer_user_month_summary.ch_broker_id, stat.st_broker_dealer_user_month_summary.ch_dealer_id, stat.st_broker_dealer_user_month_summary.i_user_id, Column#22, Column#23, ifnull(Column#24, 0)->Column#27, ifnull(Column#25, 0)->Column#28, ifnull(round(div(Column#24, Column#25), 0), 0)->Column#29, Column#26, ifnull(round(div(Column#24, cast(Column#26, decimal(20,0) BINARY)), 0), 0)->Column#30, stat.st_broker_dealer_user_month_summary.ch_gender, stat.st_broker_dealer_user_month_summary.d_birthday, stat.st_broker_dealer_user_month_summary.i_is_china_id_card, stat.st_broker_dealer_user_month_summary.ch_region_code, stat.st_broker_dealer_user_month_summary.ch_province_code, stat.st_broker_dealer_user_month_summary.ch_city_code, stat.st_broker_dealer_user_month_summary.ch_county_code, stat.st_broker_dealer_user_month_summary.i_broker_type                                                                                                                                                                          | 4.36 MB | N/A  |
| └─TableReader_34                         | 59576530.17  | 78318421  | root              |                                           | time:2m27.8s, loops:76746, cop_task: {num: 514, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_33                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | N/A     | N/A  |
|   └─ExchangeSender_33                    | 59576530.17  | 78318421  | batchCop[tiflash] |                                           | tiflash_task:{proc max:1m8.5s, min:1m8.5s, p80:1m8.5s, p95:1m8.5s, iters:512, tasks:2, threads:2}               | ExchangeType: PassThrough                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A     | N/A  |
|     └─Projection_29                      | 59576530.17  | 78318421  | batchCop[tiflash] |                                           | tiflash_task:{proc max:1m8.5s, min:1m8.5s, p80:1m8.5s, p95:1m8.5s, iters:512, tasks:2, threads:2}               | Column#22, Column#23, Column#24, Column#25, Column#26, stat.st_broker_dealer_user_month_summary.ch_broker_id, stat.st_broker_dealer_user_month_summary.ch_dealer_id, stat.st_broker_dealer_user_month_summary.i_user_id, stat.st_broker_dealer_user_month_summary.ch_gender, stat.st_broker_dealer_user_month_summary.d_birthday, stat.st_broker_dealer_user_month_summary.i_is_china_id_card, stat.st_broker_dealer_user_month_summary.ch_region_code, stat.st_broker_dealer_user_month_summary.ch_province_code, stat.st_broker_dealer_user_month_summary.ch_city_code, stat.st_broker_dealer_user_month_summary.ch_county_code, stat.st_broker_dealer_user_month_summary.i_broker_type                                                                                                                                                                                                                                                                                                                                                                        | N/A     | N/A  |
|       └─HashAgg_30                       | 59576530.17  | 78318421  | batchCop[tiflash] |                                           | tiflash_task:{proc max:1m3.7s, min:1m3.7s, p80:1m3.7s, p95:1m3.7s, iters:512, tasks:2, threads:2}               | group by:stat.st_broker_dealer_user_month_summary.ch_broker_id, stat.st_broker_dealer_user_month_summary.ch_dealer_id, stat.st_broker_dealer_user_month_summary.i_user_id, funcs:min(Column#36)->Column#22, funcs:max(Column#37)->Column#23, funcs:sum(Column#38)->Column#24, funcs:sum(Column#39)->Column#25, funcs:count(distinct stat.st_broker_dealer_user_month_summary.i_date)->Column#26, funcs:firstrow(stat.st_broker_dealer_user_month_summary.ch_broker_id)->stat.st_broker_dealer_user_month_summary.ch_broker_id, funcs:firstrow(stat.st_broker_dealer_user_month_summary.ch_dealer_id)->stat.st_broker_dealer_user_month_summary.ch_dealer_id, funcs:firstrow(stat.st_broker_dealer_user_month_summary.i_user_id)->stat.st_broker_dealer_user_month_summary.i_user_id, funcs:firstrow(Column#43)->stat.st_broker_dealer_user_month_summary.ch_gender, funcs:firstrow(Column#44)->stat.st_broker_dealer_user_month_summary.d_birthday, funcs:firstrow(Column#45)->stat.st_broker_dealer_user_month_summary.i_is_china_id_card, funcs:firstrow(Column#46)->stat.st_broker_dealer_user_month_summary.ch_region_code, funcs:firstrow(Column#47)->stat.st_broker_dealer_user_month_summary.ch_province_code, funcs:firstrow(Column#48)->stat.st_broker_dealer_user_month_summary.ch_city_code, funcs:firstrow(Column#49)->stat.st_broker_dealer_user_month_summary.ch_county_code, funcs:firstrow(Column#50)->stat.st_broker_dealer_user_month_summary.i_broker_type | N/A     | N/A  |
|         └─ExchangeReceiver_32            | 59576530.17  | 219135718 | batchCop[tiflash] |                                           | tiflash_task:{proc max:48.6s, min:45.7s, p80:48.6s, p95:48.6s, iters:1024, tasks:2, threads:32}                 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | N/A     | N/A  |
|           └─ExchangeSender_31            | 59576530.17  | 219135718 | batchCop[tiflash] |                                           | tiflash_task:{proc max:37.9s, min:37.9s, p80:37.9s, p95:37.9s, iters:512, tasks:2, threads:32}                  | ExchangeType: HashPartition, Hash Cols: stat.st_broker_dealer_user_month_summary.ch_broker_id, stat.st_broker_dealer_user_month_summary.ch_dealer_id, stat.st_broker_dealer_user_month_summary.i_user_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | N/A     | N/A  |
|             └─HashAgg_8                  | 59576530.17  | 219135718 | batchCop[tiflash] |                                           | tiflash_task:{proc max:37.9s, min:37.9s, p80:37.9s, p95:37.9s, iters:512, tasks:2, threads:2}                   | group by:Column#80, Column#81, Column#82, Column#83, funcs:min(Column#68)->Column#36, funcs:max(Column#69)->Column#37, funcs:sum(Column#70)->Column#38, funcs:sum(Column#71)->Column#39, funcs:firstrow(Column#72)->Column#43, funcs:firstrow(Column#73)->Column#44, funcs:firstrow(Column#74)->Column#45, funcs:firstrow(Column#75)->Column#46, funcs:firstrow(Column#76)->Column#47, funcs:firstrow(Column#77)->Column#48, funcs:firstrow(Column#78)->Column#49, funcs:firstrow(Column#79)->Column#50                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | N/A     | N/A  |
|               └─Projection_43            | 219261946.77 | 219135718 | batchCop[tiflash] |                                           | tiflash_task:{proc max:2.47s, min:2.47s, p80:2.47s, p95:2.47s, iters:3923, tasks:2, threads:32}                 | stat.st_broker_dealer_user_month_summary.i_date, stat.st_broker_dealer_user_month_summary.i_date, cast(stat.st_broker_dealer_user_month_summary.i_amount, decimal(41,0) BINARY)->Column#70, cast(stat.st_broker_dealer_user_month_summary.i_count, decimal(41,0) BINARY)->Column#71, stat.st_broker_dealer_user_month_summary.ch_gender, stat.st_broker_dealer_user_month_summary.d_birthday, stat.st_broker_dealer_user_month_summary.i_is_china_id_card, stat.st_broker_dealer_user_month_summary.ch_region_code, stat.st_broker_dealer_user_month_summary.ch_province_code, stat.st_broker_dealer_user_month_summary.ch_city_code, stat.st_broker_dealer_user_month_summary.ch_county_code, stat.st_broker_dealer_user_month_summary.i_broker_type, stat.st_broker_dealer_user_month_summary.ch_broker_id, stat.st_broker_dealer_user_month_summary.ch_dealer_id, stat.st_broker_dealer_user_month_summary.i_user_id, stat.st_broker_dealer_user_month_summary.i_date                                                                                         | N/A     | N/A  |
|                 └─Selection_28           | 219261946.77 | 219135718 | batchCop[tiflash] |                                           | tiflash_task:{proc max:1.66s, min:1.66s, p80:1.66s, p95:1.66s, iters:3923, tasks:2, threads:32}                 | ge(stat.st_broker_dealer_user_month_summary.i_date, 201701), le(stat.st_broker_dealer_user_month_summary.i_date, 202110)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | N/A     | N/A  |
|                   └─TableFullScan_27     | 224440400.00 | 219722775 | batchCop[tiflash] | table:st_broker_dealer_user_month_summary | tiflash_task:{proc max:1.59s, min:1.59s, p80:1.59s, p95:1.59s, iters:3940, tasks:2, threads:32}                 | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A     | N/A  |
+------------------------------------------+--------------+-----------+-------------------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
11 rows in set, 65535 warnings (2 min 35.81 sec)

system · 2022 年10 月 31 日 19:03

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。