小表热点问题优化

版本 tidb 5.0.3
问题一:
针对小表,我做了主键id 使用了随机索引 auto_rand(5) 的表结构。 但是发现还是热点集中。 主要是表的数据量不大。没有分散到多个region 上,单台机器负载压力比较大。 如何手工去split region呢, 我这个表刚开始没有预分片。现在想手工分一下。
但是这个随机id 值很大, 不知道怎么去划分这个范围。
SPLIT TABLE sdhz_rpt.testhello1 BETWEEN (xxx) AND (xxx) REGIONS nums;

随机id值这样的:
±---------------------+
| id |
±---------------------+
| 16140901064499703265 |
| 16140901064499703266 |
| 16140901064499703267 |
| 16140901064499703268 |
| 16140901064499703269 |
| 16140901064499703270 |
| 16140901064499703271 |
| 16140901064499703272 |
| 16140901064499703273 |
| 16140901064499703274 |

| id |
±---------------------+
| 11529215046070202217 |
| 11529215046070202218 |
| 11529215046070202219 |
| 11529215046070202220 |
| 11529215046070202221 |
| 11529215046070202222 |
| 11529215046070202223 |
| 11529215046070202224 |
| 11529215046070202225 |
| 11529215046070202226 |
±---------------------+
10 rows in set (0.01 sec)

问题二:
表不大,表的数据量集中在 几十万到 大几百万 之间的居多。 上千万到亿的 比较少。
(另一个角度,把默认的region_size 144,调整小点比如70M,或者更小。 是不是也可以起到效果)

5赞

问题一 可以使用 load split 来帮助你进行 region split
问题二 不建议你修改 region 大小为一个小表 让集群所有 region 数量都增加。可以考虑使用 follower read 来解。如果热点数真的特别热。还是要考虑再增加缓存来解决问题。

load split
https://docs.pingcap.com/zh/tidb/stable/configure-load-base-split/#load-base-split

follower read
https://docs.pingcap.com/zh/tidb/stable/follower-read/#follower-read

stale read 5.1 以后的特性
https://docs.pingcap.com/zh/tidb/stable/stale-read/#stale-read-功能的使用场景

3赞

说句题外话,我理解的表不大是成百上千条,:joy:本来还想建议放缓存,现在看来是不行了~

3赞

也可以考虑调整热点region打散参数:调整阈值到比当前值适当小
split.qps-threshold
split.byte-threshold

1赞

follower read+stale read效果不错,对于事实表或者维表都有效果、

1赞

这个估计解决不了那边的热点问题:sweat_smile:

是读热点还是写热点? auto_random是高位按事务执行开始时间,剩下的是按自增顺序分配的,某一时刻开始的事务的值还是连续递增的。试试hash分区

1赞

十分感谢~ , 我们主要使用的是tiflash。
针对这两个参数: split.qps-threshold 是以 这个QPS为准吗

流量split.byte-threshold 这个是以哪个监控图上的指标为准呢。

1赞

十分感谢~ , 我们主要使用的是tiflash。

针对这两个参数: split.qps-threshold 是以 这个QPS为准吗

流量split.byte-threshold 这个是以哪个监控图上的指标为准呢。

1赞

tiflash是列存,如果使用不好,查询效率可能还不如tikv

查了一下, split.qps-threshold (QPS 阈值)和 split.byte-threshold (流量阈值)两个参数目前是配置在tikv上的,不清楚对tiflash起不起作用。
TiFlash可以通过设置副本数量,提供更多的读能力。
TiFlash是列存,列存在大环境下,适合宽表查询,引入mpp主要解决join的性能问题,如果只是一个个小表,貌似不太适合小表的查询。

1赞

我们都是统计分析类查询,SQL都是很大,很长。 提供报表查询, 大屏展示。
tikv顶不住多少这类查询,没多少并发就把tikv的 CPU打满了。 这类查询我们都走tiflash,好多了。 就是还存在热点问题, 最初的情况是:单台tiflash的 压力比较大。 其他节点 没一点压力。 目前是2副本。 各个节点都是独立部署, 硬件配置96C/384G。
针对热力图上 出现的10多张表改造成 auto_rand后,现在负载打到了两台机器上,有点效果。 但是 我一共是4台tiflash, 并没有让后端4台节点 同时承担负载。
是跟我的副本数量有关系吗?

1赞

能给几个语句么?看一下适不适合使用tiflash

