SQL查询时长过长 & tidb 频繁oom

xcode03 · 2020 年9 月 14 日 10:06

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

【TiDB 版本】：v4.0.4
【问题描述】：

我在执行以下SQL时，耗时0.87s

mysql> select max(pitch) from items where yaw >= 0.5;
+------------+
| max(pitch) |
+------------+
| 1          |
+------------+
1 row in set (0.87 sec)

然后在执行min()时，耗时3个多小时


mysql> select min(pitch) from items where yaw >= 0.5;
+------------+
| min(pitch) |
+------------+
| 1          |
+------------+
1 row in set (3 hours 7 min 13.50 sec)

求问原因是什么？

Tidb同时只有一个节点在read数据，follower read似乎不生效

select count(*) from person_table;

大概两亿数据量，查询只有一个tidb节点在工作。tidb配置为3节点，每节点32c 128g，直接oom

后改了一下SQL，并设置了follower read 可以正常跑完了，但仍只有一个节点在工作

mysql> SHOW SESSION VARIABLES LIKE 'tidb_replica_read';
+-------------------+---------------------+
| Variable_name     | Value               |
+-------------------+---------------------+
| tidb_replica_read | leader-and-follower |
+-------------------+---------------------+
1 row in set (0.01 sec)
-----------------------------------------------------
mysql> select /*+ STREAM_AGG() */ count(*) from person_table;
+-----------+
| count(*)  |
+-----------+
| 212795893 |
+-----------+
1 row in set (6 min 48.95 sec)

另外在SQL执行后tidb的heapinuse内存落下来了，但process内存仍保持高位，没有被回收。再次执行同样查询会导致tidb节点oom。

请pingcap同学帮我解惑，谢谢！

yilong · 2020 年9 月 14 日 12:22

麻烦在 dashboard 慢查询界面查看下select min(pitch) from items where yaw >= 0.5 的信息，看下耗时在哪里比较长.
follower read 是 session 级别的。 tidb-server 是只有一个在查询，tikv 是多个并行的。

xcode03 · 2020 年9 月 15 日 03:13

我把图贴过来了，麻烦同学看一下。

另外tidb的oom问题还有什么优化的方法吗？对于2亿数据量的表，tidb的推荐配置（硬件/软件）是什么？

robi · 2020 年9 月 15 日 07:25

hi 可以方便提供下 items 的表结构，还有

explain select max(pitch) from items where yaw >= 0.5

和

explain select min(pitch) from items where yaw >= 0.5

的结果吗

xcode03 · 2020 年9 月 15 日 08:12

mysql> desc items;
+-------------+--------------+------+------+---------+-------+
| Field       | Type         | Null | Key  | Default | Extra |
+-------------+--------------+------+------+---------+-------+
| key         | varchar(300) | YES  | MUL  | NULL    |       |
| align5p     | varchar(30)  | YES  | MUL  | NULL    |       |
| origin      | varchar(30)  | YES  | MUL  | NULL    |       |
| base        | tinyint(1)   | YES  | MUL  | NULL    |       |
| idnum       | varchar(45)  | YES  | MUL  | NULL    |       |
| type        | varchar(10)  | YES  | MUL  | NULL    |       |
| birth       | varchar(8)   | YES  | MUL  | NULL    |       |
| gender      | varchar(10)  | YES  | MUL  | NULL    |       |
| source      | varchar(36)  | YES  | MUL  | NULL    |       |
| source_path | varchar(54)  | YES  |      | NULL    |       |
| meta_extra  | json         | YES  |      | NULL    |       |
| blur        | float        | YES  | MUL  | NULL    |       |
| pitch       | float        | YES  | MUL  | NULL    |       |
| yaw         | float        | YES  | MUL  | NULL    |       |
| crop        | float        | YES  | MUL  | NULL    |       |
| confidence  | float        | YES  | MUL  | NULL    |       |
| attr_extra  | json         | YES  |      | NULL    |       |
| id1         | int(11)      | YES  |      | 0       |       |
+-------------+--------------+------+------+---------+-------+
18 rows in set (0.00 sec)

