千万级数据 group by 性能调优

🪐 TiDB 技术问题 性能调优
1

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

【TiDB 版本】4.0

【问题描述】

  • 机器环境配置
    image
    image916×324 51.5 KB
  • Table Schema
    image
    image1302×204 21.8 KB
  • 查询
    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 文件，可在哪里进行获取，再提供详细信息

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

2

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

3

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

4

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

5

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

6
  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
7

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，没有得到明显改善。
8

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

9

TiFlash & TiKV 如下：

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

10

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

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

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

12

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

13

还在吗 :joy:

15

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

16

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

17

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

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

18

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

19

看分析，耗时都在tidb上

20

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

21

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 的方式保存下结果，以便后续分析。