tidb-server的语句级OOM问题

v6.5.2(包括目前的7.1)版本默认配置虽然利用paging能力解决了region过大导致大量数据返回给tidb-server导致的oom问题,但是即使使用paging能力还是存在如下问题:

    1. 默认tidb_enable_chunk_rpc=ON下,数据类型内存放大问题,尤其decimal极端场景下会放大40倍。
    1. union all、tidb_index_lookup_concurrency、tidb_index_lookup_size、tidb_hash_join_concurrency等并发参数导致distsql请求的放大。
    1. tidb_min_paging_size、tidb_max_paging_size,行大小不固定,如果大量decimal类型数据返回会导致内存急剧升高。
    1. hashAgg、sort等落盘速度跟不上tikv传过来数据的速度导致RPC返回来的数据大量堆积。
    1. hashJoin、hashAgg等虽然可以落盘,但是行指针均存放在内存中,数据量过大时无法全部缓存指针也会发生OOM,这种在实际生产中有些较大数据量处理还是很容易发生,
      发生了再调整参数属于事后处理而且不知道哪些需要调调多少。该场景需要较大数据量模拟,暂不测试。
      相关案例参考:当HashJoin的BuildSide过大时容易OOM

总结为:内存放大、并发放大、一个paging(多行)内存不固定、tikv-client缓存数据未与算子分隔内存管控太粗导致的语句级OOM问题。

根据该特性模拟较为极端SQL语句,希望产品可以优化能让下面这种语句在默认配置下可以不发生OOM(慢慢执行,不发生OOM,最终执行完毕)。

涉及默认配置项为:


mysql> show variables like '%tidb_mem_quota_query%';
+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| tidb_mem_quota_query | 1073741824 |
+----------------------+------------+
1 row in set (0.00 sec)
mysql> show variables like 'tidb_enable_chunk_rpc';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| tidb_enable_chunk_rpc | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'tidb_executor_concurrency';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| tidb_executor_concurrency | 5     |
+---------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'tidb_distsql_scan_concurrency';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| tidb_distsql_scan_concurrency | 15    |
+-------------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'tidb_index_lookup_size';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| tidb_index_lookup_size | 20000 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'tidb_index_lookup_concurrency';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| tidb_index_lookup_concurrency | -1    |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%paging%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| tidb_enable_paging   | ON    |
| tidb_max_paging_size | 50000 |
| tidb_min_paging_size | 128   |
+----------------------+-------+
3 rows in set (0.01 sec)

加载tpch为1的测试数据,并对lineitem表添加decimal字段

time tiup bench tpch -D  tpch1 -H 192.168.31.201 -P 4000 -U root -p ''  -T 5  prepare --ignore-error --sf=1
--添加decimal字段(内存放大效应)
alter table lineitem add column c1 decimal(5,2) not null default 1.0;
alter table lineitem add column c2 decimal(5,2) not null default 1.0;
alter table lineitem add column c3 decimal(5,2) not null default 1.0;
alter table lineitem add column c4 decimal(5,2) not null default 1.0;
alter table lineitem add column c5 decimal(5,2) not null default 1.0;
alter table lineitem add column c6 decimal(5,2) not null default 1.0;
alter table lineitem add column c7 decimal(5,2) not null default 1.0;
alter table lineitem add column c8 decimal(5,2) not null default 1.0;
alter table lineitem add column c9 decimal(5,2) not null default 1.0;
alter table lineitem add column c10 decimal(5,2) not null default 1.0;
alter table lineitem add column c11 decimal(5,2) not null default 1.0;
alter table lineitem add column c12 decimal(5,2) not null default 1.0;
alter table lineitem add column c13 decimal(5,2) not null default 1.0;
alter table lineitem add column c14 decimal(5,2) not null default 1.0;
alter table lineitem add column c15 decimal(5,2) not null default 1.0;
alter table lineitem add column c16 decimal(5,2) not null default 1.0;
alter table lineitem add column c17 decimal(5,2) not null default 1.0;
alter table lineitem add column c18 decimal(5,2) not null default 1.0;
alter table lineitem add column c19 decimal(5,2) not null default 1.0;
alter table lineitem add column c20 decimal(5,2) not null default 1.0;
--添加索引(索引
alter table lineitem add index idx1 (L_RETURNFLAG,L_SHIPDATE);

表结构如下:

mysql> show create table lineitem \G
*************************** 1. row ***************************
       Table: lineitem
Create Table: CREATE TABLE `lineitem` (
  `L_ORDERKEY` bigint(20) NOT NULL,
  `L_PARTKEY` bigint(20) NOT NULL,
  `L_SUPPKEY` bigint(20) NOT NULL,
  `L_LINENUMBER` bigint(20) NOT NULL,
  `L_QUANTITY` decimal(15,2) NOT NULL,
  `L_EXTENDEDPRICE` decimal(15,2) NOT NULL,
  `L_DISCOUNT` decimal(15,2) NOT NULL,
  `L_TAX` decimal(15,2) NOT NULL,
  `L_RETURNFLAG` char(1) NOT NULL,
  `L_LINESTATUS` char(1) NOT NULL,
  `L_SHIPDATE` date NOT NULL,
  `L_COMMITDATE` date NOT NULL,
  `L_RECEIPTDATE` date NOT NULL,
  `L_SHIPINSTRUCT` char(25) NOT NULL,
  `L_SHIPMODE` char(10) NOT NULL,
  `L_COMMENT` varchar(44) NOT NULL,
  `c1` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c2` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c3` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c4` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c5` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c6` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c7` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c8` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c9` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c10` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c11` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c12` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c13` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c14` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c15` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c16` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c17` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c18` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c19` decimal(5,2) NOT NULL DEFAULT '1.0',
  `c20` decimal(5,2) NOT NULL DEFAULT '1.0',
  PRIMARY KEY (`L_ORDERKEY`,`L_LINENUMBER`) /*T![clustered_index] CLUSTERED */,
  KEY `idx1` (`L_RETURNFLAG`,`L_SHIPDATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
--表数据量为:
mysql> select count(*) from lineitem;
+----------+
| count(*) |
+----------+
|  6001215 |
+----------+
1 row in set (0.67 sec)

mysql> select count(*) from orders;
+----------+
| count(*) |
+----------+
|  1500000 |
+----------+
1 row in set (0.24 sec)

mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
|   150000 |
+----------+
1 row in set (0.11 sec)
--具体可以创建tpch --sf=1 的测试数据来观察。

测试SQL语句

--语句中包含union all 多个union all语句加剧distsql_scan
--语句最外层做笛卡尔积,但是probe端记录数为1000,build端数据为644207 * 5 = 3221035,因此即使笛卡尔积总扫描次数为:3221035 * 1000= 32亿次,不算多,理论上可以跑的完。
--做笛卡尔积的原因是,在较多场景中会存在此类情况,比如数据的多对多,null情况下的反半连接,错误的执行计划(评估数据量较少,可能走笛卡尔积更优)等等,笛卡尔积形式如果可以管控住其它较多场景也就涵盖住了。

explain analyze select /*+ HASH_JOIN_BUILD(b) */ * from 
(select /*+ hash_join(a,b,c) */ a.*,c.* from customer a left join orders b  on  a.C_CUSTKEY=b.O_ORDERKEY left join orders c on b.O_ORDERKEY=c.O_ORDERKEY limit 1000) a
left join
(
  select * from (select /*+use_index(lineitem,idx1) */ row_number()over(partition by L_ORDERKEY) as nbr ,lineitem.* from lineitem where L_RETURNFLAG=(select L_RETURNFLAG from lineitem limit 1) and L_SHIPDATE < date_sub((select max(L_SHIPDATE) from lineitem),interval 1 year)) a where nbr=1
  union all
  select * from (select /*+use_index(lineitem,idx1) */ row_number()over(partition by L_ORDERKEY) as nbr ,lineitem.* from lineitem where L_RETURNFLAG=(select L_RETURNFLAG from lineitem limit 1) and L_SHIPDATE < date_sub((select max(L_SHIPDATE) from lineitem),interval 1 year)) a where nbr=1
  union all
  select * from (select /*+use_index(lineitem,idx1) */ row_number()over(partition by L_ORDERKEY) as nbr ,lineitem.* from lineitem where L_RETURNFLAG=(select L_RETURNFLAG from lineitem limit 1) and L_SHIPDATE < date_sub((select max(L_SHIPDATE) from lineitem),interval 1 year)) a where nbr=1
  union all
  select * from (select /*+use_index(lineitem,idx1) */ row_number()over(partition by L_ORDERKEY) as nbr ,lineitem.* from lineitem where L_RETURNFLAG=(select L_RETURNFLAG from lineitem limit 1) and L_SHIPDATE < date_sub((select max(L_SHIPDATE) from lineitem),interval 1 year)) a where nbr=1
  union all
  select * from (select /*+use_index(lineitem,idx1) */ row_number()over(partition by L_ORDERKEY) as nbr ,lineitem.* from lineitem where L_RETURNFLAG=(select L_RETURNFLAG from lineitem limit 1) and L_SHIPDATE < date_sub((select max(L_SHIPDATE) from lineitem),interval 1 year)) a where nbr=1
) b on 1=1
order by b.L_QUANTITY desc,b.L_EXTENDEDPRICE asc,b.L_DISCOUNT desc limit 10000000,10;

整个语句的执行计划:

+-----------------------------------------------------+---------------+-----------+------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                                  | estRows       | task      | access object                                        | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+-----------------------------------------------------+---------------+-----------+------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TopN_273                                            | 10.00         | root      |                                                      | Column#597:desc, Column#598, Column#599:desc, offset:10000000, count:10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| └─HashJoin_278                                      | 5502754848.24 | root      |                                                      | CARTESIAN left outer join                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   ├─Union_316(Build)                                | 5502754.85    | root      |                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|   │ ├─Projection_317                                | 1100550.97    | root      |                                                      | Column#139, tpch1.lineitem.l_orderkey, tpch1.lineitem.l_partkey, tpch1.lineitem.l_suppkey, tpch1.lineitem.l_linenumber, tpch1.lineitem.l_quantity, tpch1.lineitem.l_extendedprice, tpch1.lineitem.l_discount, tpch1.lineitem.l_tax, tpch1.lineitem.l_returnflag, tpch1.lineitem.l_linestatus, tpch1.lineitem.l_shipdate, tpch1.lineitem.l_commitdate, tpch1.lineitem.l_receiptdate, tpch1.lineitem.l_shipinstruct, tpch1.lineitem.l_shipmode, tpch1.lineitem.l_comment, tpch1.lineitem.c1, tpch1.lineitem.c2, tpch1.lineitem.c3, tpch1.lineitem.c4, tpch1.lineitem.c5, tpch1.lineitem.c6, tpch1.lineitem.c7, tpch1.lineitem.c8, tpch1.lineitem.c9, tpch1.lineitem.c10, tpch1.lineitem.c11, tpch1.lineitem.c12, tpch1.lineitem.c13, tpch1.lineitem.c14, tpch1.lineitem.c15, tpch1.lineitem.c16, tpch1.lineitem.c17, tpch1.lineitem.c18, tpch1.lineitem.c19, tpch1.lineitem.c20 |
|   │ │ └─Selection_318                               | 1100550.97    | root      |                                                      | eq(Column#139, 1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │ │   └─Shuffle_324                               | 1375688.71    | root      |                                                      | execution info: concurrency:5, data sources:[IndexLookUp_322]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   │ │     └─Window_319                              | 1375688.71    | root      |                                                      | row_number()->Column#139 over(partition by tpch1.lineitem.l_orderkey rows between current row and current row)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|   │ │       └─Sort_323                              | 1375688.71    | root      |                                                      | tpch1.lineitem.l_orderkey                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   │ │         └─IndexLookUp_322                     | 1375688.71    | root      |                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|   │ │           ├─IndexRangeScan_320(Build)         | 1375688.71    | cop[tikv] | table:lineitem, index:idx1(L_RETURNFLAG, L_SHIPDATE) | range:["A" -inf,"A" 1997-12-01), keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │ │           └─TableRowIDScan_321(Probe)         | 1375688.71    | cop[tikv] | table:lineitem                                       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|   │ ├─Projection_325                                | 1100550.97    | root      |                                                      | Column#252, tpch1.lineitem.l_orderkey, tpch1.lineitem.l_partkey, tpch1.lineitem.l_suppkey, tpch1.lineitem.l_linenumber, tpch1.lineitem.l_quantity, tpch1.lineitem.l_extendedprice, tpch1.lineitem.l_discount, tpch1.lineitem.l_tax, tpch1.lineitem.l_returnflag, tpch1.lineitem.l_linestatus, tpch1.lineitem.l_shipdate, tpch1.lineitem.l_commitdate, tpch1.lineitem.l_receiptdate, tpch1.lineitem.l_shipinstruct, tpch1.lineitem.l_shipmode, tpch1.lineitem.l_comment, tpch1.lineitem.c1, tpch1.lineitem.c2, tpch1.lineitem.c3, tpch1.lineitem.c4, tpch1.lineitem.c5, tpch1.lineitem.c6, tpch1.lineitem.c7, tpch1.lineitem.c8, tpch1.lineitem.c9, tpch1.lineitem.c10, tpch1.lineitem.c11, tpch1.lineitem.c12, tpch1.lineitem.c13, tpch1.lineitem.c14, tpch1.lineitem.c15, tpch1.lineitem.c16, tpch1.lineitem.c17, tpch1.lineitem.c18, tpch1.lineitem.c19, tpch1.lineitem.c20 |
|   │ │ └─Selection_326                               | 1100550.97    | root      |                                                      | eq(Column#252, 1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │ │   └─Shuffle_332                               | 1375688.71    | root      |                                                      | execution info: concurrency:5, data sources:[IndexLookUp_330]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   │ │     └─Window_327                              | 1375688.71    | root      |                                                      | row_number()->Column#252 over(partition by tpch1.lineitem.l_orderkey rows between current row and current row)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|   │ │       └─Sort_331                              | 1375688.71    | root      |                                                      | tpch1.lineitem.l_orderkey                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   │ │         └─IndexLookUp_330                     | 1375688.71    | root      |                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|   │ │           ├─IndexRangeScan_328(Build)         | 1375688.71    | cop[tikv] | table:lineitem, index:idx1(L_RETURNFLAG, L_SHIPDATE) | range:["A" -inf,"A" 1997-12-01), keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │ │           └─TableRowIDScan_329(Probe)         | 1375688.71    | cop[tikv] | table:lineitem                                       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|   │ ├─Projection_333                                | 1100550.97    | root      |                                                      | Column#365, tpch1.lineitem.l_orderkey, tpch1.lineitem.l_partkey, tpch1.lineitem.l_suppkey, tpch1.lineitem.l_linenumber, tpch1.lineitem.l_quantity, tpch1.lineitem.l_extendedprice, tpch1.lineitem.l_discount, tpch1.lineitem.l_tax, tpch1.lineitem.l_returnflag, tpch1.lineitem.l_linestatus, tpch1.lineitem.l_shipdate, tpch1.lineitem.l_commitdate, tpch1.lineitem.l_receiptdate, tpch1.lineitem.l_shipinstruct, tpch1.lineitem.l_shipmode, tpch1.lineitem.l_comment, tpch1.lineitem.c1, tpch1.lineitem.c2, tpch1.lineitem.c3, tpch1.lineitem.c4, tpch1.lineitem.c5, tpch1.lineitem.c6, tpch1.lineitem.c7, tpch1.lineitem.c8, tpch1.lineitem.c9, tpch1.lineitem.c10, tpch1.lineitem.c11, tpch1.lineitem.c12, tpch1.lineitem.c13, tpch1.lineitem.c14, tpch1.lineitem.c15, tpch1.lineitem.c16, tpch1.lineitem.c17, tpch1.lineitem.c18, tpch1.lineitem.c19, tpch1.lineitem.c20 |
|   │ │ └─Selection_334                               | 1100550.97    | root      |                                                      | eq(Column#365, 1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │ │   └─Shuffle_340                               | 1375688.71    | root      |                                                      | execution info: concurrency:5, data sources:[IndexLookUp_338]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   │ │     └─Window_335                              | 1375688.71    | root      |                                                      | row_number()->Column#365 over(partition by tpch1.lineitem.l_orderkey rows between current row and current row)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|   │ │       └─Sort_339                              | 1375688.71    | root      |                                                      | tpch1.lineitem.l_orderkey                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   │ │         └─IndexLookUp_338                     | 1375688.71    | root      |                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|   │ │           ├─IndexRangeScan_336(Build)         | 1375688.71    | cop[tikv] | table:lineitem, index:idx1(L_RETURNFLAG, L_SHIPDATE) | range:["A" -inf,"A" 1997-12-01), keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │ │           └─TableRowIDScan_337(Probe)         | 1375688.71    | cop[tikv] | table:lineitem                                       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|   │ ├─Projection_341                                | 1100550.97    | root      |                                                      | Column#478, tpch1.lineitem.l_orderkey, tpch1.lineitem.l_partkey, tpch1.lineitem.l_suppkey, tpch1.lineitem.l_linenumber, tpch1.lineitem.l_quantity, tpch1.lineitem.l_extendedprice, tpch1.lineitem.l_discount, tpch1.lineitem.l_tax, tpch1.lineitem.l_returnflag, tpch1.lineitem.l_linestatus, tpch1.lineitem.l_shipdate, tpch1.lineitem.l_commitdate, tpch1.lineitem.l_receiptdate, tpch1.lineitem.l_shipinstruct, tpch1.lineitem.l_shipmode, tpch1.lineitem.l_comment, tpch1.lineitem.c1, tpch1.lineitem.c2, tpch1.lineitem.c3, tpch1.lineitem.c4, tpch1.lineitem.c5, tpch1.lineitem.c6, tpch1.lineitem.c7, tpch1.lineitem.c8, tpch1.lineitem.c9, tpch1.lineitem.c10, tpch1.lineitem.c11, tpch1.lineitem.c12, tpch1.lineitem.c13, tpch1.lineitem.c14, tpch1.lineitem.c15, tpch1.lineitem.c16, tpch1.lineitem.c17, tpch1.lineitem.c18, tpch1.lineitem.c19, tpch1.lineitem.c20 |
|   │ │ └─Selection_342                               | 1100550.97    | root      |                                                      | eq(Column#478, 1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │ │   └─Shuffle_348                               | 1375688.71    | root      |                                                      | execution info: concurrency:5, data sources:[IndexLookUp_346]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   │ │     └─Window_343                              | 1375688.71    | root      |                                                      | row_number()->Column#478 over(partition by tpch1.lineitem.l_orderkey rows between current row and current row)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|   │ │       └─Sort_347                              | 1375688.71    | root      |                                                      | tpch1.lineitem.l_orderkey                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   │ │         └─IndexLookUp_346                     | 1375688.71    | root      |                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|   │ │           ├─IndexRangeScan_344(Build)         | 1375688.71    | cop[tikv] | table:lineitem, index:idx1(L_RETURNFLAG, L_SHIPDATE) | range:["A" -inf,"A" 1997-12-01), keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │ │           └─TableRowIDScan_345(Probe)         | 1375688.71    | cop[tikv] | table:lineitem                                       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|   │ └─Projection_349                                | 1100550.97    | root      |                                                      | Column#591, tpch1.lineitem.l_orderkey, tpch1.lineitem.l_partkey, tpch1.lineitem.l_suppkey, tpch1.lineitem.l_linenumber, tpch1.lineitem.l_quantity, tpch1.lineitem.l_extendedprice, tpch1.lineitem.l_discount, tpch1.lineitem.l_tax, tpch1.lineitem.l_returnflag, tpch1.lineitem.l_linestatus, tpch1.lineitem.l_shipdate, tpch1.lineitem.l_commitdate, tpch1.lineitem.l_receiptdate, tpch1.lineitem.l_shipinstruct, tpch1.lineitem.l_shipmode, tpch1.lineitem.l_comment, tpch1.lineitem.c1, tpch1.lineitem.c2, tpch1.lineitem.c3, tpch1.lineitem.c4, tpch1.lineitem.c5, tpch1.lineitem.c6, tpch1.lineitem.c7, tpch1.lineitem.c8, tpch1.lineitem.c9, tpch1.lineitem.c10, tpch1.lineitem.c11, tpch1.lineitem.c12, tpch1.lineitem.c13, tpch1.lineitem.c14, tpch1.lineitem.c15, tpch1.lineitem.c16, tpch1.lineitem.c17, tpch1.lineitem.c18, tpch1.lineitem.c19, tpch1.lineitem.c20 |
|   │   └─Selection_350                               | 1100550.97    | root      |                                                      | eq(Column#591, 1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │     └─Shuffle_356                               | 1375688.71    | root      |                                                      | execution info: concurrency:5, data sources:[IndexLookUp_354]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   │       └─Window_351                              | 1375688.71    | root      |                                                      | row_number()->Column#591 over(partition by tpch1.lineitem.l_orderkey rows between current row and current row)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|   │         └─Sort_355                              | 1375688.71    | root      |                                                      | tpch1.lineitem.l_orderkey                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   │           └─IndexLookUp_354                     | 1375688.71    | root      |                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|   │             ├─IndexRangeScan_352(Build)         | 1375688.71    | cop[tikv] | table:lineitem, index:idx1(L_RETURNFLAG, L_SHIPDATE) | range:["A" -inf,"A" 1997-12-01), keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │             └─TableRowIDScan_353(Probe)         | 1375688.71    | cop[tikv] | table:lineitem                                       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|   └─Limit_281(Probe)                                | 1000.00       | root      |                                                      | offset:0, count:1000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     └─HashJoin_292                                  | 1000.00       | root      |                                                      | left outer join, equal:[eq(tpch1.orders.o_orderkey, tpch1.orders.o_orderkey)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|       ├─Limit_295(Build)                            | 1000.00       | root      |                                                      | offset:0, count:1000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|       │ └─HashJoin_306                              | 1000.00       | root      |                                                      | left outer join, equal:[eq(tpch1.customer.c_custkey, tpch1.orders.o_orderkey)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|       │   ├─Limit_307(Build)                        | 1000.00       | root      |                                                      | offset:0, count:1000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|       │   │ └─TableReader_311                       | 1000.00       | root      |                                                      | data:Limit_310                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|       │   │   └─Limit_310                           | 1000.00       | cop[tikv] |                                                      | offset:0, count:1000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|       │   │     └─TableFullScan_309                 | 1000.00       | cop[tikv] | table:a                                              | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|       │   └─TableReader_313(Probe)                  | 1872736.00    | root      |                                                      | data:TableFullScan_312                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|       │     └─TableFullScan_312                     | 1872736.00    | cop[tikv] | table:b                                              | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|       └─TableReader_315(Probe)                      | 1872736.00    | root      |                                                      | data:TableFullScan_314                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|         └─TableFullScan_314                         | 1872736.00    | cop[tikv] | table:c                                              | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+-----------------------------------------------------+---------------+-----------+------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
55 rows in set (3.48 sec)

其中HashJoin_271的Build端是由多个union all组成,union中的一个子语句(如:Projection_310)的执行信息如下:

mysql> explain analyze select * from (select /*+use_index(lineitem,idx1) */ row_number()over(partition by L_ORDERKEY) as nbr ,lineitem.* from lineitem where L_RETURNFLAG=(select L_RETURNFLAG from lineitem limit 1) and L_SHIPDATE < date_sub((select max(L_SHIPDATE) from lineitem),interval 1 year)) a where nbr=1;
+--------------------------------------------+------------+---------+-----------+------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+
| id                                         | estRows    | actRows | task      | access object                                        | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | memory   | disk     |
+--------------------------------------------+------------+---------+-----------+------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+
| Projection_50                              | 1100550.97 | 644207  | root      |                                                      | time:1m11.9s, loops:631, Concurrency:5                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | Column#113, tpch1.lineitem.l_orderkey, tpch1.lineitem.l_partkey, tpch1.lineitem.l_suppkey, tpch1.lineitem.l_linenumber, tpch1.lineitem.l_quantity, tpch1.lineitem.l_extendedprice, tpch1.lineitem.l_discount, tpch1.lineitem.l_tax, tpch1.lineitem.l_returnflag, tpch1.lineitem.l_linestatus, tpch1.lineitem.l_shipdate, tpch1.lineitem.l_commitdate, tpch1.lineitem.l_receiptdate, tpch1.lineitem.l_shipinstruct, tpch1.lineitem.l_shipmode, tpch1.lineitem.l_comment, tpch1.lineitem.c1, tpch1.lineitem.c2, tpch1.lineitem.c3, tpch1.lineitem.c4, tpch1.lineitem.c5, tpch1.lineitem.c6, tpch1.lineitem.c7, tpch1.lineitem.c8, tpch1.lineitem.c9, tpch1.lineitem.c10, tpch1.lineitem.c11, tpch1.lineitem.c12, tpch1.lineitem.c13, tpch1.lineitem.c14, tpch1.lineitem.c15, tpch1.lineitem.c16, tpch1.lineitem.c17, tpch1.lineitem.c18, tpch1.lineitem.c19, tpch1.lineitem.c20 | 11.5 MB  | N/A      |
| └─Selection_51                             | 1100550.97 | 644207  | root      |                                                      | time:1m11.9s, loops:631                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | eq(Column#113, 1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | 1.13 MB  | N/A      |
|   └─Shuffle_57                             | 1375688.71 | 1478493 | root      |                                                      | time:1m10.9s, loops:1448, ShuffleConcurrency:5                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | execution info: concurrency:5, data sources:[IndexLookUp_55]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | N/A      | N/A      |
|     └─Window_52                            | 1375688.71 | 1478493 | root      |                                                      | time:3m53.5s, loops:1451                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | row_number()->Column#113 over(partition by tpch1.lineitem.l_orderkey rows between current row and current row)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | N/A      | N/A      |
|       └─Sort_56                            | 1375688.71 | 1478493 | root      |                                                      | time:3m53s, loops:1451                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | tpch1.lineitem.l_orderkey                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | 190.1 MB | 223.9 MB |
|         └─ShuffleReceiver_58               | 1375688.71 | 1478493 | root      |                                                      | time:1m1s, loops:1451                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | N/A      | N/A      |
|           └─IndexLookUp_55                 | 1375688.71 | 1478493 | root      |                                                      | time:5.72s, loops:1445, index_task: {total_time: 12.4s, fetch_handle: 5.53s, build: 337.3µs, wait: 6.87s}, table_task: {total_time: 1m2.4s, num: 76, concurrency: 5}, next: {wait_index: 2.05ms, wait_table_lookup_build: 8.17ms, wait_table_lookup_resp: 4.34s}                                                                                                                                                                                                                                                                                                           |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | 270.7 MB | N/A      |
|             ├─IndexRangeScan_53(Build)     | 1375688.71 | 1478493 | cop[tikv] | table:lineitem, index:idx1(L_RETURNFLAG, L_SHIPDATE) | time:3.58s, loops:1452, cop_task: {num: 54, max: 711.2ms, min: 538.1µs, avg: 206.6ms, p95: 649ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 8.5s, tot_wait: 1.84s, rpc_num: 54, rpc_time: 11.1s, copr_cache: disabled, distsql_concurrency: 15}, tikv_task:{proc max:594ms, min:0s, avg: 157ms, p80:391ms, p95:522ms, iters:1655, tasks:54}, scan_detail: {total_process_keys: 1478493, total_process_keys_size: 110886975, total_keys: 1478547, get_snapshot_time: 1.13ms, rocksdb: {key_skipped_count: 1478493, block: {cache_hit_count: 1719}}}             | range:["A" -inf,"A" 1997-12-01), keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | N/A      | N/A      |
|             └─TableRowIDScan_54(Probe)     | 1375688.71 | 1478493 | cop[tikv] | table:lineitem                                       | time:53.5s, loops:1524, cop_task: {num: 988, max: 1.47s, min: 17.4ms, avg: 414.9ms, p95: 722.9ms, max_proc_keys: 1756, p95_proc_keys: 1694, tot_proc: 5m23.2s, tot_wait: 59.2s, rpc_num: 988, rpc_time: 6m49.6s, copr_cache: disabled, distsql_concurrency: 15}, tikv_task:{proc max:1.35s, min:1ms, avg: 324.8ms, p80:472ms, p95:622ms, iters:5792, tasks:988}, scan_detail: {total_process_keys: 1478493, total_process_keys_size: 287479930, total_keys: 2956986, get_snapshot_time: 360.7ms, rocksdb: {key_skipped_count: 1478493, block: {cache_hit_count: 9879914}}} | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | N/A      | N/A      |
+--------------------------------------------+------------+---------+-----------+------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+
9 rows in set (1 min 12.71 sec)

期待官方对tidb-server的oom管控方面进一步优化,如果在默认配置下上面这条语句可以正常运行不会被oom-killer掉,那么我认为在内存管控方面已经能满足绝大多数的场景需求了。
优化方式比如:
1、划分统一缓冲池,tikv-client数据可溢出到缓冲池,缓冲池统一管理落盘。相关需求帖子参考:临时表能力的增强与tidb-server层缓存机制的建立
2、tikv-client独立内存管控(和算子分开,设置单独追踪链和溢出机制),根据算子的执行顺序和特性设置优先级(比如probe端需要low-speed即可,build需要high-speed)优先级不同内存分配不同来保证尽量少用内存的情况下速度不影响,并在语句整体上控制tikv-client使用量
3、内存评估,通过上一批返回数据评估行大小来根据内存大小自适应调整行记录请求。
4、算子计算多对多记录时候分批返回给父算子(比如外表一行匹配内表1kw行,占用大量内存,可多批次返回)。

上面建议纯属发散,总之比较迫切的希望tidb-server在oom方面进一步优化,减少语句级oom-killer的发生。

2 个赞

上面这种语句
tidb_mem_quota_query=1GB时,直接语句oom。
tidb_mem_quota_query=10GB时,当内存使用达到10GB时,会溢出到磁盘,但是落盘速度跟不上tikv返回速度还是发生oom。
tidb_mem_quota_query=-1时(不控制),机器内存64GB,达到tidb_server_memory_limit_gc_trigger=0.7时候触发GC,虚拟机CPU较少为12C,pd、tidb、tikv混合部署,加上GC较为吃资源导致机器基本卡死,只能手工kill -9 tidb-server了。

学习一下

主动去killl

for list in /server/mysql5.7/bin/mysql -hXX.XX.XX.XX -p'XXXX' -vvv -e " select id from INFORMATION_SCHEMA.processlist a where a.info is not null and (mem >=11474836480 or time >600);" |grep -Ev 'id|ID|iD|Id' |awk -F "|" '{print $2}'
do
echo $list
/server/mysql5.7/bin/mysql -hXX.XX.XX.XX -p’XXXX’ -vvv -e " select id,time,info,mem from INFORMATION_SCHEMA.processlist a where id=$list and a.info is not null;" >/sh/tidbkilllog/date +%s.log
/server/mysql5.7/bin/mysql -hXX.XX.XX.XX -p’XXXX’ -vvv -e " kill tidb $list ;"
done;

这是个点。。

这个场景再实际使用中,我碰到过。
关键在于 limit 10000000,10
只要这个limit前面的数字足够大,基本稳定触发oom。
这个sql确实值得优化一下。不推荐这个limit用法没问题,但不应该到稳定触发oom的地步。

也公正的讲讲不推荐这个limit用法的原因,因为一般这种查询多个被union all的表上都有时间字段的。例如上面这个例子上也能看到L_SHIPDATE 这个字段。所以大部分这种查询的分页场景下,时间字段和limit这两个条件是有相互替代的可能性的。
大部分情况下,要缩小limit前面这个数字还可以通过缩小时间区间的方式,只要limit 前面这个数字变小到一定程度就不会再触发oom了。

就是故意让其OOM的。在我不设置让其OOM的情况下,如果发生了OOM我理解就应该算产品缺陷。

1 个赞

记下先,防踩坑

对于烂SQL就应该被kill ,然后优化

什么是烂语句并不是产品说了算,不能说占用内存的就算烂语句。DBA可以自己写脚本来处理,或者说产品有这种杀掉“你定义好的烂语句范围”功能,那是增强功能,而不是现在的OOM问题。
我只是通过模拟“烂语句”来让其发生OOM,实际上这个“烂语句”的每一个部分在特定场合都是业务需求,比如多个indexLookup+union all操作,再比如单纯的大表indexLookup返回给客户端多个金额字段操作,前端为了避免占用内存过多用了cursor fetch来一批一批处理问题等导致后端堆积都可能发生OOM问题,单这些单独拿出来都不算“烂语句”吧。

2 个赞

赞同,执行时间长点也不能莫名地OOM

8.0版本作为实现特性实现了并行hashAGG的落盘,又进步了一点,这记录下。
对应的PR为:https://github.com/pingcap/tidb/pull/49074

1 个赞