Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.0.14
-
None
Description
http://bugs.mysql.com/bug.php?id=71747
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:
<pre>
[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)]>
</pre>
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.
Attachments
Issue Links
- causes
-
-
- Closed
-
Activity
revno: 4482
committer: Jan Lindström <jplindst@mariadb.org>
branch nick: 10.0-bugs
timestamp: Thu 2014-11-13 13:24:26 +0200
message:
MDEV-7035: Remove innodb_io_capacity setting depending on
setting of innodb_io_capacity_max
(a) Changed the behaviour so that if you set innodb_io_capacity to a
value > innodb_io_capacity_max that the value is accepted AND
that innodb_io_capacity_max = innodb_io_capacity * 2.
(b) If someone wants to reduce innodb_io_capacity_max and
reduce it below innodb_io_capacity then innodb_io_capacity
should be reduced to the same level as innodb_io_capacity_max.
In both cases give a warning to user.
There is 2 places at code where innodb_io_capacity_max is used. Firstly, on page cleaner. This function is called approximately once every second by the page_cleaner thread. Based on various factors it decides if there is a need to do flushing. If flushing is needed it is performed and the number of pages flushed is returned. In this function the number of pages requested to be flushed is modified to be no more than max setting:
if (n_pages > srv_max_io_capacity) {n_pages = srv_max_io_capacity;}/* Calculates if flushing is required based on redo generation rate. percent of io_capacity to flush to manage redo space */ut_ad(srv_max_io_capacity >= srv_io_capacity);switch ((srv_cleaner_lsn_age_factor_t)srv_cleaner_lsn_age_factor) {case SRV_CLEANER_LSN_AGE_FACTOR_LEGACY:return(static_cast<ulint>(((srv_max_io_capacity / srv_io_capacity)* (lsn_age_factor* sqrt((double)lsn_age_factor)))/ 7.5));case SRV_CLEANER_LSN_AGE_FACTOR_HIGH_CHECKPOINT:return(static_cast<ulint>(((srv_max_io_capacity / srv_io_capacity)* (lsn_age_factor * lsn_age_factor* sqrt((double)lsn_age_factor)))/ 700.5));On InnoDB only the first calculation exists.