mysql> explain select max(pitch) from items where yaw >= 0.5;
+-------------------------------------+---------+-----------+---------------------------------------+----------------------------------------------+
| id                                  | estRows | task      | access object                         | operator info                                |
+-------------------------------------+---------+-----------+---------------------------------------+----------------------------------------------+
| StreamAgg_14                        | 1.00    | root      |                                       | funcs:max(face_world.items.pitch)->Column#20 |
| └─Limit_19                          | 1.00    | root      |                                       | offset:0, count:1                            |
|   └─Projection_48                   | 1.00    | root      |                                       | face_world.items.pitch, face_world.items.yaw |
|     └─IndexLookUp_47                | 1.00    | root      |                                       |                                              |
|       ├─IndexFullScan_44(Build)     | 156.84  | cop[tikv] | table:items, index:items_pitch(pitch) | keep order:true, desc                        |
|       └─Selection_46(Probe)         | 1.00    | cop[tikv] |                                       | ge(face_world.items.yaw, 0.5)                |
|         └─TableRowIDScan_45         | 156.84  | cop[tikv] | table:items                           | keep order:false                             |
+-------------------------------------+---------+-----------+---------------------------------------+----------------------------------------------+
7 rows in set (0.00 sec)

mysql> explain select min(pitch) from items where yaw >= 0.5;
+-------------------------------------+---------+-----------+---------------------------------------+----------------------------------------------+
| id                                  | estRows | task      | access object                         | operator info                                |
+-------------------------------------+---------+-----------+---------------------------------------+----------------------------------------------+
| StreamAgg_14                        | 1.00    | root      |                                       | funcs:min(face_world.items.pitch)->Column#20 |
| └─Limit_19                          | 1.00    | root      |                                       | offset:0, count:1                            |
|   └─Projection_48                   | 1.00    | root      |                                       | face_world.items.pitch, face_world.items.yaw |
|     └─IndexLookUp_47                | 1.00    | root      |                                       |                                              |
|       ├─IndexFullScan_44(Build)     | 156.84  | cop[tikv] | table:items, index:items_pitch(pitch) | keep order:true                              |
|       └─Selection_46(Probe)         | 1.00    | cop[tikv] |                                       | ge(face_world.items.yaw, 0.5)                |
|         └─TableRowIDScan_45         | 156.84  | cop[tikv] | table:items                           | keep order:false                             |
+-------------------------------------+---------+-----------+---------------------------------------+----------------------------------------------+
7 rows in set (0.00 sec)

感谢！

Lucien · 2020 年9 月 15 日 09:17

hello ～辛苦再发一下另外两个的真实执行计划看一下

explain analyze select max(pitch) from items where yaw >= 0.5;

explain analyze select min(pitch) from items where yaw >= 0.5;

xcode03 · 2020 年9 月 15 日 09:32

min() 的迟迟没有出结果，估计又要3h+了。另外我现在正在执行tiflash replica操作，是否会对性能有影响？

mysql> explain analyze select max(pitch) from items where yaw >= 0.5;
+-------------------------------------+---------+---------+-----------+---------------------------------------+---------------------------------------------+----------------------------------------------+-------------------+------+
| id                                  | estRows | actRows | task      | access object                         | execution info                              | operator info                                | memory            | disk |
+-------------------------------------+---------+---------+-----------+---------------------------------------+---------------------------------------------+----------------------------------------------+-------------------+------+
| StreamAgg_14                        | 1.00    | 1       | root      |                                       | time:4.7106011s, loops:2                    | funcs:max(face_world.items.pitch)->Column#20 | 480 Bytes         | N/A  |
| └─Limit_19                          | 1.00    | 1       | root      |                                       | time:4.710593908s, loops:2                  | offset:0, count:1                            | N/A               | N/A  |
|   └─Projection_48                   | 1.00    | 1       | root      |                                       | time:4.710591847s, loops:1, Concurrency:OFF | face_world.items.pitch, face_world.items.yaw | 852 Bytes         | N/A  |
|     └─IndexLookUp_47                | 1.00    | 1       | root      |                                       | time:4.710587588s, loops:1                  |                                              | 336.6220703125 KB | N/A  |
|       ├─IndexFullScan_44(Build)     | 156.84  | -1      | cop[tikv] | table:items, index:items_pitch(pitch) | time:0s, loops:0                            | keep order:true, desc                        | N/A               | N/A  |
|       └─Selection_46(Probe)         | 1.00    |         | cop[tikv] |                                       | time:0ns, loops:0                           | ge(face_world.items.yaw, 0.5)                | N/A               | N/A  |
|         └─TableRowIDScan_45         | 156.84  | 0       | cop[tikv] | table:items                           | time:0s, loops:0                            | keep order:false                             | N/A               | N/A  |
+-------------------------------------+---------+---------+-----------+---------------------------------------+---------------------------------------------+----------------------------------------------+-------------------+------+
7 rows in set (4.76 sec)

winoros · 2020 年9 月 15 日 09:47

你好这个表大小的多大？yaw 这个列上有索引吗

xcode03 · 2020 年9 月 15 日 09:52

有索引的，数据大小大概在1亿到2亿之间。

mysql> show index from items;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| items |          1 | items_key        |            1 | key         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_align5p    |            1 | align5p     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_origin     |            1 | origin      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_base       |            1 | base        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_idnum      |            1 | idnum       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_type       |            1 | type        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_birth      |            1 | birth       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_gender     |            1 | gender      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_source     |            1 | source      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_blur       |            1 | blur        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_pitch      |            1 | pitch       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_yaw        |            1 | yaw         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_crop       |            1 | crop        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | items_confidence |            1 | confidence  | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
14 rows in set (0.00 sec)

winoros · 2020 年9 月 15 日 09:55

yaw 这个列和 pitch 这个列是不是在大小上有比较强的相关性，如果 pitch 大的时候，yaw 的值也比较大。当 pitch 小的时候，yaw 的值也比较小？

xcode03 · 2020 年9 月 15 日 10:04

这两个字段没有什么关联性

winoros · 2020 年9 月 15 日 10:09

那这个确实奇怪一些，如果两个列的相关性比较差的话，max 和 min 的情况应该是差不多的。可以用
select * from (select * from items order by pitch limit 100000) items where yaw >= 0.5 这样看一下？

xcode03 · 2020 年9 月 15 日 10:37

这样会返回empty set，应该是limit太小了。

我发现使用tiflash后，min()的速度会变得正常起来。

但是这样并不本质。。使用tikv时，min()的问题仍然存在

xcode03 · 2020 年9 月 15 日 12:44

explain analyze select min(pitch) from items where yaw >= 0.5;

yilong · 2020 年9 月 16 日 03:40

您好，麻烦反馈下这段时间 tidb.log 和 tikv.log 的日志，多谢。
麻烦反馈这段时间的 over-view，tidb 和 detail-tikv 的监控信息

(1)、chrome 安装这个插件https://chrome.google.com/webstore/detail/full-page-screen-capture/fdpohaocaechififmbbbbbknoalclacl

(2)、鼠标焦点置于 Dashboard 上，按 ?可显示所有快捷键，先按 d 再按 E 可将所有 Rows 的 Panels 打开，需等待一段时间待页面加载完成。

(3)、使用这个 full-page-screen-capture 插件进行截屏保存

winoros · 2020 年9 月 16 日 06:24

这两个列的大小分布实际上应该还是有关系的。
tiflash 因为列式存储+计算性能更好所以可以有很好的执行效果

这个情况下可以尝试 use index(yaw 所在列的索引名称)，这个应该是使用 tikv 时，正确的执行计划。

顺便问一下 yaw >= 0.5 的行共有多少？