临时表能力的增强与tidb-server层缓存机制的建立

人如其名 · 2023 年3 月 12 日 06:36

需求反馈
请清晰准确地描述问题场景、需求行为及背景信息，更有利于产品同学及时跟进需求
【需求涉及的问题场景】跑批中间结果集处理，需要用到临时表

【期望的需求行为】临时表可以做统计信息搜集、可以创建索引、可以落盘、可以不受大事务限制。

【需求可替代方案】目前是用普通表来完成，但是普通表导入性能过慢，不适合临时存储中间数据

关于临时表的支持：https://docs.pingcap.com/zh/tidb/dev/temporary-tables
其中也有一些限制，但是这几个版本中并没有什么优化进展，是否应该针对比较重要场景进一步做优化呢？
用到临时表的一个最多的场景就是大量中间结果集的生成放入到临时表中，然后这个临时表再和其它表进行关联加工处理数据，因此常用的一些技术点就是：1、要支持大容量；2、要支持搜集信息搜集方便和其它普通表做join；3、要支持创建索引进行数据过滤和某些场景走indexlookupjoin；4、临时表本身没有写日志等需求，因此不应该受到大事务限制。针对这几个点目前tidb都需要加强优化，以方便在真正批次场景中可以落地使用。针对这几个问题逐个测试如下：
1、目前不支持大容量：所谓大容量，可能产生的临时表会超过十几个GB，不应该都在内存中，应该进行落盘（这里官方有说明不会进行落盘）。
设置会话最大内存为100MB，临时表最大使用量为无限大，设置tidb-server整体内存为5GB，观察临时表占用内存是否会超过会话最大内存：

当oom-action=CANCEL时候，超过会话最大内存后会被kill掉。

mysql> select count(*) from lineitem;
+-----------+
| count(*)  |
+-----------+
| 179998372 |
+-----------+
1 row in set (0.58 sec)

mysql> desc lineitem;
+-----------------+---------------+------+------+---------+-------+
| Field           | Type          | Null | Key  | Default | Extra |
+-----------------+---------------+------+------+---------+-------+
| L_ORDERKEY      | bigint(20)    | NO   | PRI  | NULL    |       |
| L_PARTKEY       | bigint(20)    | NO   |      | NULL    |       |
| L_SUPPKEY       | bigint(20)    | NO   |      | NULL    |       |
| L_LINENUMBER    | bigint(20)    | NO   | PRI  | NULL    |       |
| L_QUANTITY      | decimal(15,2) | NO   |      | NULL    |       |
| L_EXTENDEDPRICE | decimal(15,2) | NO   |      | NULL    |       |
| L_DISCOUNT      | decimal(15,2) | NO   |      | NULL    |       |
| L_TAX           | decimal(15,2) | NO   |      | NULL    |       |
| L_RETURNFLAG    | char(1)       | NO   |      | NULL    |       |
| L_LINESTATUS    | char(1)       | NO   |      | NULL    |       |
| L_SHIPDATE      | date          | NO   |      | NULL    |       |
| L_COMMITDATE    | date          | NO   |      | NULL    |       |
| L_RECEIPTDATE   | date          | NO   |      | NULL    |       |
| L_SHIPINSTRUCT  | char(25)      | NO   |      | NULL    |       |
| L_SHIPMODE      | char(10)      | NO   |      | NULL    |       |
| L_COMMENT       | varchar(44)   | NO   |      | NULL    |       |
+-----------------+---------------+------+------+---------+-------+
16 rows in set (0.00 sec)

mysql> set tidb_tmp_table_max_size=67108864000;
Query OK, 0 rows affected (0.00 sec)

mysql> set tidb_mem_quota_query=100000000;
Query OK, 0 rows affected (0.00 sec)

mysql> set global tidb_server_memory_limit='5GB';
Query OK, 0 rows affected (0.01 sec)

mysql> create temporary table lineitem_temp like lineitem;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%oom%';
+--------------------------------+--------+
| Variable_name                  | Value  |
+--------------------------------+--------+
| tidb_enable_tmp_storage_on_oom | ON     |
| tidb_mem_oom_action            | CANCEL |
+--------------------------------+--------+
2 rows in set (0.00 sec)
--当执行超过session最大内存100MB时候会被kill掉。
mysql> insert into lineitem_temp select * from lineitem;
ERROR 1105 (HY000): Out Of Memory Quota![conn_id=4710292420229595553]

当oom-action=LOG时候，超过会话最大内存后不会被kill，内存一直上涨，但是超过tidb-server限制的tidb_server_memory_limit内存后仍然没有被kill掉！

mysql> show variables like '%oom%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| tidb_enable_tmp_storage_on_oom | ON    |
| tidb_mem_oom_action            | LOG   |
+--------------------------------+-------+
2 rows in set (0.01 sec)
mysql> show variables like 'tidb_server_memory_limit';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| tidb_server_memory_limit | 5GB   |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> show processlist;
+---------------------+------+----------------------+------+---------+------+------------+--------------------------------------------------+
| Id                  | User | Host                 | db   | Command | Time | State      | Info                                             |
+---------------------+------+----------------------+------+---------+------+------------+--------------------------------------------------+
| 4710292420229595553 | root | 192.168.31.200:37836 | tpch | Query   |  827 | autocommit | insert into lineitem_temp select * from lineitem |
| 4710292420229595549 | root | 192.168.31.200:53390 | NULL | Sleep   | 1422 | autocommit | NULL                                             |
| 4710292420229595551 | root | 192.168.31.200:53304 | NULL | Query   |    0 | autocommit | show processlist                                 |
+---------------------+------+----------------------+------+---------+------+------------+--------------------------------------------------+
3 rows in set (0.00 sec)
mysql> select * from information_schema.cluster_processlist where id=4710292420229595553\G
*************************** 1. row ***************************
      INSTANCE: 192.168.31.201:10080
            ID: 4710292420229595553
          USER: root
          HOST: 192.168.31.200:37836
            DB: tpch
       COMMAND: Query
          TIME: 824
         STATE: autocommit
          INFO: insert into lineitem_temp select * from lineitem
        DIGEST: 64ddb78ed27eecee91d16a859f865eadaa83cdb1e985a9cba40a2ae90bf99b1e
           MEM: 8162026651
          DISK: 0
      TxnStart: 03-12 14:04:49.940(440035204121231361)
RESOURCE_GROUP: 
1 row in set (0.01 sec)

因此在oom-action使用log的情况下当临时表调整过大后不受到tidb-server全局内存参数控制，可能会导致整个实例crash。

2、临时表不能搜集统计信息：

mysql> create temporary table lineitem_temp like lineitem;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into lineitem_temp select * from lineitem limit 10000;
Query OK, 10000 rows affected (0.13 sec)
Records: 10000  Duplicates: 0  Warnings: 0

mysql> explain select * from lineitem_temp;
+-------------------------+----------+-----------+---------------------+--------------------------------+
| id                      | estRows  | task      | access object       | operator info                  |
+-------------------------+----------+-----------+---------------------+--------------------------------+
| UnionScan_5             | 10000.00 | root      |                     |                                |
| └─TableReader_7         | 10000.00 | root      |                     | data:TableFullScan_6           |
|   └─TableFullScan_6     | 10000.00 | cop[tikv] | table:lineitem_temp | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> analyze table lineitem_temp;
Query OK, 0 rows affected, 2 warnings (0.17 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------+
| Level | Code | Message                                                                     |
+-------+------+-----------------------------------------------------------------------------+
| Error | 1146 | Table 'tpch.lineitem_temp' doesn't exist                                    |
| Note  | 1105 | Analyze use auto adjusted sample rate 1.000000 for table tpch.lineitem_temp |
+-------+------+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain select * from lineitem_temp;
+-------------------------+----------+-----------+---------------------+--------------------------------+
| id                      | estRows  | task      | access object       | operator info                  |
+-------------------------+----------+-----------+---------------------+--------------------------------+
| UnionScan_5             | 10000.00 | root      |                     |                                |
| └─TableReader_7         | 10000.00 | root      |                     | data:TableFullScan_6           |
|   └─TableFullScan_6     | 10000.00 | cop[tikv] | table:lineitem_temp | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------------+--------------------------------+
3 rows in set (0.00 sec)

3、临时表不能添加索引：

mysql> desc lineitem_temp;
+-----------------+---------------+------+------+---------+-------+
| Field           | Type          | Null | Key  | Default | Extra |
+-----------------+---------------+------+------+---------+-------+
| L_ORDERKEY      | bigint(20)    | NO   | PRI  | NULL    |       |
| L_PARTKEY       | bigint(20)    | NO   |      | NULL    |       |
| L_SUPPKEY       | bigint(20)    | NO   |      | NULL    |       |
| L_LINENUMBER    | bigint(20)    | NO   | PRI  | NULL    |       |
| L_QUANTITY      | decimal(15,2) | NO   |      | NULL    |       |
| L_EXTENDEDPRICE | decimal(15,2) | NO   |      | NULL    |       |
| L_DISCOUNT      | decimal(15,2) | NO   |      | NULL    |       |
| L_TAX           | decimal(15,2) | NO   |      | NULL    |       |
| L_RETURNFLAG    | char(1)       | NO   |      | NULL    |       |
| L_LINESTATUS    | char(1)       | NO   |      | NULL    |       |
| L_SHIPDATE      | date          | NO   |      | NULL    |       |
| L_COMMITDATE    | date          | NO   |      | NULL    |       |
| L_RECEIPTDATE   | date          | NO   |      | NULL    |       |
| L_SHIPINSTRUCT  | char(25)      | NO   |      | NULL    |       |
| L_SHIPMODE      | char(10)      | NO   |      | NULL    |       |
| L_COMMENT       | varchar(44)   | NO   |      | NULL    |       |
+-----------------+---------------+------+------+---------+-------+
16 rows in set (0.01 sec)

mysql> alter table lineitem_temp add index idx1(L_SUPPKEY);
ERROR 8200 (HY000): TiDB doesn't support ALTER TABLE for local temporary table
mysql>

4、不应该受到大事务影响（在6.1版本是有影响的，6.6测试应该是取消这个限制了）

--之前6.1版本的测试情况：
--设置内存临时表最大为10GB
set tidb_tmp_table_max_size=10737418240;
set tidb_mem_quota_query=10737418240;
insert into session_tmp select o_orderkey,nbr,ceil(nbr/10000) as batch from (select o_orderkey ,row_number()over() as nbr from (select O_ORDERKEY from orders order by O_ORDERKEY) a)b ;
但是这里的insert到临时表受限制于大事务，报错：ERROR 8004 (HY000): Transaction is too large, size: 104857632

在6.6版本中应该优化了这个问题，参考第1个问题的测试可以看到并没有受到txn-total-size-limit参数的影响。

mysql> show config where name like '%txn-total-size-limit%';
+------+---------------------+----------------------------------+-----------+
| Type | Instance            | Name                             | Value     |
+------+---------------------+----------------------------------+-----------+
| tidb | 192.168.31.201:4000 | performance.txn-total-size-limit | 104857600 |
+------+---------------------+----------------------------------+-----------+
1 row in set (0.00 sec)

需求，在后续的版本规划中是否应该考虑如下的功能增强：
1、临时表支持落盘能力（最重要），支持搜集统计信息，支持添加索引。
2、在tidb-server层存在很多落盘的场景，比如DDL的ingest过程、算子的落盘（sort、非并行hashagg、join用到的hashtable等都是用到chunk container的落盘部分能力，落盘性能存在问题帖子：Sort算子落盘性能太慢，希望产品层面进行优化）、CTE落盘、临时表的落盘需求等等，因此是否可以统一考虑一个“临时表空间”的缓存层，然后让这个临时表空间做统一落盘机制的处理，利用共享缓存减少内存的使用，增强落盘机制呢？

tidb菜鸟一只 · 2023 年3 月 13 日 01:13

临时表支持落盘能力的话，落盘之后它的读写速度和实体表不是没有差异了吗？哪还有必要使用临时表吗？直接用实体表不就行了吗？

人如其名 · 2023 年3 月 13 日 04:51

传统表要raft同步，要写wal，临时表都不需要，怎么可能一样快呢。不说分布式，就是在传统单机数据库里面不记录日志的临时表插入性能要比普通表好很多吧。

tidb菜鸟一只 · 2023 年3 月 13 日 06:46

可能我用的临时表场景比较小，我主要就是用临时表存储在内存中，处理效率比较高，并且有会话隔离的优势，减少会话间的冲突，如果落盘的话，反而会受到一定的负面影响。
另外tidb是支持GLOBAL TEMPORARY TABLE ON COMMIT DELETE ROWS，这种倒是可以加索引的，不过不知道适不适合你的应用场景

Defined2014 · 2023 年3 月 15 日 08:14

传统表要raft同步，要写wal，临时表都不需要，怎么可能一样快呢。不说分布式，就是在传统单机数据库里面不记录日志的临时表插入性能要比普通表好很多吧。

这样就不能叫临时表了吧，这个需求听起来更像是要一个只有一副本且写到本机的表

人如其名 · 2023 年3 月 15 日 09:50

这个应该就没有副本的概念了吧，只是表现在tidb层。就和现在形式差不多，只是具有落盘能力。全部放到内存中并不现实。