Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7035

Remove innodb_io_capacity setting depending on setting of innodb_io_capacity_max

    XMLWordPrintable

    Details

      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

          Activity

            People

            Assignee:
            jplindst Jan Lindström
            Reporter:
            jplindst Jan Lindström
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: