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

    XMLWordPrintable

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

            People

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

              Dates

                Created:
                Updated:

                Git Integration

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