[MDEV-13013] InnoDB unnecessarily extends data files Created: 2017-06-06  Updated: 2023-03-13  Resolved: 2022-08-17

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.10, 10.7.6, 10.8.5, 10.9.3, 10.10.2

Type: Bug Priority: Critical
Reporter: Marko Mäkelä Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 2
Labels: space, startup

Issue Links:
Blocks
blocks MDEV-26782 InnoDB temporary tablespace: reclaimi... Closed
Relates
relates to MDEV-21952 ibdata1 file size growing in MariaDB Closed
relates to MDEV-28038 Why is there a big gap in the actual ... Open
relates to MDEV-28699 Shrink temporary tablespaces without ... Closed
relates to MDEV-11585 Hard-code the shared InnoDB temporary... Closed
relates to MDEV-12042 Re-bootstrap the server if InnoDB opt... Closed
relates to MDEV-12289 Keep 128 persistent rollback segments... Closed
relates to MDEV-12600 crash during install_db with innodb_p... Closed
relates to MDEV-14887 On a 32-bit system, MariaDB 10.2 mish... Closed
relates to MDEV-26525 Replication stops because too big inn... Closed

 Description   

There are several bugs in the way how the InnoDB temporary tablespace is handled at InnoDB startup.

The theoretical minimum size of the InnoDB temporary tablespace should be one allocation extent (1M for innodb_page_size=16k or less, 2M for innodb_page_size=32k, 4M for innodb_page_size=64k). For simplicity, it should be possible to set the minimum size as 4M.

MDEV-12289 creates 128 rollback segments for the temporary tablespace. This might be an overkill, and perhaps we should create only 32 of them, like MySQL 5.7 does.

In SysTablespace::open_or_create(), even if the specification lacks :autoextend for the last file, max_size=ULINT_MAX will be incorrectly assigned. This bug was introduced in MySQL 5.7 by WL#7943 https://github.com/mysql/mysql-server/commit/38e3aa74d8d2bf882863d9586ad8c9e9ed2c4f00, most of which was reverted from MariaDB 10.2 in MDEV-11426.

MDEV-11585 accidentally inverted a condition in fsp_fill_free_list() that decides if the innodb_temporary tablespace should be extended.

The biggest problem is that trx_rseg_header_create() is trying to extend the temporary tablespace already for creating the very first rollback segment. This happens for a 12MiB temporary tablespace when using 64KiB page size. The following test will crash:

./mtr --mysqld=--innodb-buffer-pool-size=24m --mysqld=--innodb-page-size=64k --mem innodb.temporary_table

This must be fixed, so that the test can be run with all innodb_page_size combinations.

 Comments   
Comment by Thirunarayanan Balathandayuthapani [ 2018-02-19 ]

Minimum test case:

temp1.opt
 
========
 
--innodb_temp_data_file_path=ibtmp1:12M
 
--innodb-page-size=64k
 
--innodb-buffer-pool-size=24M\
 
 
 
temp1.test
 
==========
 
 
 
--source include/have_innodb.inc
 
 
 
create temporary table t1
 
        (keyc int, c1 char(100), c2 char(100),
 
         primary key(keyc)) engine = innodb;
 
 
 
Create table failed with error 1005: Can't create table `test`.`t1` (errno: 135 "No more room in record file").

The above test case fails only during create table in 10.2. It fails because there are no undo logs created
for temporary tablespace.

But in mysql-5.7, It fails while starting the mysql-5.7 server with following error:

2018-02-19T08:22:03.003594Z 0 [ERROR] InnoDB: System or UNDO tablespace is running of out of space.
 
During server startup, they have check whether 128 rollback segments are created (96 redo and 32 noredo segments)
 
It doesn't create 32 no-redo rollback segment and it fails during startup.

