Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.32, 10.5.23, 10.6.16, 10.11.5
-
docker mariadb:10.11
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
- 0.002s.png
- 33 kB
- 50s.png
- 31 kB
- backup.sql
- 7.43 MB
- init_tables.sql
- 9.01 MB
- select.sql
- 18 kB
Issue Links
- relates to
-
MDEV-29445 reorganise innodb buffer pool (and remove buffer pool chunks)
-
- In Review
-
Activity
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".
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.
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.
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)