Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

tccfence log table delete should be optimized #4489

Closed
Bughue opened this issue Mar 23, 2022 · 6 comments
Closed

tccfence log table delete should be optimized #4489

Bughue opened this issue Mar 23, 2022 · 6 comments

Comments

@Bughue
Copy link
Contributor

Bughue commented Mar 23, 2022 •
edited
Loading

Why you need it?

Is your feature request related to a problem? Please describe in details
tcc_fence_log的定时任务对上一天的记录清除时没有limit，数据量大的时候一次过把所有数据删除可能会造成客户端性能急剧下降(explain出来type是all，全表扫描)

explain delete from tcc_fence_log where  gmt_modified<'2022-03-31 07:31:51' and status in (3,4)
Name         |Value                  |
-------------+-----------------------+
id           |1                      |
select_type  |DELETE                 |
table        |tcc_fence_log          |
partitions   |                       |
type         |ALL                    |
possible_keys|idx_gmt_modified,idx_st|
key          |                       |
key_len      |                       |
ref          |                       |
rows         |64211                  |
filtered     |100.0                  |
Extra        |Using where            |

How it could be?

A clear and concise description of what you want to happen. You can explain more about input of the feature, and output of it.
认为这个表的删除应该有limit 分批删除

Other related information

Add any other context or screenshots about the feature request here.
@Bughue Bughue mentioned this issue Mar 23, 2022
Merged
1 task
@caohdgege
Copy link
Contributor

  1. limit 是其中一个解决方案
  2. 当前这些一天一次的定时任务(假如配置的是一天一次)，都是在服务启动后的24小时整之后开始执行，而这个时候是有可能撞上业务高峰期的，把这个配置改成cron的表达式，然后默认每天3点(或者4点)之后执行，避开业务高峰期是否可行？

@Bughue
Copy link
Contributor Author

Bughue commented Mar 28, 2022

  1. limit 是其中一个解决方案
  2. 当前这些一天一次的定时任务(假如配置的是一天一次)，都是在服务启动后的24小时整之后开始执行，而这个时候是有可能撞上业务高峰期的，把这个配置改成cron的表达式，然后默认每天3点(或者4点)之后执行，避开业务高峰期是否可行？

是的这个我也有想，我是觉得两件事可能都需要做，其中delete的优化比较直接。而定时周期这个可能更加见仁见智，后续可以讨论一下

@Bughue
Copy link
Contributor Author

Bughue commented Mar 29, 2022 •
edited
Loading

压测日志部分展示，
1.在delete之前先插入17w行数据（sql），
2.开启20个线程往tccfencelog里面插数据（直接调用TCCFenceHandler.prepareFence和TCCFenceHandler.rollbackFence）
3.同时开启一条线程去删除日志（TCCFenceHandler.deleteFenceByDate）
几次试验得到的效果都是：在删除10+w行记录会使该表的操作停顿1s以上，数据更多会停顿更久

2022-03-29 15:20:19.080  INFO 70499 --- [pool-8-thread-7] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000021, branchId: 10000021
2022-03-29 15:20:19.131  INFO 70499 --- [pool-8-thread-2] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000027, branchId: 10000027
2022-03-29 15:20:19.132  INFO 70499 --- [pool-8-thread-4] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000028, branchId: 10000028
2022-03-29 15:20:19.137  INFO 70499 --- [ool-8-thread-14] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000036, branchId: 10000036
2022-03-29 15:20:19.137  INFO 70499 --- [pool-8-thread-6] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000030, branchId: 10000030
2022-03-29 15:20:19.137  INFO 70499 --- [pool-8-thread-8] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000029, branchId: 10000029
2022-03-29 15:20:19.248  INFO 70499 --- [ool-8-thread-12] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000032, branchId: 10000032
2022-03-29 15:20:19.313  INFO 70499 --- [ool-8-thread-13] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000033, branchId: 10000033
2022-03-29 15:20:19.315  INFO 70499 --- [ool-8-thread-11] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000035, branchId: 10000035
2022-03-29 15:20:19.316  INFO 70499 --- [ool-8-thread-16] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000034, branchId: 10000034
2022-03-29 15:20:19.370  INFO 70499 --- [ool-8-thread-15] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000031, branchId: 10000031
2022-03-29 15:20:19.372  INFO 70499 --- [ool-8-thread-19] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000037, branchId: 10000037
2022-03-29 15:20:19.373  INFO 70499 --- [ool-8-thread-17] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000039, branchId: 10000039
2022-03-29 15:20:19.373  INFO 70499 --- [ool-8-thread-18] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000038, branchId: 10000038
2022-03-29 15:20:19.548  INFO 70499 --- [ool-8-thread-20] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000040, branchId: 10000040
2022-03-29 15:20:19.612  INFO 70499 --- [pool-8-thread-9] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000041, branchId: 10000041
2022-03-29 15:20:19.672  INFO 70499 --- [pool-8-thread-3] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000042, branchId: 10000042
2022-03-29 15:20:19.850  INFO 70499 --- [pool-8-thread-1] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000043, branchId: 10000043
2022-03-29 15:20:19.850  INFO 70499 --- [pool-8-thread-5] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000044, branchId: 10000044
2022-03-29 15:20:19.895  INFO 70499 --- [ool-8-thread-10] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000045, branchId: 10000045
2022-03-29 15:20:19.905  INFO 70499 --- [pool-8-thread-7] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000046, branchId: 10000046
2022-03-29 15:20:19.909  INFO 70499 --- [pool-8-thread-2] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000047, branchId: 10000047
2022-03-29 15:20:19.969  INFO 70499 --- [pool-8-thread-8] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000048, branchId: 10000048
2022-03-29 15:20:20.071  INFO 70499 --- [pool-8-thread-4] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000049, branchId: 10000049
2022-03-29 15:20:20.077  INFO 70499 --- [ool-8-thread-14] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000050, branchId: 10000050