一:
SELECT DISTINCT t1.省份,t1.大区,t1.城市类型,t1.城市 FROM (select 全国 ,大区 ,省份 ,城市 ,城市类型 ,年月 ,avg(新员工数量) as 顾问数量_新 ,avg(老员工数量) as 顾问数量_老 ,avg(在职人数) as 顾问数量_整体 ,sum(新员工发起) as 有效发起_新顾问 ,sum(老员工发起) as 有效发起_老顾问 ,sum(有效发起) 有效发起 ,sum(新员工捐单) as 捐单数量_新顾问 ,sum(老员工捐单) as 捐单数量_老顾问 ,sum(捐单数量) 捐单数量 from (select “全国” 全国 ,a.bd_area 大区 ,a.bd_province 省份 ,a.bd_city 城市 ,a.city_type 城市类型 ,substr(a.mon,1,7) 年月 ,a.mon 年月日 ,count(distinct a.unique_code) 在职人数 ,count(distinct case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then a.unique_code else null end) 新员工数量 ,count(distinct case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then null else a.unique_code end) 老员工数量 ,sum(a.valid_case_cnt) 有效发起 ,sum(case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then a.valid_case_cnt else 0 end) 新员工发起 ,sum(case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then 0 else a.valid_case_cnt end) 老员工发起 ,sum(a.tq_donate_cnt) 捐单数量 ,sum(case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then a.tq_donate_cnt else 0 end) 新员工捐单 ,sum(case when substr(a.entry_time,1,7) = substr(a.mon,1,7) or a.entry_time >= date_sub(last_day(a.mon),interval 60 day) then 0 else a.tq_donate_cnt end) 老员工捐单 from dws_cf_bd_performance_full_d a where a.mon >= ‘2020-10-01’ and a.bd_city is not null and a.bd_province is not null and a.bd_area is not null and (a.leave_time = ‘’ or a.leave_time is null or a.leave_time>=a.mon) group by 全国 ,a.bd_area ,a.bd_province ,a.bd_city ,a.city_type ,substr(a.mon,1,7) ,a.mon) t0 group by 全国 ,大区 ,省份 ,城市 ,城市类型 ,年月 ) t1 limit 999999;

二:
SELECT DISTINCT t1.医院省份,t1.医院城市,t1.医院名称 FROM (select stat_dt ,周日 ,周五 ,顾问所属大区 ,BD组织省区 ,BD组织分区 ,BD所属小组 ,顾问所属省份 ,顾问所属城市 ,顾问职级 , 顾问职级中文 ,一级架构 ,case when 一级架构 = 二级架构 then null else 二级架构 end as 二级架构 ,case when 二级架构 = 三级架构 then null else 三级架构 end as 三级架构 ,case when 三级架构 = 四级架构 then null else 四级架构 end as 四级架构 ,case when 四级架构 = 五级架构 then null else 五级架构 end as 五级架构 ,case when 五级架构 = 六级架构 then null else 六级架构 end as 六级架构 ,case when 六级架构 = 七级架构 then null else 七级架构 end as 七级架构 ,渠道 ,volunteer_name ,volunteer_code ,org_tag ,partner_tag ,医院名称 ,医院省份 ,医院城市 ,发起案例数 ,有效案例数_发生口径 ,捐单量_发生口径 ,捐款金额_发生口径 ,有效发起案例数_同期口径 ,捐单量_同期口径 ,捐单金额_同期口径 from (select stat_dt ,date_sub(date(cast(stat_dt as DATE)),interval mod(datediff(date(cast(stat_dt as DATE)),‘2017-01-01’),7) day) as 周日 ,date_sub(date(cast(stat_dt as DATE)),interval mod(datediff(date(cast(stat_dt as DATE)),‘2017-01-06’),7) day) as 周五 ,bd_area as 顾问所属大区 ,bd_provincial_area as BD组织省区 ,bd_region as BD组织分区 ,bd_group as BD所属小组 ,bd_province as 顾问所属省份 ,bd_city as 顾问所属城市 ,bd_level as 顾问职级 ,bd_level_name as 顾问职级中文 ,substring_index(substring_index(org_name,’-’,2),’-’,-1) as 一级架构 ,substring_index(substring_index(org_name,’-’,3),’-’,-1) as 二级架构 ,substring_index(substring_index(org_name,’-’,4),’-’,-1) as 三级架构 ,substring_index(substring_index(org_name,’-’,5),’-’,-1) as 四级架构 ,substring_index(substring_index(org_name,’-’,6),’-’,-1) as 五级架构 ,substring_index(substring_index(org_name,’-’,7),’-’,-1) as 六级架构 ,substring_index(substring_index(org_name,’-’,8),’-’,-1) as 七级架构 ,case when channel_group = ‘线上’ then ‘线上’ when channel_type = ‘线下自主’ then ‘线下自主’ when channel_type = ‘线上分流线下’ then ‘线上分流线下’ end as 渠道 ,volunteer_name ,volunteer_code ,org_tag ,partner_tag ,hospital_name as 医院名称,vvhospital_code,hospital_province as 医院省份,hospital_city as 医院城市 ,sum(launch_case_num) as 发起案例数 ,sum(valid_case_num) as 有效案例数_发生口径 ,sum(donate_cnt) as 捐单量_发生口径 ,sum(donate_amt) as 捐款金额_发生口径 ,sum(launch_valid_case_num) as 有效发起案例数_同期口径 ,sum(launch_donate_num) as 捐单量_同期口径 ,sum(launch_donate_amt) as 捐单金额_同期口径 from dws_cf_case_stat_full_d where stat_dt >= ‘2021-01-01’ and org_name not like ‘%测试%’ and org_name is not null group by stat_dt ,date_sub(date(cast(stat_dt as DATE)),interval mod(datediff(date(cast(stat_dt as DATE)),‘2017-01-01’),7) day) ,date_sub(date(cast(stat_dt as DATE)),interval mod(datediff(date(cast(stat_dt as DATE)),‘2017-01-06’),7) day) ,bd_area ,bd_provincial_area ,bd_region ,bd_group ,bd_province ,bd_city ,bd_level ,bd_level_name ,volunteer_name ,volunteer_code ,org_tag ,partner_tag ,hospital_name,vvhospital_code,hospital_province,hospital_city ,一级架构 ,二级架构 ,三级架构 ,四级架构 ,五级架构 ,六级架构 ,七级架构 ,渠道 )t) t1 limit 999999;

三:
SELECT DATE_FORMAT(SELECT_T_1_.cno_start_time, ‘%Y-%m-%d’) AS 外呼日期(day), SELECT_T_1_.clue_type AS clue_type , COUNT(DISTINCT SELECT_T_1_.call_out_id) AS 外呼次数, COUNT(DISTINCT clue_id) AS 外呼线索数 , COUNT(DISTINCT SELECT_T_1_.呼通线索数) AS 呼通线索数, COUNT(DISTINCT SELECT_T_1_.呼通>30s线索数) AS 呼通>30s线索数 , SUM(SELECT_T_1_.呼通时长mins) AS 呼通时长mins , sum(呼通时长mins) / count(DISTINCT if(呼通线索数 <> 0, clue_id, NULL)) AS 单均通话时长 , count(DISTINCT call_out_id) / count(DISTINCT clue_id) AS 单均拨打次数 FROM ( SELECT * , if(connected_status IN (200, ‘预测外呼, 客户接听’) AND date(assgin_time) = date(connected_time), clue_id, 0) AS 当日分配当日呼通线索数 , if(task_status = 6 AND date(assgin_time) = date(last_handle_time) AND (user_tag IN (‘可发起’) OR first_tag = 44), clue_id, 0) AS 当日分配当日流转线索数 , if(task_status = 6 AND (user_tag IN (‘可发起’) OR first_tag = 44), clue_id, 0) AS 流转线索数 , if(connected_status IN (200, ‘预测外呼, 客户接听’), clue_id, 0) AS 呼通线索数 , if(connected_status IN (200, ‘预测外呼, 客户接听’) AND unix_timestamp(cno_end_time) - unix_timestamp(connected_time) >= 30, clue_id, 0) AS 呼通>30s线索数 , if(connected_status IN (200, ‘预测外呼, 客户接听’), unix_timestamp(cno_end_time) - unix_timestamp(connected_time), 0) / 60 AS 呼通时长mins FROM dwb_cf_clue_call_out_detail_full_d ) SELECT_T_1_ GROUP BY DATE_FORMAT(SELECT_T_1_.cno_start_time, ‘%Y-%m-%d’), SELECT_T_1_.clue_type ORDER BY DATE_FORMAT(SELECT_T_1_.cno_start_time, ‘%Y-%m-%d’) DESC, SELECT_T_1_.clue_type ASC LIMIT 0,200;

