Enhancement of Temporary Table Capabilities and Establishment of Caching Mechanism at the TiDB-Server Layer

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

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

| username: 人如其名

Requirement Feedback
Please clearly and accurately describe the problem scenario, desired behavior, and background information to facilitate timely follow-up by the product team.
[Problem Scenario Involved in the Requirement] Processing intermediate result sets during batch runs requires the use of temporary tables.

[Expected Behavior] Temporary tables should support statistics collection, index creation, disk storage, and should not be constrained by large transactions.

[Alternative Solution] Currently, ordinary tables are used, but the import performance of ordinary tables is too slow, making them unsuitable for temporary storage of intermediate data.

Support for temporary tables: 临时表 | PingCAP 文档中心
There are some limitations, but there has been no optimization progress in these versions. Shouldn’t we further optimize for more important scenarios?
One of the most common scenarios for using temporary tables is to generate a large number of intermediate result sets and store them in temporary tables, which are then joined with other tables for data processing. Therefore, some commonly used technical points are: 1. Support for large capacity; 2. Easy statistics collection and joining with other ordinary tables; 3. Support for index creation for data filtering and certain scenarios using indexlookupjoin; 4. Temporary tables do not require logging, so they should not be constrained by large transactions. Currently, TiDB needs to strengthen optimization on these points to facilitate practical use in real batch scenarios. The following tests were conducted for these issues:

  1. Currently does not support large capacity: Large capacity means that the temporary table may exceed tens of GBs and should not all be in memory but should be stored on disk (the official documentation states that it will not be stored on disk).
    Set the session’s maximum memory to 100MB, the temporary table’s maximum usage to unlimited, and the overall memory of the tidb-server to 5GB. Observe whether the temporary table’s memory usage exceeds the session’s maximum memory:
  • When oom-action=CANCEL, it will be killed after exceeding the session’s maximum memory.
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)
--When the session's maximum memory exceeds 100MB, it will be killed.
mysql> insert into lineitem_temp select * from lineitem;
ERROR 1105 (HY000): Out Of Memory Quota![conn_id=4710292420229595553]
  • When oom-action=LOG, it will not be killed after exceeding the session’s maximum memory, and the memory will keep increasing. However, it will still not be killed after exceeding the tidb-server’s memory limit of tidb_server_memory_limit!
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)

Therefore, when oom-action is set to log, if the temporary table is too large, it will not be controlled by the global memory parameter of tidb-server, which may cause the entire instance to crash.

  1. Temporary tables cannot collect statistics:
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)
  1. Temporary tables cannot add indexes:
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> 
  1. Should not be affected by large transactions (in version 6.1 it was affected, but in version 6.6 this limitation seems to have been removed)
--Previous test situation in version 6.1:
--Set the maximum memory for temporary tables to 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 ;
However, the insert into the temporary table is limited by large transactions, resulting in the error: ERROR 8004 (HY000): Transaction is too large, size: 104857632

In version 6.6, this issue seems to have been optimized. The test for the first issue shows that it is not affected by the txn-total-size-limit parameter.

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)

Requirement: In future version planning, should we consider the following feature enhancements:

  1. Temporary tables should support disk storage (most important), statistics collection, and index creation.
  2. There are many disk storage scenarios at the tidb-server layer, such as the DDL ingest process, operator disk storage (sort, non-parallel hashagg, join using hashtable, etc., all use the chunk container’s disk storage capability, with performance issues as discussed in this post: Sort算子落盘性能太慢,希望产品层面进行优化 - TiDB 的问答社区), CTE disk storage, and temporary table disk storage requirements. Therefore, should we consider a unified “temporary table space” cache layer to handle unified disk storage mechanisms, utilizing shared cache to reduce memory usage and enhance disk storage mechanisms?
| username: tidb菜鸟一只 | Original post link

If temporary tables support the ability to write to disk, wouldn’t their read and write speeds be the same as physical tables after writing to disk? Is there still a need to use temporary tables? Wouldn’t it be better to just use physical tables directly?

| username: 人如其名 | Original post link

Traditional tables require Raft synchronization and writing to WAL, which temporary tables do not need. How could they possibly be as fast? Even in traditional single-machine databases, the insertion performance of temporary tables that do not log is much better than that of regular tables, right?

| username: tidb菜鸟一只 | Original post link

It might be that the scenarios in which I use temporary tables are relatively small. I mainly use temporary tables to store data in memory, which results in higher processing efficiency and the advantage of session isolation, reducing conflicts between sessions. If the data is written to disk, it might have some negative impact instead.

Additionally, TiDB supports GLOBAL TEMPORARY TABLE ON COMMIT DELETE ROWS, which can have indexes added. However, I’m not sure if this suits your application scenario.

| username: Defined2014 | Original post link

Then it can’t be called a temporary table, right? This requirement sounds more like needing a table with only one replica that writes to the local machine.

| username: 人如其名 | Original post link

There shouldn’t be the concept of replicas here, right? It’s just reflected at the TiDB layer. It’s similar to the current form, but with the ability to persist to disk. Storing everything in memory is not realistic.