xcode03
(Xcode03)
1
为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
- 我在执行以下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同学帮我解惑,谢谢!
xcode03
(Xcode03)
3
我把图贴过来了,麻烦同学看一下。
另外tidb的oom问题还有什么优化的方法吗?对于2亿数据量的表,tidb的推荐配置(硬件/软件)是什么?
robi
(robi-PingCAP)
4
hi 可以方便提供下 items 的表结构, 还有
explain select max(pitch) from items where yaw >= 0.5
和
explain select min(pitch) from items where yaw >= 0.5
的结果吗
xcode03
(Xcode03)
5
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)
感谢!
hello ~ 辛苦再发一下另外两个的真实执行计划看一下
explain analyze select max(pitch) from items where yaw >= 0.5;
explain analyze select min(pitch) from items where yaw >= 0.5;
xcode03
(Xcode03)
7
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)
xcode03
(Xcode03)
9
有索引的,数据大小大概在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
(PingCAP-winoros)
10
yaw 这个列和 pitch 这个列是不是在大小上有比较强的相关性,如果 pitch 大的时候,yaw 的值也比较大。当 pitch 小的时候,yaw 的值也比较小?
winoros
(PingCAP-winoros)
12
那这个确实奇怪一些,如果两个列的相关性比较差的话,max
和 min
的情况应该是差不多的。可以用
select * from (select * from items order by pitch limit 100000) items where yaw >= 0.5
这样看一下?
xcode03
(Xcode03)
13
这样会返回empty set,应该是limit太小了。
我发现使用tiflash后,min()的速度会变得正常起来。
但是这样并不本质。。使用tikv时,min()的问题仍然存在
xcode03
(Xcode03)
14
explain analyze select min(pitch) from items where yaw >= 0.5;
yilong
(yi888long)
15
- 您好,麻烦反馈下这段时间 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
(PingCAP-winoros)
16
这两个列的大小分布实际上应该还是有关系的。
tiflash 因为列式存储+计算性能更好所以可以有很好的执行效果
这个情况下可以尝试 use index(yaw 所在列的索引名称),这个应该是使用 tikv 时,正确的执行计划。
顺便问一下 yaw >= 0.5
的行共有多少?