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:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[8 Oct 2016 9:17] zhai weixiang
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
[14 Oct 2016 9:33] zhai weixiang
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.

[14 Oct 2016 9:34] zhai weixiang
based on latest 5.7.16
[21 Dec 2016 6:24] MySQL Verification Team
Hello Zhai,

Thank you for the report and contribution.