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

SET GLOBAL innodb_buffer_pool_size=12*1024*1024 has different outcomes depending on version

Details

    Description

      SET GLOBAL innodb_buffer_pool_size=12*1024*1024;
      

      Has very different outcomes depending on what version you use (note 10.4, and 10.8 vs other versions):

      10.3.36 9d10b7107cf022b939dc61cedf8fc8985443c880 (Debug)

      10.3.36-dbg>SET GLOBAL innodb_buffer_pool_size=12*1024*1024;
      Query OK, 0 rows affected, 1 warning (0.000 sec)
       
      10.3.36-dbg>SHOW WARNINGS;
      +---------+------+----------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                          |
      +---------+------+----------------------------------------------------------------------------------+
      | Warning | 1210 | innodb_buffer_pool_size must be at least innodb_buffer_pool_chunk_size=134217728 |
      +---------+------+----------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      10.3.36-dbg>SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
      +-------------------------+-----------+
      | Variable_name           | Value     |
      +-------------------------+-----------+
      | innodb_buffer_pool_size | 134217728 |
      +-------------------------+-----------+
      1 row in set (0.001 sec)
      

      10.4.26 ebbd5ef6e2902a51a46e47dbb8a8667593cb25e7 (Debug)

      10.4.26-dbg>SET GLOBAL innodb_buffer_pool_size=12*1024*1024;
      ERROR 1231 (42000): Variable 'innodb_buffer_pool_size' can't be set to the value of '12582912'
      

      10.5.17 2840d7750db11a8d2ab3f212a05f5afefaef6d4d (Debug)

      10.5.17-dbg>SET GLOBAL innodb_buffer_pool_size=12*1024*1024;
      Query OK, 0 rows affected, 1 warning (0.000 sec)
       
      10.5.17-dbg>SHOW WARNINGS;
      +---------+------+----------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                          |
      +---------+------+----------------------------------------------------------------------------------+
      | Warning | 1210 | innodb_buffer_pool_size must be at least innodb_buffer_pool_chunk_size=134217728 |
      +---------+------+----------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      10.5.17-dbg>SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
      +-------------------------+-----------+
      | Variable_name           | Value     |
      +-------------------------+-----------+
      | innodb_buffer_pool_size | 134217728 |
      +-------------------------+-----------+
      1 row in set (0.004 sec)
      

      10.6.9 05d049bdbe6814aee8f011fbd0d915f9d82a30ee (Debug)

      10.6.9-dbg>SET GLOBAL innodb_buffer_pool_size=12*1024*1024;
      Query OK, 0 rows affected, 1 warning (0.000 sec)
       
      10.6.9-dbg>SHOW WARNINGS;
      +---------+------+----------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                          |
      +---------+------+----------------------------------------------------------------------------------+
      | Warning | 1210 | innodb_buffer_pool_size must be at least innodb_buffer_pool_chunk_size=134217728 |
      +---------+------+----------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      10.6.9-dbg>SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
      +-------------------------+-----------+
      | Variable_name           | Value     |
      +-------------------------+-----------+
      | innodb_buffer_pool_size | 134217728 |
      +-------------------------+-----------+
      1 row in set (0.005 sec)
      

      10.7.5 61727fa40f914370e843ff89a76eba7ef785c5fe (Debug)

      10.7.5-dbg>SET GLOBAL innodb_buffer_pool_size=12*1024*1024;
      Query OK, 0 rows affected, 1 warning (0.000 sec)
       
      10.7.5-dbg>SHOW WARNINGS;
      +---------+------+----------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                          |
      +---------+------+----------------------------------------------------------------------------------+
      | Warning | 1210 | innodb_buffer_pool_size must be at least innodb_buffer_pool_chunk_size=134217728 |
      +---------+------+----------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      10.7.5-dbg>SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
      +-------------------------+-----------+
      | Variable_name           | Value     |
      +-------------------------+-----------+
      | innodb_buffer_pool_size | 134217728 |
      +-------------------------+-----------+
      1 row in set (0.002 sec)
      

      10.8.4 0e0a3580efdae313fab340bbb308d371fa36c021 (Optimized)

      10.8.4-opt>SET GLOBAL innodb_buffer_pool_size=12*1024*1024;
      Query OK, 0 rows affected (0.000 sec)
      

      10.9.2 6ec17142dcfb1e9d9f41211ed1b6d82e062d1541 (Optimized)

      10.9.2-opt>SET GLOBAL innodb_buffer_pool_size=12*1024*1024;
      Query OK, 0 rows affected (0.001 sec)
      

      10.10.0 081a284712bb661349e2e3802077b12211cede3e (Debug)

      10.10.0-dbg>SET GLOBAL innodb_buffer_pool_size=12*1024*1024;
      Query OK, 0 rows affected (0.003 sec)
      

      Given MDEV-28804, I am not sure if 10.3 to 10.7 are affected versions, or 10.4, or rather 10.8 to 10.10 etc. (i.e. these latter versions are not failing here, but look to be giving performance issues). In any case, 10.4 is failing.

      Attachments

        Issue Links

          Activity

            danblack Daniel Black added a comment -

            The buffer pool size range of valid values is determined by the innodb_buffer_pool_chunk_size. Since MDEV-25342 in 10.8 this became smaller allowing a lower value.

            When the previous innodb_buffer_pool_chunk_size default was 128M of course its impossible to set a 12M pool size.

            danblack Daniel Black added a comment - The buffer pool size range of valid values is determined by the innodb_buffer_pool_chunk_size. Since MDEV-25342 in 10.8 this became smaller allowing a lower value. When the previous innodb_buffer_pool_chunk_size default was 128M of course its impossible to set a 12M pool size.
            Roel Roel Van de Paar added a comment - - edited

            danblack Thank you. That explains 10.8+, but not the issue observed in 10.4.

            Roel Roel Van de Paar added a comment - - edited danblack Thank you. That explains 10.8+, but not the issue observed in 10.4.
            danblack Daniel Black added a comment -

            Looks like 10.4, setting to a value less than specified was some odd behaviour and wrong. Can't find it now. With 10.4 eol I think this can be closed?

            danblack Daniel Black added a comment - Looks like 10.4, setting to a value less than specified was some odd behaviour and wrong. Can't find it now. With 10.4 eol I think this can be closed?
            Roel Roel Van de Paar added a comment - - edited

            Closing as suggested

            Thank you

            Roel Roel Van de Paar added a comment - - edited Closing as suggested Thank you

            People

              danblack Daniel Black
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.