TiDB heap memory usage is over 10 GB

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

  • 【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\n(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))” } } } ] } } }