Transaction is too large when use TiDB as storage

[wutingjia]

Please answer these questions before submitting your issue.

• Why do you submit this issue?
• Question or discussion
• Bug
• Requirement
• Feature or performance improvement

---

Question

I use the TIDB as the storage.When beginning remove the expire metrics from the storage,transaction is too large exception is thrown. I have adjusted the limit to 1GB, but it seems still not enough.
Should I let go of restrictions or some recommend value?

---

Bug

• Which version of SkyWalking, OS and JRE?
  SkyWalking 8.1.0

[xbkaishui]

@wutingjia which version of tidb do you use?

[wu-sheng]

Do you mean the delete statement over the limitation? which configs do you set?

[xbkaishui]

For tidb version 3.x, the transaction has size limit, please refer this

[wutingjia]

Do you mean the delete statement over the limitation? which configs do you set?
For tidb version 3.x, the transaction has size limit, please refer this

TiDB 中，单个事务的总大小默认不超过 100 MB，这个默认值可以通过配置文件中的配置项 txn-total-size-limit 进行修改，最大支持 10 GB。实际的单个事务大小限制还取决于服务器剩余可用内存的大小，执行事务时 TiDB 进程的内存消耗大约是事务大小的 6 倍以上。

yes, I use the tidb 4.0.4.I have changed the txn-total-size-limit to 1GB, but still not enough in testing Environment.
I worry about even if I set the txn-total-size-limit to 10GB in production environment is still not enough .Because my service amount is 10 times over testing Environment. In this case ,every expire execution will cost several tens of GB memory ?
，

[xbkaishui]

This is kind of a bug. we should use tidb_batch_delete=1 and also enable-batch-dml = true, refer

[xbkaishui]

Tidb is more like a OLTP than OLAP, why you use it in prod env as skywalking backend. recommend ES.

[moonsphere]

@xbkaishui hi, what's real SQL make a transaction such large? does this SQL?

skywalking/oap-server/server-storage-plugin/storage-jdbc-hikaricp-plugin/src/main/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/h2/dao/H2HistoryDeleteDAO.java

Line 31 in c95a913

public class H2HistoryDeleteDAO implements IHistoryDeleteDAO {

[xxxlxy2008]

delete in batch，just like：delete from xxx where create_time>=? and create_time <=? limit 1000，per transaction。

[xbkaishui]

@xbkaishui hi, what's real SQL make a transaction such large? does this SQL?

skywalking/oap-server/server-storage-plugin/storage-jdbc-hikaricp-plugin/src/main/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/h2/dao/H2HistoryDeleteDAO.java

Line 31 in c95a913

public class H2HistoryDeleteDAO implements IHistoryDeleteDAO {

Yes it is, seems the history data(metric, record) is too large

[xbkaishui]

delete in batch，just like：delete from xxx where create_time>=? and create_time <=? limit 1000，per transaction。

No, delete in batch is the internal implement in Tidb, it will split one big transaction to some small transactions

[wutingjia]

This is kind of a bug. we should use tidb_batch_delete=1 and also enable-batch-dml = true, refer

sorry，i can not find the description about this bug or these config in this refer，could you post it on here?

Tidb is more like a OLTP than OLAP, why you use it in prod env as skywalking backend. recommend ES.

Yeah， I accutally use es first，but because of no person is very familiar with es and hard to operation in my company.
With using es，i must change some config such as default max shard number，write thread_pool，bulk size，build new monitor culster and so no.Or it will throw several kinds of exeception and not work for running a period of time.Unfortunately，I can not find related configuration recommendation，so even if I have solve some exception，es still not work for running a period of time。Instead tidb can nearly use out of box.

No, delete in batch is the internal implement in Tidb, it will split one big transaction to some small transactions

If it is why the delete transaction is larger than 1GB in a low load env?

[xbkaishui]

About tidb batch delete issue. you can search by google.
for the es usage issue. @kezhenxu94 any suggestion? thanks

[xbkaishui]

This is kind of a bug. we should use tidb_batch_delete=1 and also enable-batch-dml = true, refer

sorry，i can not find the description about this bug or these config in this refer，could you post it on here?

Tidb is more like a OLTP than OLAP, why you use it in prod env as skywalking backend. recommend ES.

Yeah， I accutally use es first，but because of no person is very familiar with es and hard to operation in my company.
With using es，i must change some config such as default max shard number，write thread_pool，bulk size，build new monitor culster and so no.Or it will throw several kinds of exeception and not work for running a period of time.Unfortunately，I can not find related configuration recommendation，so even if I have solve some exception，es still not work for running a period of time。Instead tidb can nearly use out of box.

No, delete in batch is the internal implement in Tidb, it will split one big transaction to some small transactions

If it is why the delete transaction is larger than 1GB in a low load env?

check this link https://book.tidb.io/session4/chapter6/transaction-statement-count-limit.html