MySQL Bugs: #83307: Wakeup purge thread while enabling innodb_undo_log

Bug #83307	Wakeup purge thread while enabling innodb_undo_log_truncate
Submitted:	8 Oct 2016 9:17	Modified:	21 Dec 2016 6:24
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
Because the online undo truncating requests a full checkpoint (actually twice, one before truncating, another full checkpoint after that), and it may affect the stability of our online business,  we tend to turn on innodb_undo_log_truncate when it's necessary (for example, at midnight when there's very little workload)  

But turning on innodb_undo_log_truncate doesn't wakeup the purge thread so we have to wait for a while until the purge operation is finished. 

How to repeat:
1. Open a transaction with RR isolation, select something to make sure that the read view is opened.

2. do a lot of dml...under very heavy workload which aims to increase the size of undo tablespaces. 

3. commit the transaction, and wait until the length of history list doesn't change , or make a slow shutdown instead.

4. Restart the server, turn down innodb_max_undo_log_size(smaller than the file size of undo tablespace), turn on innodb_undo_log_truncate. (Make sure there isn't any dml workload)

You'll find that the trx purge thread won't be waked up and truncate undo tablespace even there isn't any activity.

Suggested fix:
Wakeup the purge thread while turning on innodb_undo_log_truncate or turning down the value of innodb_max_undo_log_size

Wake up the purge thread if the setting changes (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: wake_purge.diff (application/octet-stream, text), 4.45 KiB.

based on latest 5.7.16

Hello Zhai,

Thank you for the report and contribution.

Thanks,
Umesh