四:
SELECT
b.pay_month AS pay_month,
agent_id,
agent_name,
team_id,
team_name,
group_id,
group_name,
round(sum(origin), 2) AS origin,
round(
sum(IF(pay_cnt >= 1, origin, 0)) / sum(origin),
4
) AS 1rate,
round(
sum(IF(pay_cnt >= 2, origin, 0)) / sum(origin),
4
) AS 2rate,
round(
sum(IF(pay_cnt >= 3, origin, 0)) / sum(origin),
4
) AS 3rate,
round(sum(IF(pay_cnt >= 4, origin, 0)) / sum(origin), 4) AS 4rate,
round(
sum(IF(pay_cnt >= 5, origin, 0)) / sum(origin),
4
) AS 5rate,
round(
sum(IF(pay_cnt >= 6, origin, 0)) / sum(origin),
4
) AS 6rate,
round(
sum(IF(pay_cnt >= 7, origin, 0)) / sum(origin),
4
) AS 7rate,
round(
sum(IF(pay_cnt >= 8, origin, 0)) / sum(origin),
4
) AS 8rate,
round(
sum(IF(pay_cnt >= 9, origin, 0)) / sum(origin),
4
) AS 9rate,
round(
sum(IF(pay_cnt >= 10, origin, 0)) / sum(origin),
4
) AS 10rate,
round(
sum(IF(pay_cnt >= 11, origin, 0)) / sum(origin),
4
) AS 11rate,
round(
sum(IF(pay_cnt >= 12, origin, 0)) / sum(origin),
4
) AS 12rate
FROM
(
SELECT
agent_id,
agent_name,
team_id,
team_name,
group_id,
group_name,
substr(policy_time, 1, 7) AS pay_month,
order_no,
money,
origin,
origin / 12 AS per_money,
round(money * 12 / origin, 0) AS pay_cnt,
product_name,
policy_time,
STATUS,
CASE
WHEN pay_limit = 10 THEN ‘10’
WHEN pay_limit > 10
AND pay_limit <= 20 THEN ‘20’
WHEN pay_limit > 20 THEN ‘30’
END AS pay_dist
FROM
rpt_sdb_crm_order_stat_history_d
WHERE
STATUS IN (3, 4, 6, 11, 12)
AND valid = 1
AND order_pay_type IN (6, 8, 10)
AND is_long = 1
AND refund_order_no IS NULL
AND money != 0
AND DATE_FORMAT(policy_time, ‘%Y-%m-%d’) >= ‘2021-05-01’
AND DATE_FORMAT(policy_time, ‘%Y-%m-%d’) <= ‘2021-05-31’
AND agent_id IN (
‘JMTYHFZC_202002141816811’,
‘JMTYHFGLSJZC_20210204152751’,
‘DXJMTYHF_202009251619510’
)
AND team_id IN (432, 736, 1072, 560, 1108, 756, 1288, 345, 559)
AND group_id IN (
2952,
4233,
5386,
5899,
3084,
1166,
1167,
1168,
4240,
1169,
1170,
3986,
5267,
3987,
3093,
2837,
3483,
2844,
3101,
5406,
5407,
2719,
2720,
5922,
2722,
1572,
1575,
1576,
2601,
2473,
1577,
1962,
1963,
1964,
3759,
3760,
3761,
1458,
1971,
1459,
1972,
1973,
5431,
2871,
2872,
3897,
2618,
4411,
2500,
5573,
5574,
2886,
3912,
4044,
4560,
1504,
3168,
2400,
4449,
4454,
5609,
4457,
4458,
3050,
4459,
4460,
2669,
3057,
2292,
4469,
2941,
4478,
4479,
3583
)
) b
GROUP BY
agent_id,
agent_name,
team_id,
team_name,
group_id,
group_name,
b.pay_month
ORDER BY
pay_month ASC,
agent_id ASC;

1赞

:thinking:果然够复杂~

我来顶顶 我们也遇到了小表读热点问题 有点麻烦 几万条数据 即使切分了没多久也会自动合并回去 又不敢调小 region合并的阈值 想调整split.qps-threshold 调小了还有个bug 会导致tikv重启

在 Welcome 日志之前看到有 FATAL 日志

