Bug #96875 add a option to disable InnoDB undo log purge

• Submitted: 14 Sep 2019 10:59
• Modified: 21 Oct 2019 12:11
• Reporter: Zongzhi Chen (OCA)
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[14 Sep 2019 10:59] Zongzhi Chen

Description:
sometime when running the sysbench, the undo log purge activity may affect the performance if the previous sysbench type contain some update operation
I suggest add option to avoid InnoDB undo purge

we can prevent the undo purge by flush table tt for export now, we need a simply way.

Another benefit by avoid undo purge, we can get better performance in loading large data workload, we can disable the undo purge, then restart after finish. It looks like disable background data compact in LSM tree such as (leveldb, rocksdb).

How to repeat:
read the code

[8 Oct 2019 14:06] MySQL Verification Team

Hi,

Thank you for your feature request.

I do not think that what you are asking for is feasible, simply because undo log could overflow if not purged.

[16 Oct 2019 12:24] Zongzhi Chen

Yep, If don't purge, the undo log could overflow.. And this configure should not use in online environment.

However, when running sysbench, the background undo-purge may affect the performance.In order to avoid the affect, add a option is good solution..

[16 Oct 2019 12:42] MySQL Verification Team

Hi Mr. Zongzhi,

I am afraid that disabling undo log purge might have devastating effects on many installation.

That is why I consider that this feature request would be denied.

[18 Oct 2019 20:33] Zongzhi Chen

However, when running some benchmark, people really need it.
This why I said that add an option to control it, and the default behavior is open the undo purge.

[21 Oct 2019 12:11] MySQL Verification Team

Hi Mr. zongzhi,

We had an internal discussion with InnoDB team on various matters, including this one.

We all agreed that this feature would be a VERY dangerous one, if implemented.

On the other hand, anybody who wants to reduce the level of load from the purge, can right now use the max purge lag parameter to practically disable purge.