2022-03-29 15:20:20.086  INFO 70499 --- [pool-8-thread-6] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000051, branchId: 10000051
2022-03-29 15:20:20.128  INFO 70499 --- [ool-8-thread-12] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000052, branchId: 10000052
2022-03-29 15:20:20.141  INFO 70499 --- [ool-8-thread-13] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000053, branchId: 10000053
2022-03-29 15:20:20.142  INFO 70499 --- [ool-8-thread-11] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000054, branchId: 10000054
2022-03-29 15:20:20.145  INFO 70499 --- [ool-8-thread-15] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000055, branchId: 10000055
2022-03-29 15:20:20.148  INFO 70499 --- [ool-8-thread-16] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000056, branchId: 10000056
2022-03-29 15:20:20.321  INFO 70499 --- [ool-8-thread-19] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000057, branchId: 10000057
2022-03-29 15:20:20.362  INFO 70499 --- [ool-8-thread-18] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000058, branchId: 10000058
2022-03-29 15:20:20.363  INFO 70499 --- [ool-8-thread-17] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000059, branchId: 10000059
2022-03-29 15:20:20.369  INFO 70499 --- [ool-8-thread-20] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000060, branchId: 10000060
2022-03-29 15:20:20.437  INFO 70499 --- [pool-8-thread-9] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000061, branchId: 10000061
2022-03-29 15:20:20.599  INFO 70499 --- [pool-8-thread-5] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000063, branchId: 10000063
2022-03-29 15:20:20.600  INFO 70499 --- [pool-8-thread-3] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000062, branchId: 10000062
2022-03-29 15:20:20.601  INFO 70499 --- [pool-8-thread-1] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000064, branchId: 10000064
2022-03-29 15:20:20.613  INFO 70499 --- [ool-8-thread-10] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000065, branchId: 10000065
2022-03-29 15:20:20.670  INFO 70499 --- [pool-8-thread-7] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000066, branchId: 10000066
2022-03-29 15:20:20.717  INFO 70499 --- [pool-8-thread-2] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000067, branchId: 10000067
2022-03-29 15:20:20.778  INFO 70499 --- [pool-8-thread-4] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000068, branchId: 10000068
2022-03-29 15:20:20.779  INFO 70499 --- [pool-8-thread-6] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000070, branchId: 10000070
2022-03-29 15:20:20.837  INFO 70499 --- [ool-8-thread-14] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000071, branchId: 10000071
2022-03-29 15:20:20.840  INFO 70499 --- [pool-8-thread-8] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000069, branchId: 10000069
2022-03-29 15:20:20.843  INFO 70499 --- [ool-8-thread-12] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000072, branchId: 10000072
2022-03-29 15:20:20.845  INFO 70499 --- [ool-8-thread-13] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000073, branchId: 10000073
2022-03-29 15:20:20.885  INFO 70499 --- [ool-8-thread-11] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000074, branchId: 10000074
2022-03-29 15:20:20.954  INFO 70499 --- [ool-8-thread-19] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000077, branchId: 10000077
2022-03-29 15:20:20.963  INFO 70499 --- [ool-8-thread-16] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000076, branchId: 10000076
2022-03-29 15:20:21.073  INFO 70499 --- [ool-8-thread-17] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000078, branchId: 10000078
2022-03-29 15:20:21.077  INFO 70499 --- [ool-8-thread-18] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000079, branchId: 10000079
2022-03-29 15:20:21.183  INFO 70499 --- [ool-8-thread-20] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000080, branchId: 10000080
2022-03-29 15:20:21.184  INFO 70499 --- [ool-8-thread-15] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000075, branchId: 10000075
2022-03-29 15:20:21.252  INFO 70499 --- [ool-8-thread-10] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000084, branchId: 10000084
2022-03-29 15:20:21.252  INFO 70499 --- [pool-8-thread-9] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000081, branchId: 10000081
2022-03-29 15:20:21.307  INFO 70499 --- [pool-8-thread-4] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000087, branchId: 10000087
2022-03-29 15:20:21.311  INFO 70499 --- [pool-8-thread-5] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000082, branchId: 10000082
2022-03-29 15:20:21.312  INFO 70499 --- [pool-8-thread-3] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000085, branchId: 10000085
2022-03-29 15:20:21.312  INFO 70499 --- [pool-8-thread-1] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000083, branchId: 10000083
2022-03-29 15:20:21.375  INFO 70499 --- [pool-8-thread-2] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000086, branchId: 10000086
2022-03-29 15:20:21.418  INFO 70499 --- [pool-8-thread-7] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000088, branchId: 10000088
2022-03-29 15:20:21.495  INFO 70499 --- [pool-8-thread-6] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000089, branchId: 10000089
2022-03-29 15:20:21.498  INFO 70499 --- [ool-8-thread-13] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000092, branchId: 10000092
2022-03-29 15:20:21.550  INFO 70499 --- [ool-8-thread-14] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000090, branchId: 10000090
2022-03-29 15:20:21.550  INFO 70499 --- [pool-8-thread-8] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000091, branchId: 10000091
2022-03-29 15:20:21.553  INFO 70499 --- [ool-8-thread-12] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000093, branchId: 10000093
2022-03-29 15:20:21.610  INFO 70499 --- [ool-8-thread-11] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000094, branchId: 10000094
2022-03-29 15:20:21.725  INFO 70499 --- [ool-8-thread-16] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000095, branchId: 10000095
2022-03-29 15:20:21.786  INFO 70499 --- [ool-8-thread-17] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000097, branchId: 10000097
2022-03-29 15:20:21.786  INFO 70499 --- [ool-8-thread-19] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000096, branchId: 10000096
2022-03-29 15:20:21.843  INFO 70499 --- [ool-8-thread-18] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000098, branchId: 10000098
（delete开始执行，在此之前每秒约25次插入）
2022-03-29 15:20:21.845  INFO 70499 --- [      Thread-88] c.s.b.b.e.m.seata.tcc.TccFenceStress     : ----------------------------------delete begin----------------------------------
2022-03-29 15:20:21.858  INFO 70499 --- [ool-8-thread-15] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000099, branchId: 10000099
2022-03-29 15:20:21.913  INFO 70499 --- [ool-8-thread-20] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000100, branchId: 10000100
2022-03-29 15:20:21.946  INFO 70499 --- [pool-8-thread-9] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000101, branchId: 10000101
2022-03-29 15:20:21.957  INFO 70499 --- [ool-8-thread-10] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000102, branchId: 10000102
2022-03-29 15:20:21.999  INFO 70499 --- [pool-8-thread-4] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000103, branchId: 10000103
2022-03-29 15:20:22.022  INFO 70499 --- [pool-8-thread-3] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000105, branchId: 10000105
2022-03-29 15:20:22.022  INFO 70499 --- [pool-8-thread-5] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000104, branchId: 10000104
2022-03-29 15:20:22.126  INFO 70499 --- [pool-8-thread-1] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000106, branchId: 10000106
2022-03-29 15:20:22.126  INFO 70499 --- [pool-8-thread-7] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000108, branchId: 10000108
2022-03-29 15:20:22.126  INFO 70499 --- [pool-8-thread-2] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000107, branchId: 10000107
2022-03-29 15:20:22.187  INFO 70499 --- [pool-8-thread-6] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000109, branchId: 10000109
2022-03-29 15:20:22.189  INFO 70499 --- [ool-8-thread-13] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000110, branchId: 10000110
2022-03-29 15:20:22.248  INFO 70499 --- [pool-8-thread-8] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000112, branchId: 10000112
2022-03-29 15:20:22.248  INFO 70499 --- [ool-8-thread-14] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000111, branchId: 10000111
2022-03-29 15:20:22.309  INFO 70499 --- [ool-8-thread-12] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000113, branchId: 10000113
(停顿1s以上，这期间没有数据能写成功)
2022-03-29 15:20:23.651  INFO 70499 --- [ool-8-thread-16] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000115, branchId: 10000115
2022-03-29 15:20:23.651  INFO 70499 --- [ool-8-thread-19] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000117, branchId: 10000117
2022-03-29 15:20:23.651  INFO 70499 --- [ool-8-thread-17] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000118, branchId: 10000118
2022-03-29 15:20:23.651  INFO 70499 --- [ool-8-thread-11] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000114, branchId: 10000114
2022-03-29 15:20:23.651  INFO 70499 --- [ool-8-thread-18] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000116, branchId: 10000116
2022-03-29 15:20:23.709  INFO 70499 --- [      Thread-88] c.s.b.b.e.m.seata.tcc.TccFenceStress     : clean tcc fence before:Tue Mar 29 15:20:21 CST 2022,count=176002
2022-03-29 15:20:23.709  INFO 70499 --- [      Thread-88] c.s.b.b.e.m.seata.tcc.TccFenceStress     : ----------------------------------delete end----------------------------------
（delete结束，后面继续正常执行）
2022-03-29 15:20:23.943  INFO 70499 --- [pool-8-thread-7] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000121, branchId: 10000121
2022-03-29 15:20:23.943  INFO 70499 --- [pool-8-thread-4] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000123, branchId: 10000123
2022-03-29 15:20:23.944  INFO 70499 --- [pool-8-thread-3] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000122, branchId: 10000122
2022-03-29 15:20:23.948  INFO 70499 --- [ool-8-thread-10] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000120, branchId: 10000120
2022-03-29 15:20:23.948  INFO 70499 --- [pool-8-thread-1] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000125, branchId: 10000125
2022-03-29 15:20:23.948  INFO 70499 --- [pool-8-thread-9] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000119, branchId: 10000119
2022-03-29 15:20:23.948  INFO 70499 --- [pool-8-thread-5] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000124, branchId: 10000124
2022-03-29 15:20:23.950  INFO 70499 --- [ool-8-thread-13] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000128, branchId: 10000128
2022-03-29 15:20:23.950  INFO 70499 --- [pool-8-thread-8] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000127, branchId: 10000127
2022-03-29 15:20:23.953  INFO 70499 --- [pool-8-thread-2] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000126, branchId: 10000126
2022-03-29 15:20:23.953  INFO 70499 --- [pool-8-thread-6] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000131, branchId: 10000131
2022-03-29 15:20:23.956  INFO 70499 --- [ool-8-thread-15] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000129, branchId: 10000129
2022-03-29 15:20:24.076  INFO 70499 --- [ool-8-thread-20] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000130, branchId: 10000130
2022-03-29 15:20:24.114  INFO 70499 --- [ool-8-thread-12] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000132, branchId: 10000132
2022-03-29 15:20:24.181  INFO 70499 --- [ool-8-thread-17] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000135, branchId: 10000135
2022-03-29 15:20:24.184  INFO 70499 --- [ool-8-thread-11] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000134, branchId: 10000134
2022-03-29 15:20:24.185  INFO 70499 --- [ool-8-thread-14] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000133, branchId: 10000133
2022-03-29 15:20:24.188  INFO 70499 --- [ool-8-thread-19] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000137, branchId: 10000137
2022-03-29 15:20:24.210  INFO 70499 --- [ool-8-thread-16] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000136, branchId: 10000136
2022-03-29 15:20:24.247  INFO 70499 --- [ool-8-thread-18] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000138, branchId: 10000138
2022-03-29 15:20:24.467  INFO 70499 --- [pool-8-thread-7] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000139, branchId: 10000139
2022-03-29 15:20:24.469  INFO 70499 --- [ool-8-thread-10] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000140, branchId: 10000140
2022-03-29 15:20:24.472  INFO 70499 --- [pool-8-thread-1] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000141, branchId: 10000141
2022-03-29 15:20:24.473  INFO 70499 --- [pool-8-thread-8] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000142, branchId: 10000142
2022-03-29 15:20:24.477  INFO 70499 --- [pool-8-thread-5] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000143, branchId: 10000143
2022-03-29 15:20:24.477  INFO 70499 --- [pool-8-thread-3] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000144, branchId: 10000144
2022-03-29 15:20:24.479  INFO 70499 --- [pool-8-thread-4] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000145, branchId: 10000145
2022-03-29 15:20:24.481  INFO 70499 --- [pool-8-thread-9] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000146, branchId: 10000146
2022-03-29 15:20:24.502  INFO 70499 --- [ool-8-thread-13] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000147, branchId: 10000147
2022-03-29 15:20:24.508  INFO 70499 --- [pool-8-thread-6] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000148, branchId: 10000148
2022-03-29 15:20:24.508  INFO 70499 --- [ool-8-thread-15] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000150, branchId: 10000150
2022-03-29 15:20:24.508  INFO 70499 --- [pool-8-thread-2] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000149, branchId: 10000149
2022-03-29 15:20:24.622  INFO 70499 --- [ool-8-thread-20] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000151, branchId: 10000151
2022-03-29 15:20:24.639  INFO 70499 --- [ool-8-thread-12] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000152, branchId: 10000152
2022-03-29 15:20:24.716  INFO 70499 --- [ool-8-thread-14] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000154, branchId: 10000154
2022-03-29 15:20:24.716  INFO 70499 --- [ool-8-thread-17] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000153, branchId: 10000153
2022-03-29 15:20:24.716  INFO 70499 --- [ool-8-thread-11] io.seata.rm.tcc.TCCFenceHandler          : TCC fence prepare result: true. xid: 10000155, branchId: 10000155
（后面不再记录，插入情况和delete开始之前基本一样）

