| Bug #72269 | Setting innodb_max_purge_lag without innodb_max_purge_lag_delay disables delay | ||
|---|---|---|---|
| Submitted: | 8 Apr 2014 4:59 | Modified: | 11 Apr 2014 20:22 |
| Reporter: | Eric Bergen (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.6, 5.7.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[8 Apr 2014 5:01]
Eric Bergen
The host running my test rebooted so I haven't tested the fix. :(
[8 Apr 2014 19:17]
Sveta Smirnova
Thank you for the report. > Then set innodb_max_purge_lag_delay > 0 and watch the delay go down while dml statements run slower. This is documented behavior, see at http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_max_purge_lag_...: "Any non-zero value represents an upper limit on the delay period computed from the formula based on the value of innodb_max_purge_lag. The default of zero means that there is no upper limit imposed on the delay interval. " If you want no upper limit you don't need to set innodb_max_purge_lag_delay > 0
[8 Apr 2014 20:00]
Eric Bergen
You're missing the point of the bug. The manual says, "The default of zero means that there is no upper limit imposed on the delay interval." This bug exists because instead of allowing an unbounded upper limit on the delay interval mysql actually sets the delay to 0.
[8 Apr 2014 21:11]
Eric Bergen
To put this another way if you have a config made in 5.5 with innodb_max_purge_lag set but no innodb_max_purge_lag_delay because it was added in 5.6 then the delay is disabled.
[11 Apr 2014 20:22]
Sveta Smirnova
Thank you for the feedback.
I now got your point: when innodb_max_purge_lag set to positive value and innodb_max_purge_lag_delay set to 0 calculated delay, based on innodb_max_purge_lag, should be used. But it is not used, because when if statement "if (delay > srv_max_purge_lag_delay) {" compares it with calculated delay calculated delay got set to value of srv_max_purge_lag_delay) which is 0.
Verified as described using code analysis.
Description: By default svr_max_purge_lag_delay is 0. When the calculated delay is greater than srv_max_purge_lag_delay innodb the returned delay is set to srv_max_purge_lag_delay which overwrites the calculated delay: The code from trx/trx0purge.cc if (srv_max_purge_lag > 0) { float ratio; ratio = float(trx_sys->rseg_history_len) / srv_max_purge_lag; if (ratio > 1.0) { /* If the history list length exceeds the srv_max_purge_lag, the data manipulation statements are delayed by at least 5000 microseconds. */ delay = (ulint) ((ratio - .5) * 10000); } if (delay > srv_max_purge_lag_delay) { delay = srv_max_purge_lag_delay; } How to repeat: Get a server running frequent enough updates that the purge threads fall behind. Set only innodb_max_purge_lag > 0 so that history_list_length / innodb_max_purge_lag > 1. This will let innodb calculate the delay and se no change in the lag. Then set innodb_max_purge_lag_delay > 0 and watch the delay go down while dml statements run slower. I also have this funky looking query which outputs the delay as it would be calculated by innodb among other things. select variable_name, variable_value, if (((variable_value / @@innodb_max_purge_lag) - .5) * 10000 > @@innodb_max_purge_lag_delay, @@innodb_max_purge_lag_delay, ((variable_value / @@innodb_max_purge_lag) - .5) * 10000) as calculated_delay, @@innodb_max_purge_lag, @@innodb_max_purge_lag_delay from information_schema.global_status where variable_name like 'INNODB_PURGE_PENDING'; Suggested fix: if (svr_max_purge_lag_delay > 0 && delay > svr_max_purge_lag_delay)