During server startup, InnoDB tries to create rollback segment in temporary tablespace(fseg_create()).
It always call with the assumption that there is no reservation done before. (fseg_create_general).
fsp_reserve_free_extents() also tries to reserve the extent in FSP_NORMAL allocation mode.

  switch (alloc_type) {
 
        case FSP_NORMAL:
 
                /* We reserve 1 extent + 0.5 % of the space size to undo logs
 
                and 1 extent + 0.5 % to cleaning operations; NOTE: this source
 
                code is duplicated in the function below! */
 
 
 
                reserve = 2 + ((size / FSP_EXTENT_SIZE) * 2) / 200;
 
 
 
                if (n_free <= reserve + n_ext) {
 
 
 
                        goto try_to_extend;
 
                }
 
                break;

Above comment made in the function looks like invalid one. Theortically we have 2 free extents(128 pages) and 63 free pages.
These pages are enough to create inode pages and 128 rollback segment pages. We have to evaluate the comments are valid now
and re-evaluate the condition for small tablespace and assumption of no reservation before.

These changes are more risky to 10.3. Hence moving this issue to 10.4.

Another observation:

For page size = 64k, ibtmp1 is 76mb (mysql-5.7 and 10.3)
There is no change in file size because of 128 temporary tablespace rollback segments in 10.3

Comment by Alice Sherepa [ 2021-11-26 ] 

[ERROR] InnoDB: The InnoDB temporary tablespace ran out of space. Please add another file or use 'autoextend' for the last file in setting innodb_temp_data_file_path.
 
211126 12:32:16 [ERROR] mysqld got signal 11 ;
 
Server version: 10.7.2-MariaDB-debug
 
 
 
include/buf0types.h:130(page_id_t::page_no() const)[0x562c2d85b657]
 
trx/trx0rseg.cc:709(trx_temp_rseg_create())[0x562c2dd19611]
 
srv/srv0start.cc:1840(srv_start(bool))[0x562c2dcb4976]
 
handler/ha_innodb.cc:4206(innodb_init(void*))[0x562c2d7fc45f]
 
sql/handler.cc:649(ha_initialize_handlerton(st_plugin_int*))[0x562c2cd777ed]
 
sql/sql_plugin.cc:1462(plugin_initialize(st_mem_root*, st_plugin_int*, int*, char**, bool))[0x562c2c54908a]
 
sql/sql_plugin.cc:1755(plugin_init(int*, char**, int))[0x562c2c54b023]
 
sql/mysqld.cc:5082(init_server_components())[0x562c2c11e400]
 
sql/mysqld.cc:5695(mysqld_main(int, char**))[0x562c2c120682]
 
sql/main.cc:34(main)[0x562c2c108e8d]
 
??:0(__libc_start_main)[0x7f906cbc70b3]
 
??:0(_start)[0x562c2c108dae]


[ERROR] InnoDB: The InnoDB temporary tablespace ran out of space. Please add another file or use 'autoextend' for the last file in setting innodb_temp_data_file_path.
 
 
 
211126 12:34:47 [ERROR] mysqld got signal 11 ;
 
Server version: 10.2.42-MariaDB-debug
 
 
 
sigaction.c:0(__restore_rt)[0x7f96d3e963c0]
 
include/buf0types.h:163(page_id_t::page_no() const)[0x56045cde2ad7]
 
trx/trx0rseg.cc:320(trx_temp_rseg_create())[0x56045d144ab8]
 
srv/srv0start.cc:2614(innobase_start_or_create_for_mysql())[0x56045d0b8563]
 
handler/ha_innodb.cc:4361(innobase_init(void*))[0x56045cce5bde]
 
sql/handler.cc:555(ha_initialize_handlerton(st_plugin_int*))[0x56045c7c1faf]
 
sql/sql_plugin.cc:1417(plugin_initialize(st_mem_root*, st_plugin_int*, int*, char**, bool))[0x56045c1b5bd7]
 
sql/sql_plugin.cc:1699(plugin_init(int*, char**, int))[0x56045c1b790e]
 
sql/mysqld.cc:5389(init_server_components())[0x56045bf25a8f]
 
sql/mysqld.cc:5996(mysqld_main(int, char**))[0x56045bf27b50]

Comment by Marko Mäkelä [ 2021-11-26 ]

alice, which innodb_temp_data_file_path did you specify to get that SIGSEGV? I do not think that we test non-autoextend system or temporary tablespaces much.

Comment by Alice Sherepa [ 2021-12-07 ]

marko it is the case from the first comment, min test from Thiru. I was browsing for some other bug and tried this one if it is repeatable and then just decided to add stacktraces

Comment by Marko Mäkelä [ 2022-07-29 ]

thiru, the code change in bb-10.3-MDEV-13013 looks OK to me. Some updates to test .rdiff files should be cleaned up. A test seems to fail everywhere on our CI system.

Comment by Marko Mäkelä [ 2022-08-03 ]

While I am fairly confident that the fix is correct, I think that it is safer that we omit it from the quarterly releases that are being prepared right now, and implement this right after the releases are out. In that way, we will have 3 months time to catch and address any surprise regression.

Comment by Matthias Leich [ 2022-08-15 ] 

origin/bb-10.6-MDEV-13013 6e5f6a8d6a212b9cea6abe8db653de335ac58925 2022-08-01T12:18:18+05:30
 
behaved well in RQG testing. No new innoDB related problems.

Generated at Thu Feb 08 08:02:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.