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

Setting innodb_buffer_pool_size to the maximum value can cause drastic performance degradation

Details

    Description

      docker run --restart=always --name mariadb1011enginevalidate -e MYSQL_ROOT_PASSWORD=123456 -d mariadb:10.11

      > create database test;
      > use test;
      > source init_tables.sql;
      > source select.sql;

      When executing source select.sql normally, you will get that the empty set takes about 0.002s.
      But when SET GLOBAL innodb_buffer_pool_size = 9223372036854775807; this value is the legal maximum value described in the document, and the execution time of source select.sql can reach about 50 seconds.
      Performance drops greatly. Why is this?

      https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size

      Attachments

        1. 0.002s.png
          0.002s.png
          33 kB
        2. 50s.png
          50s.png
          31 kB
        3. backup.sql
          7.43 MB
        4. init_tables.sql
          9.01 MB
        5. select.sql
          18 kB

        Issue Links

          Activity

            ying fuying added a comment -

            Thank you both very much for your replies!
            I am using fuzz testing technology to test MariaDB, so there may indeed be problems in the generated SQL. I used source init_tables.sql; to execute it before. Although there was an error message, it did not affect the reproduce. So this time I use mysqldump to export the database status. You should be able to create the initial database by executing backup.sql.

            Here are my creation steps
            1. Start a new MariaDB 10.11 container
            2. CREATE DATABASE TEST;
            3.USE TEST;
            4. source backup.sql backup.sql

            After creating the initial database state, I tested and still have this problem:
            execute the statements in select.sql
            >Empty set (0.003 sec)

            >SET GLOBAL innodb_buffer_pool_size = 9223372036854775807;
            >Query OK, 0 rows affected, 1 warning (0.000 sec)
            Execute the statements in select.sql
            >Empty set (52.228 sec)

            ying fuying added a comment - Thank you both very much for your replies! I am using fuzz testing technology to test MariaDB, so there may indeed be problems in the generated SQL. I used source init_tables.sql; to execute it before. Although there was an error message, it did not affect the reproduce. So this time I use mysqldump to export the database status. You should be able to create the initial database by executing backup.sql. Here are my creation steps 1. Start a new MariaDB 10.11 container 2. CREATE DATABASE TEST; 3.USE TEST; 4. source backup.sql backup.sql After creating the initial database state, I tested and still have this problem: execute the statements in select.sql >Empty set (0.003 sec) >SET GLOBAL innodb_buffer_pool_size = 9223372036854775807; >Query OK, 0 rows affected, 1 warning (0.000 sec) Execute the statements in select.sql >Empty set (52.228 sec)
            danblack Daniel Black added a comment -

            Much easier thanks.

            The logs from SET GLOBAL innodb_buffer_pool_size = 9223372036854775807

            Version: '10.11.6-MariaDB-1:10.11.6+maria~ubu2204'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
            2023-11-21  2:43:06 0 [Note] InnoDB: Buffer pool(s) load completed at 231121  2:43:06
            2023-11-21  2:43:53 0 [Note] InnoDB: Resizing buffer pool from 128.000MiB to 8.000EiB (unit = 2.000MiB).
            2023-11-21  2:43:53 0 [Note] InnoDB: Disabling adaptive hash index.
            2023-11-21  2:43:53 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
            2023-11-21  2:43:53 0 [Note] InnoDB: Latching entire buffer pool.
            2023-11-21  2:43:53 0 [Note] InnoDB: Resizing buffer pool from 64 chunks to 4398046511104 chunks.
            2023-11-21  2:44:52 0 [ERROR] InnoDB: Cannot allocate 211106232533016 bytes of memory after 60 retries over 60 seconds. OS error: Cannot allocate memory (12). Check if you should increase the swap file or ulimits of your operating system. Note that on most 32-bit computers the process memory space is limited to 2 GB or 4 GB.
            2023-11-21  2:44:52 0 [ERROR] InnoDB: failed to allocate the chunk array.
            2023-11-21  2:44:52 0 [Note] InnoDB: Completed resizing buffer pool from 134217728 to 9223372036854775808 bytes.
            2023-11-21  2:44:52 0 [Note] InnoDB: Resizing buffer pool failed
            

            Do it again

            MariaDB [(none)]> select @@innodb_buffer_pool_size;
            +---------------------------+
            | @@innodb_buffer_pool_size |
            +---------------------------+
            |                 134217728 |
            +---------------------------+
            1 row in set (0.000 sec)
             
            MariaDB [(none)]> SET GLOBAL innodb_buffer_pool_size = 9223372036854775807;
            Query OK, 0 rows affected, 1 warning (0.000 sec)
             
            MariaDB [(none)]> show warnings;                                           
            +---------+------+--------------------------------------------------------------------------------+
            | Level   | Code | Message                                                                        |
            +---------+------+--------------------------------------------------------------------------------+
            | Warning | 1210 | innodb_buffer_pool_size must be at least innodb_buffer_pool_chunk_size=2097152 |
            +---------+------+--------------------------------------------------------------------------------+
            1 row in set (0.000 sec)
             
            MariaDB [(none)]> select @@innodb_buffer_pool_size;                        
            +---------------------------+
            | @@innodb_buffer_pool_size |
            +---------------------------+
            |                 134217728 |
            +---------------------------+
            1 row in set (0.000 sec)
            

            and:

            2023-11-21  2:47:11 0 [Note] InnoDB: Size did not change: old size = new size = 9223372036854775808
            

            So the reason the query takes so long is the 60 seconds on retry in which there's buffer pool lock on the innodb buffer pool while resizing to prevent the query.

            However, there's obviously a bunch of contradictory state about the the resize failing "Cannot allocate", "Completed", but "Resizing failed".

            danblack Daniel Black added a comment - Much easier thanks. The logs from SET GLOBAL innodb_buffer_pool_size = 9223372036854775807 Version: '10.11.6-MariaDB-1:10.11.6+maria~ubu2204' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution 2023-11-21 2:43:06 0 [Note] InnoDB: Buffer pool(s) load completed at 231121 2:43:06 2023-11-21 2:43:53 0 [Note] InnoDB: Resizing buffer pool from 128.000MiB to 8.000EiB (unit = 2.000MiB). 2023-11-21 2:43:53 0 [Note] InnoDB: Disabling adaptive hash index. 2023-11-21 2:43:53 0 [Note] InnoDB: Withdrawing blocks to be shrunken. 2023-11-21 2:43:53 0 [Note] InnoDB: Latching entire buffer pool. 2023-11-21 2:43:53 0 [Note] InnoDB: Resizing buffer pool from 64 chunks to 4398046511104 chunks. 2023-11-21 2:44:52 0 [ERROR] InnoDB: Cannot allocate 211106232533016 bytes of memory after 60 retries over 60 seconds. OS error: Cannot allocate memory (12). Check if you should increase the swap file or ulimits of your operating system. Note that on most 32-bit computers the process memory space is limited to 2 GB or 4 GB. 2023-11-21 2:44:52 0 [ERROR] InnoDB: failed to allocate the chunk array. 2023-11-21 2:44:52 0 [Note] InnoDB: Completed resizing buffer pool from 134217728 to 9223372036854775808 bytes. 2023-11-21 2:44:52 0 [Note] InnoDB: Resizing buffer pool failed Do it again MariaDB [(none)]> select @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 134217728 | +---------------------------+ 1 row in set (0.000 sec)   MariaDB [(none)]> SET GLOBAL innodb_buffer_pool_size = 9223372036854775807; Query OK, 0 rows affected, 1 warning (0.000 sec)   MariaDB [(none)]> show warnings; +---------+------+--------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------+ | Warning | 1210 | innodb_buffer_pool_size must be at least innodb_buffer_pool_chunk_size=2097152 | +---------+------+--------------------------------------------------------------------------------+ 1 row in set (0.000 sec)   MariaDB [(none)]> select @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 134217728 | +---------------------------+ 1 row in set (0.000 sec) and: 2023-11-21 2:47:11 0 [Note] InnoDB: Size did not change: old size = new size = 9223372036854775808 So the reason the query takes so long is the 60 seconds on retry in which there's buffer pool lock on the innodb buffer pool while resizing to prevent the query. However, there's obviously a bunch of contradictory state about the the resize failing "Cannot allocate", "Completed", but "Resizing failed".
            danblack Daniel Black added a comment -

            Version: '10.4.32-MariaDB-1:10.4.32+maria~ubu2004'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
            2023-11-21  2:57:21 0 [Note] InnoDB: Buffer pool(s) load completed at 231121  2:57:21
            2023-11-21  2:57:32 8 [Note] InnoDB:  (new size: -9223372036854775808 bytes)
            2023-11-21  2:57:32 0 [Note] InnoDB: Resizing buffer pool from 268435456 to 9223372036854775808 (unit=134217728).
            2023-11-21  2:57:32 0 [Note] InnoDB: Disabling adaptive hash index.
            2023-11-21  2:57:32 0 [Note] InnoDB: disabled adaptive hash index.
            2023-11-21  2:57:32 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
            2023-11-21  2:57:32 0 [Note] InnoDB: Latching whole of buffer pool.
            2023-11-21  2:57:32 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 2 to 68719476736.
            2023-11-21  2:57:32 0 [ERROR] InnoDB: buffer pool 0 : failed to allocate the chunk array.
            2023-11-21  2:57:32 0 [Note] InnoDB: Completed to resize buffer pool from 268435456 to 9223372036854775808.
            2023-11-21  2:57:32 0 [Note] InnoDB: Re-enabled adaptive hash index.
            2023-11-21  2:57:32 0 [Note] InnoDB: Resizing buffer pool failed, finished resizing at 231121  2:57:32.
            

            10.4 doesn't delay on the the select as it doesn't keep trying. However the output message around "Completed to resize" needs correction..

            10.4

            MariaDB [(none)]> select @@innodb_buffer_pool_size;
            +---------------------------+
            | @@innodb_buffer_pool_size |
            +---------------------------+
            |                 268435456 |
            +---------------------------+
            1 row in set (0.000 sec)
             
            MariaDB [(none)]> SET GLOBAL innodb_buffer_pool_size = 9223372036854775807; show warnings ;
            Query OK, 0 rows affected, 1 warning (0.000 sec)
             
            +---------+------+--------------------------------------------------------------------------+
            | Level   | Code | Message                                                                  |
            +---------+------+--------------------------------------------------------------------------+
            | Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '9223372036854775807' |
            +---------+------+--------------------------------------------------------------------------+
            1 row in set (0.000 sec)
             
            MariaDB [(none)]> select @@innodb_buffer_pool_size;                                        
            +---------------------------+
            | @@innodb_buffer_pool_size |
            +---------------------------+
            |                 268435456 |
            +---------------------------+
            1 row in set (0.000 sec)
            

            Try behaviour starts at 10.5:

            10.5.23-6cfd2ba397b0ca689d8ff1bdb9fc4a4dc516a5eb / 10.6.16-b83c379420a8846ae4b28768d3c81fa354cca056

            2023-11-21  2:59:00 0 [Note] InnoDB: Buffer pool(s) load completed at 231121  2:59:00
            2023-11-21  2:59:03 3 [Note] InnoDB: Requested to resize buffer pool. (new size: -9223372036854775808 bytes)
            2023-11-21  2:59:03 0 [Note] InnoDB: Resizing buffer pool from 134217728 to 9223372036854775808 (unit=134217728).
            2023-11-21  2:59:03 0 [Note] InnoDB: Disabling adaptive hash index.
            2023-11-21  2:59:03 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
            2023-11-21  2:59:03 0 [Note] InnoDB: Latching whole of buffer pool.
            2023-11-21  2:59:03 0 [Note] InnoDB: buffer pool resizing with chunks 1 to 68719476736.
            2023-11-21  3:00:02 0 [ERROR] InnoDB: Cannot allocate 3298534883352 bytes of memory after 60 retries over 60 seconds. OS error: Cannot allocate memory (12). Check if you should increase the swap file or ulimits of your operating system. Note that on most 32-bit computers the process memory space is limited to 2 GB or 4 GB.
            2023-11-21  3:00:02 0 [ERROR] InnoDB: failed to allocate the chunk array.
            2023-11-21  3:00:02 0 [Note] InnoDB: Completed to resize buffer pool from 134217728 to 9223372036854775808.
            2023-11-21  3:00:02 0 [Note] InnoDB: Resizing buffer pool failed, finished resizing at 231121  3:00:02.
            

            10.5

            MariaDB [(none)]> select @@innodb_buffer_pool_size;
            +---------------------------+
            | @@innodb_buffer_pool_size |
            +---------------------------+
            |                 134217728 |
            +---------------------------+
            1 row in set (0.000 sec)
             
            MariaDB [(none)]> 
            MariaDB [(none)]>  SET GLOBAL innodb_buffer_pool_size = 9223372036854775807; show warnings ;
            Query OK, 0 rows affected, 1 warning (0.000 sec)
             
            +---------+------+--------------------------------------------------------------------------+
            | Level   | Code | Message                                                                  |
            +---------+------+--------------------------------------------------------------------------+
            | Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '9223372036854775807' |
            +---------+------+--------------------------------------------------------------------------+
            1 row in set (0.000 sec)
             
            MariaDB [(none)]> 
            MariaDB [(none)]> select @@innodb_buffer_pool_size;                                         
            +---------------------------+
            | @@innodb_buffer_pool_size |
            +---------------------------+
            |                 134217728 |
            +---------------------------+
            1 row in set (0.000 sec)
             
            (after a 1minute)
            MariaDB [(none)]> select @@innodb_buffer_pool_size;
            +---------------------------+
            | @@innodb_buffer_pool_size |
            +---------------------------+
            |                 134217728 |
            +---------------------------+
            1 row in set (0.000 sec)
            

            so its just that 10.11 gets nicer units and avoids negative sizes on memory.

            danblack Daniel Black added a comment - Version: '10.4.32-MariaDB-1:10.4.32+maria~ubu2004' socket: '/var/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution 2023-11-21 2:57:21 0 [Note] InnoDB: Buffer pool(s) load completed at 231121 2:57:21 2023-11-21 2:57:32 8 [Note] InnoDB: (new size: -9223372036854775808 bytes) 2023-11-21 2:57:32 0 [Note] InnoDB: Resizing buffer pool from 268435456 to 9223372036854775808 (unit=134217728). 2023-11-21 2:57:32 0 [Note] InnoDB: Disabling adaptive hash index. 2023-11-21 2:57:32 0 [Note] InnoDB: disabled adaptive hash index. 2023-11-21 2:57:32 0 [Note] InnoDB: Withdrawing blocks to be shrunken. 2023-11-21 2:57:32 0 [Note] InnoDB: Latching whole of buffer pool. 2023-11-21 2:57:32 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 2 to 68719476736. 2023-11-21 2:57:32 0 [ERROR] InnoDB: buffer pool 0 : failed to allocate the chunk array. 2023-11-21 2:57:32 0 [Note] InnoDB: Completed to resize buffer pool from 268435456 to 9223372036854775808. 2023-11-21 2:57:32 0 [Note] InnoDB: Re-enabled adaptive hash index. 2023-11-21 2:57:32 0 [Note] InnoDB: Resizing buffer pool failed, finished resizing at 231121 2:57:32. 10.4 doesn't delay on the the select as it doesn't keep trying. However the output message around "Completed to resize" needs correction.. 10.4 MariaDB [(none)]> select @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 268435456 | +---------------------------+ 1 row in set (0.000 sec)   MariaDB [(none)]> SET GLOBAL innodb_buffer_pool_size = 9223372036854775807; show warnings ; Query OK, 0 rows affected, 1 warning (0.000 sec)   +---------+------+--------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '9223372036854775807' | +---------+------+--------------------------------------------------------------------------+ 1 row in set (0.000 sec)   MariaDB [(none)]> select @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 268435456 | +---------------------------+ 1 row in set (0.000 sec) Try behaviour starts at 10.5: 10.5.23-6cfd2ba397b0ca689d8ff1bdb9fc4a4dc516a5eb / 10.6.16-b83c379420a8846ae4b28768d3c81fa354cca056 2023-11-21 2:59:00 0 [Note] InnoDB: Buffer pool(s) load completed at 231121 2:59:00 2023-11-21 2:59:03 3 [Note] InnoDB: Requested to resize buffer pool. (new size: -9223372036854775808 bytes) 2023-11-21 2:59:03 0 [Note] InnoDB: Resizing buffer pool from 134217728 to 9223372036854775808 (unit=134217728). 2023-11-21 2:59:03 0 [Note] InnoDB: Disabling adaptive hash index. 2023-11-21 2:59:03 0 [Note] InnoDB: Withdrawing blocks to be shrunken. 2023-11-21 2:59:03 0 [Note] InnoDB: Latching whole of buffer pool. 2023-11-21 2:59:03 0 [Note] InnoDB: buffer pool resizing with chunks 1 to 68719476736. 2023-11-21 3:00:02 0 [ERROR] InnoDB: Cannot allocate 3298534883352 bytes of memory after 60 retries over 60 seconds. OS error: Cannot allocate memory (12). Check if you should increase the swap file or ulimits of your operating system. Note that on most 32-bit computers the process memory space is limited to 2 GB or 4 GB. 2023-11-21 3:00:02 0 [ERROR] InnoDB: failed to allocate the chunk array. 2023-11-21 3:00:02 0 [Note] InnoDB: Completed to resize buffer pool from 134217728 to 9223372036854775808. 2023-11-21 3:00:02 0 [Note] InnoDB: Resizing buffer pool failed, finished resizing at 231121 3:00:02. 10.5 MariaDB [(none)]> select @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 134217728 | +---------------------------+ 1 row in set (0.000 sec)   MariaDB [(none)]> MariaDB [(none)]> SET GLOBAL innodb_buffer_pool_size = 9223372036854775807; show warnings ; Query OK, 0 rows affected, 1 warning (0.000 sec)   +---------+------+--------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '9223372036854775807' | +---------+------+--------------------------------------------------------------------------+ 1 row in set (0.000 sec)   MariaDB [(none)]> MariaDB [(none)]> select @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 134217728 | +---------------------------+ 1 row in set (0.000 sec)   (after a 1minute) MariaDB [(none)]> select @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 134217728 | +---------------------------+ 1 row in set (0.000 sec) so its just that 10.11 gets nicer units and avoids negative sizes on memory.
            danblack Daniel Black added a comment -

            So:
            10.4+:

            • fix "InnoDB: Completed to resize buffer pool" (not) error message

            10.5:

            • Fix signed-ness of "Requested to resize buffer pool. (new size: -9223372036854775808 bytes)"
            • Remove "Note that on most 32-bit computer" when not a 32 bit computer.
            • 60 second retry in storage/innobase/include/ut0new.h - exclude/reduce retries for buffer pool? When throw_on_error is true?

            10.11:

            • Use human readable notation on "Cannot allocate 211106232533016 bytes of memory" and "Completed resizing buffer pool"
            danblack Daniel Black added a comment - So: 10.4+: fix "InnoDB: Completed to resize buffer pool" (not) error message 10.5: Fix signed-ness of "Requested to resize buffer pool. (new size: -9223372036854775808 bytes)" Remove "Note that on most 32-bit computer" when not a 32 bit computer. 60 second retry in storage/innobase/include/ut0new.h - exclude/reduce retries for buffer pool? When throw_on_error is true? 10.11: Use human readable notation on "Cannot allocate 211106232533016 bytes of memory" and "Completed resizing buffer pool"

            danblack, thank you, these suggestions sound reasonable to me. I agree that the ut_allocator::allocate() retry logic does not make any sense when allocating the buffer pool. If the small allocation of a buffer pool chunk fails, then the large allocation of the additional buffer pool memory is very likely to fail as well. This code would be rewritten in MDEV-29445, where I would like to move to a single contiguous range of virtual memory addresses for the buffer pool.

            marko Marko Mäkelä added a comment - danblack , thank you, these suggestions sound reasonable to me. I agree that the ut_allocator::allocate() retry logic does not make any sense when allocating the buffer pool. If the small allocation of a buffer pool chunk fails, then the large allocation of the additional buffer pool memory is very likely to fail as well. This code would be rewritten in MDEV-29445 , where I would like to move to a single contiguous range of virtual memory addresses for the buffer pool.

            People

              danblack Daniel Black
              ying fuying
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.