TiDB使用SSB压测，q1.1耗时100秒！麻烦大家帮忙分析下

HappyMa · 2021 年12 月 23 日 03:25

【 TiDB 使用环境】
4台，16核64G内存

【问题】
SSB压测，q1.1测试结果查询耗时太长100秒

【 TiDB 版本】
V5.3

【执行计划】
Q1.1执行计划.html (9.4 KB)
Q1.1执行计划优化.html (18.9 KB)

【数据量】

【优化参数】

set @@session.tidb_allow_mpp=1;
set @@session.tidb_isolation_read_engines = "tiflash,tikv";
set @@tidb_mem_quota_query = 20 << 30;
set @@tidb_distsql_scan_concurrency = 15;
set @@tidb_opt_distinct_agg_push_down = 1;
set @@tidb_opt_agg_push_down = 1;
set @@tidb_allow_batch_cop = 1;

【建表语句】


create DATABASE if not exists db1;

CREATE TABLE `lineorder` (
  `lo_orderkey` bigint(20) NULL COMMENT "",
  `lo_linenumber` bigint(20) NULL COMMENT "",
  `lo_custkey` int(11) NULL COMMENT "",
  `lo_partkey` int(11) NULL COMMENT "",
  `lo_suppkey` int(11) NULL COMMENT "",
  `lo_orderdate` int(11) NULL COMMENT "",
  `lo_orderpriority` varchar(16) NULL COMMENT "",
  `lo_shippriority` int(11) NULL COMMENT "",
  `lo_quantity` bigint(20) NULL COMMENT "",
  `lo_extendedprice` bigint(20) NULL COMMENT "",
  `lo_ordtotalprice` bigint(20) NULL COMMENT "",
  `lo_discount` bigint(20) NULL COMMENT "",
  `lo_revenue` bigint(20) NULL COMMENT "",
  `lo_supplycost` bigint(20) NULL COMMENT "",
  `lo_tax` bigint(20) NULL COMMENT "",
  `lo_commitdate` bigint(20) NULL COMMENT "",
  `lo_shipmode` varchar(11) NULL COMMENT ""
)	
PARTITION BY RANGE(`lo_orderdate`)(
PARTITION p1992 VALUES LESS THAN (19930101),
PARTITION p1993 VALUES LESS THAN (19940101),
PARTITION p1994 VALUES LESS THAN (19950101),
PARTITION p1995 VALUES LESS THAN (19960101),
PARTITION p1996 VALUES LESS THAN (19970101),
PARTITION p1997 VALUES LESS THAN (19980101),
PARTITION p1998 VALUES LESS THAN (19990101)
);


CREATE TABLE `customer` (
  `c_custkey` int(11) NULL COMMENT "",
  `c_name` varchar(26) NULL COMMENT "",
  `c_address` varchar(41) NULL COMMENT "",
  `c_city` varchar(11) NULL COMMENT "",
  `c_nation` varchar(16) NULL COMMENT "",
  `c_region` varchar(13) NULL COMMENT "",
  `c_phone` varchar(16) NULL COMMENT "",
  `c_mktsegment` varchar(11) NULL COMMENT ""
);

CREATE TABLE `date` (
  `d_datekey` int(11) NULL COMMENT "",
  `d_date` varchar(20) NULL COMMENT "",
  `d_dayofweek` varchar(10) NULL COMMENT "",
  `d_month` varchar(11) NULL COMMENT "",
  `d_year` int(11) NULL COMMENT "",
  `d_yearmonthnum` int(11) NULL COMMENT "",
  `d_yearmonth` varchar(9) NULL COMMENT "",
  `d_daynuminweek` int(11) NULL COMMENT "",
  `d_daynuminmonth` int(11) NULL COMMENT "",
  `d_daynuminyear` int(11) NULL COMMENT "",
  `d_monthnuminyear` int(11) NULL COMMENT "",
  `d_weeknuminyear` int(11) NULL COMMENT "",
  `d_sellingseason` varchar(14) NULL COMMENT "",
  `d_lastdayinweekfl` int(11) NULL COMMENT "",
  `d_lastdayinmonthfl` int(11) NULL COMMENT "",
  `d_holidayfl` int(11) NULL COMMENT "",
  `d_weekdayfl` int(11) NULL COMMENT ""
);

CREATE TABLE `part` (
  `p_partkey` int(11) NULL COMMENT "",
  `p_name` varchar(23) NULL COMMENT "",
  `p_mfgr` varchar(7) NULL COMMENT "",
  `p_category` varchar(8) NULL COMMENT "",
  `p_brand` varchar(10) NULL COMMENT "",
  `p_color` varchar(12) NULL COMMENT "",
  `p_type` varchar(26) NULL COMMENT "",
  `p_size` int(11) NULL COMMENT "",
  `p_container` varchar(11) NULL COMMENT ""
);

CREATE TABLE `supplier` (
  `s_suppkey` int(11) NULL COMMENT "",
  `s_name` varchar(26) NULL COMMENT "",
  `s_address` varchar(26) NULL COMMENT "",
  `s_city` varchar(11) NULL COMMENT "",
  `s_nation` varchar(16) NULL COMMENT "",
  `s_region` varchar(13) NULL COMMENT "",
  `s_phone` varchar(16) NULL COMMENT ""
);

【其他信息】

Song · 2021 年12 月 23 日 07:14

从执行计划来看，数据扫表走的是 static pruning，由于 TiFlash 并不支持 PartitionUnion 算子，所以计算都在 TiDB 单节点做的。目前 TiFlash 的 Dynamic pruning 正在开发中，预计 v5.5.0 支持，请关注产品最新消息。

HappyMa · 2021 年12 月 23 日 07:28

我多执行了几次，好像tidb自动优化了，见附件Q1.1执行计划优化，结果耗时5秒多。能分析下吗？
还有我其他sql ，hash join 因为6亿条数据的缘故，耗时很长，为啥不能根据查询条件在取数据时就下推过滤掉？

补一下sql

-- Q2.1
SELECT SUM(lo_revenue), d_year, p_brand
FROM lineorder, date, part, supplier
WHERE lo_orderdate = d_datekey
AND lo_partkey = p_partkey
AND lo_suppkey = s_suppkey
AND p_category = 'MFGR#12'
AND s_region = 'AMERICA'
GROUP BY d_year, p_brand
ORDER BY d_year, p_brand;

Song · 2021 年12 月 23 日 08:30

这个好像是5分多？

caiyfc · 2021 年12 月 23 日 08:46

对tiflash了解不是很多，我猜测变快了是因为经常查询，导致有一些结果已经缓存下来了，下次查的时候，会直接命中，不用去扫底层数据了，所以速度变快了

HappyMa · 2021 年12 月 23 日 09:18

对，我换了一个sql，结果很慢，现在是不知道怎么优化

HappyMa · 2021 年12 月 23 日 09:18

脑壳痛，看了别人的测试，感觉好快

Hacker_t9OJsnDa · 2021 年12 月 23 日 16:25

暂时按照这个配置预期如果不分区的话大概是 10 秒左右，分区了反而会大幅降低速度，这里主要原因在于分区表在 MPP 下没有支持，因此使用分区表的查询会退化到使用单机的 go runtime 引擎，反而会有数量级的降速。分区表在 MPP 的支持大致来说需要 1 月中可以有能 PoC 的版本，正式发版应该是 4 月。另外很好奇您能否帮忙看下反复执行后性能大幅度提升到 5 秒的 analyze 结果？TiFlash 暂时没有支持 result cache。
使用 explain analyze select … 可以得到结果。

HappyMa · 2021 年12 月 24 日 00:40

这个就是5秒的执行计划

Song · 2021 年12 月 27 日 07:57

你好。看了一下 “Q1.1执行计划.html” 和 “Q1.1执行计划优化.html”。查询加速的主要原因是因为开启了 tidb_opt_agg_push_down 开关。该开关开启后，agg 会在 join 和 PartitionUnion 之前做，这使得 join 的数据大大减少，此外，agg 也会因此在 tiflash 做且走 batch cop。

Hacker_t9OJsnDa · 2021 年12 月 27 日 08:27

请问您在前后两次测试中有没有开启和关闭刚说的 tidb_opt_agg_push_down 开关？

system · 2022 年10 月 31 日 19:25

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