为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
- 【TiDB 版本】：Release Version: v4.0.0-alpha-1119-g41c7d7e94
- 【问题描述】：告警提示“TiDB heap memory usage is over 10 GB”，在tidb_stderr.log中发现“fatal error: runtime: out of memory”，然后集群的就宕机了。
- 这是 OOM 了，可以在 tidb.log 中，down 机那段时间，搜索关键字 expensive，看看是什么 SQL，进行优化。一般是 SQL 使用内存过多导致的。
这是找出来一条查询预计，耗时100s以上，但是在mysql上查询只需要0.034s左右。
[2019/12/23 12:28:33.638 +08:00] [WARN] [expensivequery.go:156] [expensive_query] [cost_time=60.0055572s] [process_time=108.075s] [wait_time=108.075s] [backoff_time=0.002s] [request_count=12473] [total_keys=26304978] [process_keys=25382080] [num_cop_tasks=12473] [process_avg_time=0.008664715s] [process_p90_time=0.017s] [process_max_time=2.229s] [process_max_addr=172.19.207.30:20160] [wait_avg_time=0.019187765s] [wait_p90_time=0.047s] [wait_max_time=0.602s] [wait_max_addr=172.19.207.30:20160] [stats=wn_fiction_bookshelf:413420169335144475] [conn_id=973] [user=judianunite] [database=app_user] [table_ids="[461]"] [txn_start_ts=413420815284174849] [mem_max="2255835264 Bytes (2.1009103059768677 GB)"] [sql="select * from wn_fiction_bookshelf AS a WHERE a.uid_b = 0 AND a.device_id = '66C8A202-78AD-467A-B875-0CF57C73BD7B' AND a.nid = 13039 AND a.update_time = \r\ (SELECT max(b.update_time ) FROM wn_fiction_bookshelf AS b WHERE b.uid_b = a.uid_b AND b.device_id = a.device_id AND b.nid = a.nid) limit 1"]
- explain analyze + SQL，发下执行计划。
- 看日志这个 SQL 用了 60 s 跑出来，使用内存 2GB，看起来没有走很好的索引。
explain analyze+sql，有样例吗，这个不太懂，麻烦了。
(root@127.0.0.1) [test]>explain analyze select * from test;
+-------------------+-------+------+---------------------------------------------------------------+---------------------------------+-----------+
| id | count | task | operator info | execution info | memory |
+-------------------+-------+------+---------------------------------------------------------------+---------------------------------+-----------+
| TableReader_5 | 18.00 | root | data:TableScan_4 | time:8.3158ms, loops:2, rows:18 | 187 Bytes |
| └─TableScan_4 | 18.00 | cop | table:test, range:[-inf,+inf], keep order:false, stats:pseudo | time:0s, loops:1, rows:18 | N/A |
+-------------------+-------+------+---------------------------------------------------------------+---------------------------------+-----------+
2 rows in set (0.09 sec)
*************************** 1. row ***************************
id: Projection_13
count: 1.00
task: root
operator info: app_user.wn_fiction_bookshelf.id, app_user.wn_fiction_bookshelf.uid_b, app_user.wn_fiction_bookshelf.device_id, app_user.wn_fiction_bookshelf.nid, app_user.wn_fiction_bookshelf.chapnum, app_user.wn_fiction_bookshelf.create_time, app_user.wn_fiction_bookshelf.create_date, app_user.wn_fiction_bookshelf.update_time, app_user.wn_fiction_bookshelf.editor_id
execution info: time:3.41435348s, loops:1, rows:0, Concurrency:OFF
memory: N/A
disk: N/A
*************************** 2. row ***************************
id: └─Limit_16
count: 1.00
task: root
operator info: offset:0, count:1
execution info: time:3.414352534s, loops:1, rows:0
memory: N/A
disk: N/A
*************************** 3. row ***************************
id: └─Selection_17
count: 1.00
task: root
operator info: eq(app_user.wn_fiction_bookshelf.device_id, "66C8A202-78AD-467A-B875-0CF57C73BD7B"), eq(app_user.wn_fiction_bookshelf.nid, 13039), eq(app_user.wn_fiction_bookshelf.uid_b, 0), eq(app_user.wn_fiction_bookshelf.update_time, Column#19)
execution info: time:3.414350606s, loops:1, rows:0
memory: N/A
disk: N/A
*************************** 4. row ***************************
id: └─StreamAgg_22
count: 1.00
task: root
operator info: group by:app_user.wn_fiction_bookshelf.id, funcs:firstrow(app_user.wn_fiction_bookshelf.id)->app_user.wn_fiction_bookshelf.id, funcs:firstrow(app_user.wn_fiction_bookshelf.uid_b)->app_user.wn_fiction_bookshelf.uid_b, funcs:firstrow(app_user.wn_fiction_bookshelf.device_id)->app_user.wn_fiction_bookshelf.device_id, funcs:firstrow(app_user.wn_fiction_bookshelf.nid)->app_user.wn_fiction_bookshelf.nid, funcs:firstrow(app_user.wn_fiction_bookshelf.chapnum)->app_user.wn_fiction_bookshelf.chapnum, funcs:firstrow(app_user.wn_fiction_bookshelf.create_time)->app_user.wn_fiction_bookshelf.create_time, funcs:firstrow(app_user.wn_fiction_bookshelf.create_date)->app_user.wn_fiction_bookshelf.create_date, funcs:firstrow(app_user.wn_fiction_bookshelf.update_time)->app_user.wn_fiction_bookshelf.update_time, funcs:firstrow(app_user.wn_fiction_bookshelf.editor_id)->app_user.wn_fiction_bookshelf.editor_id, funcs:max(app_user.wn_fiction_bookshelf.update_time)->Column#19
execution info: time:3.414349315s, loops:1, rows:0
memory: N/A
disk: N/A
*************************** 5. row ***************************
id: └─IndexHashJoin_78
count: 5.38
task: root
operator info: left outer join, inner:IndexLookUp_70, outer key:app_user.wn_fiction_bookshelf.device_id, inner key:app_user.wn_fiction_bookshelf.device_id, other cond:eq(app_user.wn_fiction_bookshelf.nid, app_user.wn_fiction_bookshelf.nid), eq(app_user.wn_fiction_bookshelf.uid_b, app_user.wn_fiction_bookshelf.uid_b)
execution info: time:3.414347474s, loops:1, rows:0, Concurrency:4
memory: 114.140625 KB
disk: N/A
*************************** 6. row ***************************
id: ├─TableReader_80
count: 1.00
task: root
operator info: data:TableScan_79
execution info: time:3.414196235s, loops:2, rows:32, rpc num: 1, rpc time:1.610844332s, proc keys:845424
memory: 1.1706557320430875 GB
disk: N/A
*************************** 7. row ***************************
id: │ └─TableScan_79
count: 1.00
task: cop[tikv]
operator info: table:a, range:[-inf,+inf], keep order:true
execution info: time:596ms, loops:830, rows:845423
memory: N/A
disk: N/A
*************************** 8. row ***************************
id: └─IndexLookUp_70
count: 5.38
task: root
operator info:
execution info: time:0ns, loops:0, rows:0
memory: N/A
disk: N/A
*************************** 9. row ***************************
id: ├─IndexScan_68
count: 5.38
task: cop[tikv]
operator info: table:b, index:device_id, range: decided by [eq(app_user.wn_fiction_bookshelf.device_id, app_user.wn_fiction_bookshelf.device_id)], keep order:false
execution info: time:0ns, loops:0, rows:0
memory: N/A
disk: N/A
*************************** 10. row ***************************
id: └─TableScan_69
count: 5.38
task: cop[tikv]
operator info: table:b, keep order:false
execution info: time:0ns, loops:0, rows:0
memory: N/A
disk: N/A
10 rows in set (3.45 sec)
- 辛苦 别用 \G 输出，正常 ; 结尾输出发下。
- 可以的话表结构辛苦也发下吧。
- MySQL 的执行计划可以的话也发下看看。比如：
(root@127.0.0.1) [test]>explain format=json select * from a;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "a",
"access_type": "ALL",
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.20",
"prefix_cost": "1.20",
"data_read_per_join": "608"
},
"used_columns": [
"b"
]
}
}
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
在tidb上执行 ±-------------------------------±------±----------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-----------------------------------------------------------------------------------------±--------------------±-----+ | id | count | task | operator info | execution info | memory | disk | ±-------------------------------±------±----------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-----------------------------------------------------------------------------------------±--------------------±-----+ | Projection_13 | 1.00 | root | app_user.wn_fiction_bookshelf.id, app_user.wn_fiction_bookshelf.uid_b, app_user.wn_fiction_bookshelf.device_id, app_user.wn_fiction_bookshelf.nid, app_user.wn_fiction_bookshelf.chapnum, app_user.wn_fiction_bookshelf.create_time, app_user.wn_fiction_bookshelf.create_date, app_user.wn_fiction_bookshelf.update_time, app_user.wn_fiction_bookshelf.editor_id | time:2.662177694s, loops:1, rows:0, Concurrency:OFF | N/A | N/A | | └─Limit_16 | 1.00 | root | offset:0, count:1 | time:2.662176606s, loops:1, rows:0 | N/A | N/A | | └─Selection_17 | 1.00 | root | eq(app_user.wn_fiction_bookshelf.device_id, “66C8A202-78AD-467A-B875-0CF57C73BD7B”), eq(app_user.wn_fiction_bookshelf.nid, 13039), eq(app_user.wn_fiction_bookshelf.uid_b, 0), eq(app_user.wn_fiction_bookshelf.update_time, Column#19) | time:2.662175606s, loops:1, rows:0 | N/A | N/A | | └─StreamAgg_22 | 1.00 | root | group by:app_user.wn_fiction_bookshelf.id, funcs:firstrow(app_user.wn_fiction_bookshelf.id)->app_user.wn_fiction_bookshelf.id, funcs:firstrow(app_user.wn_fiction_bookshelf.uid_b)->app_user.wn_fiction_bookshelf.uid_b, funcs:firstrow(app_user.wn_fiction_bookshelf.device_id)->app_user.wn_fiction_bookshelf.device_id, funcs:firstrow(app_user.wn_fiction_bookshelf.nid)->app_user.wn_fiction_bookshelf.nid, funcs:firstrow(app_user.wn_fiction_bookshelf.chapnum)->app_user.wn_fiction_bookshelf.chapnum, funcs:firstrow(app_user.wn_fiction_bookshelf.create_time)->app_user.wn_fiction_bookshelf.create_time, funcs:firstrow(app_user.wn_fiction_bookshelf.create_date)->app_user.wn_fiction_bookshelf.create_date, funcs:firstrow(app_user.wn_fiction_bookshelf.update_time)->app_user.wn_fiction_bookshelf.update_time, funcs:firstrow(app_user.wn_fiction_bookshelf.editor_id)->app_user.wn_fiction_bookshelf.editor_id, funcs:max(app_user.wn_fiction_bookshelf.update_time)->Column#19 | time:2.662174583s, loops:1, rows:0 | N/A | N/A | | └─IndexHashJoin_78 | 5.38 | root | left outer join, inner:IndexLookUp_70, outer key:app_user.wn_fiction_bookshelf.device_id, inner key:app_user.wn_fiction_bookshelf.device_id, other cond:eq(app_user.wn_fiction_bookshelf.nid, app_user.wn_fiction_bookshelf.nid), eq(app_user.wn_fiction_bookshelf.uid_b, app_user.wn_fiction_bookshelf.uid_b) | time:2.662172716s, loops:1, rows:0, Concurrency:4 | 114.140625 KB | N/A | | ├─TableReader_80 | 1.00 | root | data:TableScan_79 | time:2.662049587s, loops:2, rows:32, rpc num: 1, rpc time:2.661810421s, proc keys:845423 | 1.26879776827991 GB | N/A | | │ └─TableScan_79 | 1.00 | cop[tikv] | table:a, range:[-inf,+inf], keep order:true | time:940ms, loops:830, rows:845423 | N/A | N/A | | └─IndexLookUp_70 | 5.38 | root | | time:0ns, loops:0, rows:0 | N/A | N/A | | ├─IndexScan_68 | 5.38 | cop[tikv] | table:b, index:device_id, range: decided by [eq(app_user.wn_fiction_bookshelf.device_id, app_user.wn_fiction_bookshelf.device_id)], keep order:false | time:0ns, loops:0, rows:0 | N/A | N/A | | └─TableScan_69 | 5.38 | cop[tikv] | table:b, keep order:false | time:0ns, loops:0, rows:0 | N/A | N/A | ±-------------------------------±------±----------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-----------------------------------------------------------------------------------------±--------------------±-----+ 10 rows in set (2.66 sec)
在mysql上执行
{
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “1.20”
},
“table”: {
“table_name”: “a”,
“access_type”: “ref”,
“possible_keys”: [
“device_id”,
“nid”,
“idx_uid_b”
],
“key”: “device_id”,
“used_key_parts”: [
“device_id”
],
“key_length”: “1022”,
“ref”: [
“const”
],
“rows_examined_per_scan”: 1,
“rows_produced_per_join”: 0,
“filtered”: “16.10”,
“cost_info”: {
“read_cost”: “1.00”,
“eval_cost”: “0.03”,
“prefix_cost”: “1.20”,
“data_read_per_join”: “171”
},
“used_columns”: [
“id”,
“uid_b”,
“device_id”,
“nid”,
“chapnum”,
“create_time”,
“create_date”,
“update_time”,
“editor_id”
],
“attached_condition”: “((app_user.a.nid = 13039) and (app_user.a.uid_b = 0) and (app_user.a.update_time = (/* select#2 */ select max(app_user.b.update_time) from app_user.wn_fiction_bookshelf b where ((app_user.b.uid_b = app_user.a.uid_b) and (app_user.b.device_id = app_user.a.device_id) and (app_user.b.nid = app_user.a.nid)))))”,
“attached_subqueries”: [
{
“dependent”: true,
“cacheable”: false,
“query_block”: {
“select_id”: 2,
“cost_info”: {
“query_cost”: “7.22”
},
“table”: {
“table_name”: “b”,
“access_type”: “ref”,
“possible_keys”: [
“device_id”,
“nid”,
“idx_uid_b”
],
“key”: “device_id”,
“used_key_parts”: [
“device_id”
],
“key_length”: “1022”,
“ref”: [
“app_user.a.device_id”
],
“rows_examined_per_scan”: 6,
“rows_produced_per_join”: 0,
“filtered”: “1.00”,
“cost_info”: {
“read_cost”: “6.02”,
“eval_cost”: “0.01”,
“prefix_cost”: “7.22”,
“data_read_per_join”: “64”
},
“used_columns”: [
“uid_b”,
“device_id”,
“nid”,
“update_time”
],
“attached_condition”: “((app_user.b.uid_b = app_user.a.uid_b) and (app_user.b.nid = app_user.a.nid))”
}
}
}
]
}
}
}
- 初步分析了下两个执行计划，看起来我们走了全表扫。
- 辛苦拿下wn_fiction_bookshelf 表的统计信息和表结构：https://pingcap.com/docs-cn/stable/reference/performance/statistics/#导出统计信息
- 拿完统计信息和表结构，可以手动 analyze 一下wn_fiction_bookshelf 表，看看效率是否会提升。