MPP Error 1105: other error for mpp stream

表总大小: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不行。是哪个参数没配置正确?

1赞

看下执行计划

1赞
*************************** 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)
1赞

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

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

1赞

可以先试一下缩小一点

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

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

1赞

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

1赞

目前的解决方案是,去掉内存限制,参数max_memory_usagemax_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

1赞

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

2赞

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

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

执行计划

+------------------------------------------+--------------+-----------+-------------------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| 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)