Group by Region is unavailable 问题

Hacker_FiUFo8sr · 2019 年12 月 6 日 03:56

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

【TiDB 版本】：
【问题描述】：

MySQL [test]> SELECT COUNT(1) AS count, SUM(account_balance) AS amount, trade_desc AS type FROM b_test WHERE member_id = ‘22792279001’ AND detail_create_date >= ‘2019-11-19 17:00:00’ AND detail_create_date < ‘2019-11-28 17:00:00’ group by trade_desc;

ERROR 9005 (HY000): Region is unavailable

一个简单的group by查询，经常出现Region is unavailable。member_id已经添加索引，理论上不该出现此种情况。请问如何优化？

zhenjiaogao · 2019 年12 月 6 日 04:03

1、提供 tidb server 节点的 log ，并且请查看下是否有相关的报错

2、提供上述 sql 的执行计划

3、目标表的表结构

Hacker_FiUFo8sr · 2019 年12 月 6 日 04:50

执行计划：

MySQL [test]> explain SELECT COUNT(1) AS count, SUM(account_balance) AS amount, trade_desc AS type FROM b_test WHERE member_id = ‘22792279001’ AND detail_create_date >= ‘2019-11-19 17:00:00’ AND detail_create_date < ‘2019-11-28 17:00:00’ group by trade_desc; ±-------------------------±------------±-----±---------------------------------------------------------------------------------------------------------------------+ | id | count | task | operator info | ±-------------------------±------------±-----±---------------------------------------------------------------------------------------------------------------------+ | HashAgg_19 | 33918.16 | root | group by:col_3, funcs:count(col_0), sum(col_1), firstrow(col_2) | | └─IndexLookUp_20 | 33918.16 | root | | | ├─IndexScan_16 | 88365650.00 | cop | table:b_test, index:member_id, out_trade_no, range:[“22792279001”,“22792279001”], keep order:false | | └─HashAgg_7 | 33918.16 | cop | group by:test.b_test.trade_desc, funcs:count(1), sum(test.b_test.account_balance), firstrow(test.b_test.trade_desc) | | └─Selection_18 | 2474315.25 | cop | ge(test.b_test.detail_create_date, “2019-11-19 17:00:00”), lt(test.b_test.detail_create_date, “2019-11-28 17:00:00”) | | └─TableScan_17 | 88365650.00 | cop | table:b_test, keep order:false | ±-------------------------±------------±-----±---------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec)

tidbserver log：

[2019/12/06 12:48:43.540 +08:00] [ERROR] [client_batch.go:255] [“batchRecvLoop re-create streaming fail”] [target=172.23.227.74:20172] [error=“rpc error: code = Unavailable desc = all SubConns are in TransientFailure, latest connection error: connection error: desc = “transport: Error while dialing dial tcp 172.23.227.74:20172: connect: connection refused””] [stack=“github.com/pingcap/tidb/store/tikv.(*batchCommandsClient).reCreateStreamingClientOnce /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/client_batch.go:255ngithub.com/pingcap/tidb/store/tikv.(*batchCommandsClient).reCreateStreamingClient /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/client_batch.go:327ngithub.com/pingcap/tidb/store/tikv.(*batchCommandsClient).batchRecvLoop /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/client_batch.go:285”]

目标表结构：

CREATE TABLE b_test ( merchant_no varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, member_id varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, account_name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, detail_create_date varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, out_trade_no varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, account_balance varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, in_amount varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, out_amount varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, trade_desc varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, bill_date varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, ext_min varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, order_seq_no bigint(20) DEFAULT NULL, KEY b_test_member_id_IDX1 (member_id), KEY b_test_member_id_IDX2 (member_id,out_trade_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

zhenjiaogao · 2019 年12 月 6 日 06:44

从执行计划可见，索引扫描使用的是 b_test_member_id_IDX2 ( member_id , out_trade_no ) 的组合索引，b_test 表中除该索引外还有一个 member id 的单列索引，建议尝试如下操作：

1、检查目标表统计信息健康度，并收集目标表统计信息。可参照下述链接：
https://pingcap.com/docs-cn/stable/reference/performance/statistics/#统计信息简介

2、收集完统计信息后，尝试执行 explain analyze sql 操作，看下过滤的数据的情况，如果仍然存在问题，建议评估是否使用 member id + detail_create_date 组合索引来完成数据的检索~

Hacker_FiUFo8sr · 2019 年12 月 10 日 04:12

detail_create_date是时间字段，数据分散，创建索引是否会造成索引过大，从而失去查询效果？我尝试针对detail_create_date字段创建索引，但是创建是失败的。同时我也尝试使用tispark进行查询，出现异常。 Caused by: com.pingcap.tikv.exception.GrpcException: message: “peer is not leader for region 8470, leader may None” not_leader { region_id: 8470 }

此查询针对的是大账户流水的group by测试，小账户group by并未出现此问题。查询语句如下： SELECT COUNT(1) AS count, SUM(account_balance) AS amount, trade_desc AS type FROM b_test WHERE member_id = ‘xxx’ AND detail_create_date >= ‘2019-04-10 01:00:00’ AND detail_create_date < ‘2019-04-28 17:00:00’ group by trade_desc 请问应该如何处理？

Hacker_FiUFo8sr · 2019 年12 月 10 日 04:18

如图所示，针对同一个类型的查询，小账户与大账户执行计划不同
上面为小账户，下面为大账户

Lucien-卢西恩 · 2019 年12 月 10 日 06:24

hello～你提问的提一个报错的原因：

ERROR 9005 (HY000) : Region is unavailable

访问的 Region 不可用，某个 Raft Group 不可用，如副本数目不足，出现在 TiKV 比较繁忙或者是 TiKV 节点停机的时候，请检查 TiKV Server 状态/监控/日志。

解决办法

可以通过 pd-ctl 检查 tikv 的状态和 region 的情况，有没有存在缺少副本的 region 的情况，这个报错优先检查一下 Region 的监控情况。

另外第二个问题：

可能的原因：

如果是大账户流水的数据和小账户数据流水的数据的数据分布的偏移量较大，可能会导致执行计划存在异常。
有可能是统计信息不准，导致物理执行计划跑偏，这个有可能的，可以通过 show stats_healthy where table_name=‘xxxx’ ，通过这个检查一下 table 的统计状态是否正常，若 < 70% ,那么建议收集统计信息再确认一下。

关于 Tispark

Tispark 适合单表查询，在暴力扫描的场景响应时间会比 TIDB Server 有优势，但是如果使用使用索引还是优先考虑 TiDB Server。

TUG微尘 · 2021 年1 月 29 日 09:08

对于大表，统计信息失真，校准需要用户参与并且耗时较长(大表)，这个后续有优化计划嘛

GangShen · 2021 年1 月 29 日 09:42

4.0 中有新增 SPM 的功能，用于绑定执行计划，统计信息不准不会影响已经绑定了执行计划的 SQL。
另外有自动捕获绑定和自动捕获演进，避免过多的人工手动绑定执行计划。

具体可以了解一下：https://docs.pingcap.com/zh/tidb/stable/sql-plan-management

system · 2022 年10 月 31 日 19:04

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