[2021/03/06 11:02:42.967 +08:00] [FATAL] [lib.rs:482] ["Uniform::sample_single called with low >= high"] [backtrace="stack backtrace:\n   0: tikv_util::set_panic_hook::{{closure}}\n             at components/tikv_util/src/lib.rs:481\n   1: std::panicking::rust_panic_with_hook\n             at src/libstd/panicking.rs:475\n   2: std::panicking::begin_panic\n             at /rustc/0de96d37fbcc54978458c18f5067cd9817669bc8/src/libstd/panicking.rs:404\n   3: <rand::distributions::uniform::UniformInt<usize> as rand::distributions::uniform::UniformSampler>::sample_single\n             at /home/jenkins/agent/workspace/build_tikv_multi_branch_v4.0.11/tikv/<::std::macros::panic macros>:3\n      rand::Rng::gen_range\n             at /rust/registry/src/github.com-1ecc6299db9ec823/rand-0.6.5/src/lib.rs:245\n   4: raftstore::store::worker::split_controller::sample\n             at components/raftstore/src/store/worker/split_controller.rs:86\n      raftstore::store::worker::split_controller::AutoSplitController::flush\n             at components/raftstore/src/store/worker/split_controller.rs:375\n   5: raftstore::store::worker::pd::StatsMonitor::start::{{closure}}\n             at components/raftstore/src/store/worker/pd.rs:342\n      std::sys_common::backtrace::__rust_begin_short_backtrace\n             at /rustc/0de96d37fbcc54978458c18f5067cd9817669bc8/src/libstd/sys_common/backtrace.rs:136\n   6: std::thread::Builder::spawn_unchecked::{{closure}}::{{closure}}\n             at /rustc/0de96d37fbcc54978458c18f5067cd9817669bc8/src/libstd/thread/mod.rs:469\n      <std::panic::AssertUnwindSafe<F> as core::ops::function::FnOnce<()>>::call_once\n             at /rustc/0de96d37fbcc54978458c18f5067cd9817669bc8/src/libstd/panic.rs:318\n      std::panicking::try::do_call\n             at /rustc/0de96d37fbcc54978458c18f5067cd9817669bc8/src/libstd/panicking.rs:292\n      std::panicking::try\n             at /rustc/0de96d37fbcc54978458c18f5067cd9817669bc8//src/libpanic_unwind/lib.rs:78\n      std::panic::catch_unwind\n             at /rustc/0de96d37fbcc54978458c18f5067cd9817669bc8/src/libstd/panic.rs:394\n      std::thread::Builder::spawn_unchecked::{{closure}}\n             at /rustc/0de96d37fbcc54978458c18f5067cd9817669bc8/src/libstd/thread/mod.rs:468\n      core::ops::function::FnOnce::call_once{{vtable.shim}}\n             at /rustc/0de96d37fbcc54978458c18f5067cd9817669bc8/src/libcore/ops/function.rs:232\n   7: <alloc::boxed::Box<F> as core::ops::function::FnOnce<A>>::call_once\n             at /rustc/0de96d37fbcc54978458c18f5067cd9817669bc8/src/liballoc/boxed.rs:1022\n   8: <alloc::boxed::Box<F> as core::ops::function::FnOnce<A>>::call_once\n             at /rustc/0de96d37fbcc54978458c18f5067cd9817669bc8/src/liballoc/boxed.rs:1022\n      std::sys_common::thread::start_thread\n             at src/libstd/sys_common/thread.rs:13\n      std::sys::unix::thread::Thread::new::thread_start\n             at src/libstd/sys/unix/thread.rs:80\n   9: start_thread\n  10: __clone\n"] [location=/rust/registry/src/github.com-1ecc6299db9ec823/rand-0.6.5/src/distributions/uniform.rs:473] [thread_name=stats-monitor]
[2021/03/06 11:03:10.144 +08:00] [INFO] [lib.rs:92] ["Welcome to TiKV"]

TiKV Panic,并且 panic 堆栈有以下类似的错误:
UniformSampler::sample_single: low >= high"] [backtrace="stack backtrace:

这是一个已知 BUG ,当 workload 存在单个 Region 读请求 QPS 高于 split.qps-threshold(默认3000)会触发这个 BUG 导致 TiKV panic.

临时的 workound 是:
将 QPS 阈值设高 [以下是动态配置设置方法,可以调整相应 tikv 的配置文件]:
[mysql] >> set config tikv split.qps-threshold=3000000

相关 issue:https://github.com/tikv/tikv/issues/9733

2赞

:+1: 这种bug碰上也不算容易吧。 load base split虽然能spLit region但测试感觉效果不大,分裂region还要看分裂的点是否把热点拆开,还得要能region调度到其他的节点,热点region调度现在好像看的是网络流量,即使分裂后也不能把请求分散。 感觉还是建表时auto_random/shard_row_id/hash分区+pre_split_region最有效

1赞

您好。主要是小表 几万条数据 上千的qps访问量/秒,而且是全表扫描不带条件 要取所有数据 热点打散不了, region预先切分了后面也会被合并的参数max-merge-region-size=20m max-merge-region-keys=20w影响又合并回去
我也是看到这个帖子 https://asktug.com/t/topic/68855/3。 遇到了同样的问题。调低了split.qps-threshold触发了重启

1赞

从TIDB分离出去吧,杀鸡用牛刀:sweat_smile:

被开发忽悠了 当时和我说访问量不大 上了几个小表加了5k+ qps :rofl: