Bug #71747 Remove innodb_io_capacity setting depending on setting of innodb_io_capacity_max
Submitted: 17 Feb 2014 9:42 Modified: 17 Feb 2014 10:25
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.15, 5.6.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, innodb_io_capacity, innodb_io_capacity_max

[17 Feb 2014 9:42] Simon Mudd
Description:
Setting innodb_io_capacity does not work if it changes it higher than the value of innodb_io_capacity_max.
That is unhelpful and thus requires 2 settings to be (a) configured, and (b) changed in the _right_ order.

How to repeat:
Server version: 5.6.15-log MySQL Community Server (GPL)

This server was started with /etc/my.cnf having:

innodb_io_capacity = 2000

Subsequently the configuration has been changed (and we have a scripted mechanism to sync the /etc/my.cnf settings to the db server if they are different:

[smudd@myserver ~]$ grep io_capacity /etc/my.cnf
innodb_io_capacity = 2500
[smudd@myserver ~]$ mysql
...
root@myserver [(none)]> show global variables like '%\_io_capacity%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 2000  |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+
2 rows in set (0.00 sec)

root@myserver [(none)]> set global  innodb_io_capacity = 2500;
Query OK, 0 rows affected, 2 warnings (0.10 sec)

root@myserver [(none)]> show warnings;
+---------+------+----------------------------------------------------------------------+
| Level   | Code | Message                                                              |
+---------+------+----------------------------------------------------------------------+
| Warning | 1210 | innodb_io_capacity cannot be set higher than innodb_io_capacity_max. |
| Warning | 1210 | Setting innodb_io_capacity to 2000                                   |
+---------+------+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@myserver [(none)]> show global variables like '%\_io_capacity%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 2000  |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+
2 rows in set (0.00 sec)

root@myserver [(none)]>

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_io_capacity_ma...

says: 
    The limit up to which InnoDB is allowed to extend the innodb_io_capacity
    setting in case of emergency. Its default value is twice the default
    value of innodb_io_capacity, with a lower limit of 2000. It is
    inoperative if you have specified any value for innodb_io_capacity
    at server startup.

Suggested fix:
Suggested fixes:

(1a) Change the behaviour so that if I _explicitly_ set innodb_io_capacity to a value > innodb_io_capacity_max that the value is accepted AND that innodb_io_capacity_max is increased (if necessary) at least until the new setting of Innodb_io_capacity.

(1b) Given that the default setting for innodb_io_capacity_max = 2 x innodb_io_capacity, I would suggest that if innodb_io_capacity is adjusted the _max value is adjusted to 2 x the innodb_io_capacity value if the current value of innodb_io_capacity_max is below that value.

(1c) If someone wants to reduce innodb_io_capacity_max and reduce it _below_ innodb_io_capacity then I guess innodb_io_capacity should be reduced to the same level as innodb_io_capacity.

(2a) describe for the "max" parameter what the "in case of emergency" really means. If necessary point directly to another section of the manual if this description is going to be long.
(2b) Describe the interaction of the 2 parameters so there is no confusion about what will happen and under what circumstances.

Personally I find the variable innodb_io_capacity_max rather confusing. It has already been renamed from the name it had in pre-5.6 GA to its current name and it seems to be a value which depends exclusively on the innodb_io_capacity setting, so I'm not sure why we really need an additional configuration setting.
[17 Feb 2014 10:25] MySQL Verification Team
Hello Simon,

Thank you for the bug report.

Thanks,
Umehs