@Bughue Bughue changed the title tccfence log table should be deleted in batches tccfence log table delete should be optimized Mar 29, 2022
@Bughue
Copy link
Contributor Author

Bughue commented Mar 31, 2022

  1. limit 是其中一个解决方案
  2. 当前这些一天一次的定时任务(假如配置的是一天一次)，都是在服务启动后的24小时整之后开始执行，而这个时候是有可能撞上业务高峰期的，把这个配置改成cron的表达式，然后默认每天3点(或者4点)之后执行，避开业务高峰期是否可行？

发现explain 原delete语句的时候，type=all，虽然有索引，但也是全表扫描（status索引是有可能走的，最好保留）。综合之前聊的，sql方面优化有3个思路

  1. limit 10000/20000 ，分批删除（我觉得始终要limit的，不然百分百锁表，我今天试了很久加了limit之后没有锁过，而且多个节点可能一起去delete，造成更慢的情况）
  2. 建表的时候使用自增主键，删除的时候查询出maxid再删除（但自增主键的方式看起来不是很推荐？）
  3. 将status索引改为倒序（mysql8以下这个不起作用，而且只能让status=1的插入成功，还是会阻塞update操作）

@pengten
Copy link
Contributor

pengten commented Apr 15, 2022

自增主键应该是不可取的，cron表达式是个不错的选择，在某一时刻走索引删除。
另外还需要考虑SQL在不同数据库中索引选择策略的差异（比如根据时间分段删除，时间是递增的，建立索引正合适）。

@renliangyu857
Copy link
Contributor

这个定期删除，时间长了会造成表空间碎片问题吧，是否要考虑按日期分区分表滚动删除或者表空间整理

@Bughue Bughue closed this as completed Dec 29, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@Bughue @caohdgege @renliangyu857 @pengten