memory management issue for large transaction

[mzhang77]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

tiup bench tpch --sf=1 prepare --db test
then use dumpling to export lineitem table in sql format. So we generates around 6M insert statements

login to tidb:
create database test1;
create table lineitem like test.lineitem;
set autocommit=off;
source lineitem.sql

2. What did you expect to see? (Required)

These are just 6M simple insert statements, it should be completed without issue.

3. What did you see instead (Required)

ERROR 1105 (HY000): Out Of Memory Quota![conn_id=7590317993653961157]

This issue is not about tidb_mem_quota_query. The memory required to run simple insert statements should not accumulate over the number of rows. This same insert script can run on mysql db without any issue.

4. What is your TiDB version? (Required)

Release Version: v6.5.3
Edition: Community
Git Commit Hash: 71e6696
Git Branch: heads/refs/tags/v6.5.3
UTC Build Time: 2023-06-12 13:52:32
GoVersion: go1.19.9
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[windtalker]

Hi @mzhang77 , since autocommit is off, does it mean all the 6M insert data is comitted at the end of lineitem.sql?

[mzhang77]

@windtalker yes, I did this on purpose. I believe memory consumption should not be accumulated over rows within a transaction. I did a same test on mysql with autocommit=off too. Even a very small mysql test database can complete 6M inserts, or more, without any issue.

[windtalker]

@windtalker yes, I did this on purpose. I believe memory consumption should not be accumulated over rows within a transaction. I did a same test on mysql with autocommit=off too. Even a very small mysql test database can complete 6M inserts, or more, without any issue.

Do you mean even if the whole transaction is big(like 1G), but since it contains 6M insert statements, the memory usage in TiDB should not be 1G, instead, it should be around the size of each insert statement?

[mzhang77]

@windtalker Yes, there is memory associated with transaction, and memory associated with statement, these are different objects and tidb memory management should have finer granularity. Once a SQL statement is completed, all the data related with this SQL is securely written to database. There is no need to keep the statement memory until commit. And this appears to be what mysql do so it can easily support large transactions.

[windtalker]

@windtalker Yes, there is memory associated with transaction, and memory associated with statement, these are different objects and tidb memory management should have finer granularity. Once a SQL statement is completed, all the data related with this SQL is securely written to database. There is no need to keep the statement memory until commit. And this appears to be what mysql do so it can easily support large transactions.

Got it. AFAIK, this is a by designed behavior in TiDB. And in order to improve it, pipelined dml is proposed(#50215), and I think it is still WIP