千万级数据 group by 性能调优

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:

【TiDB 版本】4.0

【问题描述】

  • 机器环境配置
    image
  • Table Schema
  • 查询
    MySQL [fulltext_db]> select count(1) from mf_docs;
    ±---------+
    | count(1) |
    ±---------+
    | 10351374 |
    ±---------+
    1 row in set (0.93 sec)
    在千万数据数据规模上进行查询: 耗时 12-sec ;参照如下:
    sql_groupby.txt (3.3 KB)

注:info_gathering.py 中 inventory.ini 文件,可在哪里进行获取,再提供详细信息

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

麻烦也反馈下 explain analyze sql 的结果。

如果有 TiFlash 也麻烦看下 TiFlash 的 Explain Analyze,多谢。

TiFlash 详情如下:
tikv&tiflash.txt (7.9 KB)

如下:
sql-analyze.txt (7.1 KB)

  1. 目标期望是多长时间呢? 这个sql调用很频繁吗?
  2. 业务低峰期,可以先尝试在session级别调大参数试试:
    tidb_hashagg_final_concurrency 和 tidb_hashagg_partial_concurrency 调到 8 试试。
    https://docs.pingcap.com/zh/tidb/stable/system-variables#tidb_hashagg_final_concurrency

MySQL [fulltext_db]> SET tidb_hashagg_final_concurrency = 20;
Query OK, 0 rows affected (0.00 sec)

MySQL [fulltext_db]> SET tidb_hashagg_partial_concurrency = 20;
Query OK, 0 rows affected (0.00 sec)

MySQL [fulltext_db]> analyze table mf_docs;
Query OK, 0 rows affected (1 min 43.62 sec)

多次执行稳定在 8s~9s;

  1. 目标期望值在 5s 以内,sql调用不频繁;
  2. 另外借助TiFlash,没有得到明显改善。

辛苦也给一下调整参数后的 explain analyze 的结果。TiKV 和 Tiflash 的都给一下。

TiFlash & TiKV 如下:

sql-analyze.txt (9.1 KB)
注: 磁盘非SSD

对于 以下查询语句,期望通过 建立 titleSimHash 和 pubTime的联合索引,来进行性能优化, 发现并未提升。相反,在 Mysql 中进行联合索引建立,提升效果显著。
SELECT titleSimHash,Max(pubTime) AS pubTime FROM mf_docs GROUP BY titleSimHash Order by pubTime limit 10;

trace format='row' SELECT titleSimHash,count(1) AS num FROM mf_docs  group by titleSimHash order by num desc limit 10;

这个的结果辛苦也给一下。TiKV 和 Tiflash 分别都给一下。

请查看
sql_trace_tiflash.txt (746.2 KB)
sql_trace_tikv.txt (281.6 KB)

还在吗 :joy:

千万量也不大吧主要是你group太多,看分析你在tidb耗时太多了,估计只能在业务上优化了,看看能不能优化掉吧

我这边没找到和你类似的数据集,反正在1e group 1k 左右和你耗时差不多,
看你这个是1kw group 300w?

嗯嗯;本来也是期望通过 TIDB 来达到去ES的场景, 但就是group num 大,性能没有太多提升。另外在测试中:

期望通过 建立 titleSimHash 和 pubTime的联合索引,来进行性能优化, 发现并未提升。相反,在 Mysql 中进行联合索引建立,提升效果显著。
SELECT titleSimHash,Max(pubTime) AS pubTime FROM mf_docs GROUP BY titleSimHash Order by pubTime limit 10;

其实我也遇到过这种,但是调了半天发现group多了之后,tidb处理就是耗时大,这种已经不是能不能下推的事情了,看看有没有其他大佬给点启发

看分析,耗时都在tidb上

看大佬有没更加优雅的解决方案

SELECT titleSimHash,Max(pubTime) AS pubTime FROM mf_docs GROUP BY titleSimHash Order by pubTime limit 10;

针对这条 SQL,可以看下 explain 的结果,应该可能并没有用上新建的复合索引( titleSimHash 和 pubTime的联合索引),执行计划是不是和未加索引的执行计划是一致的。

如果是一致的,说明新建立的索引并没用上,可以尝试使用 SELECT /*+ stream_agg() */ titleSimHash,Max(pubTime) AS pubTime FROM mf_docs GROUP BY titleSimHash Order by pubTime limit 10; 使用 stream agg 来看看效果。

在执行上面这条 SQL 的时候,最好使用 explain analyze + SQL 的方式保存下结果,以便后续分析。