I don't think it has ever been claimed that the ibdata1 would shrink as the result of defragmentation; but I find that our current documentation is not at all straightforward in regard to this question, so I'm assigning it to jplindst to clarify, especially in the KB.

greenman Can you clarify documentation that tablespace files (including ibdata1) will not shrink as the result of defragmentation.

thanks for the reply. Is the data actually moved to the beginning of the ibdata file ? is it difficult to add the possibility to shrink the file ibdata1 ?

Documentation now specifically mentions that the file size does not shrink. martina342 the only way to shrink the existing file size is with dumping and reloading. The article at https://www.thegeekstuff.com/2016/02/mysql-innodb-file-per-table/ seems to describe your scenario of moving from innodb_file_per_table = 0 to innodb_file_per_table=1

I think that it is a reasonable request to shrink the system tablespace after all tables have been moved out of it (innodb_file_per_table=1), dedicated undo tablespaces have been created (MDEV-19229) and the change buffer has been disabled (MDEV-27734).

We have to be careful about this in order to avoid introducing the equivalent of MDEV-29999. We may have to track changes not only to FSP_SIZE but also to FSP_FREE_LIMIT or something else that reliably tells the last actually used page offset within the file.

Because some code that is shared with undo tablespace truncation is being modified here, I think that we need to pay special attention to test coverage.

MDEV-29694 in 11.0 removed the InnoDB change buffer. If this change were ever applied to anything that supports the change buffer, then we should also think about deleting the change buffer records and clearing change buffer bitmap bits for the pages that will be trimmed off the end of the system tablespace.

I happened to notice a related-looking test failure in an IA-32 environment:

innodb.log_data_file_size '64k,innodb'   w1 [ fail ]

…

2023-06-29  3:40:49 0 [Note] InnoDB: Truncating system tablespaces from 1344 to 320 pages

2023-06-29 03:40:49 0xa96f9b40  InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-11.2.0/storage/innobase/fil/fil0fil.cc line 2988

InnoDB: Failing assertion: space->size == check.size

This might be fixed by acquiring fil_system.mutex earlier in the resizing code.

To better understand the maximum number of pages accessed and log records written by the shrinking operation, I came up with the following test:

rm -fr /dev/shm/data /dev/shm/rr && mkdir /dev/shm/data &&

./mariadbd --innodb-page-size=4k --innodb-log-file-size=4m --innodb-buffer-pool-size=4m --datadir /dev/shm/data --innodb-data-file-path=ibdata1:56G --bootstrap < /dev/null &&

_RR_TRACE_DIR=/dev/shm/rr rr record \

./mariadbd --innodb-page-size=4k --innodb-log-file-size=4m --innodb-buffer-pool-size=4m --datadir /dev/shm/data --innodb-data-file-path=ibdata1:12M:autoextend --bootstrap < /dev/null

rr replay /dev/shm/rr/latest-trace

There will be extent descriptor pages every innodb_page_size pages. There can be at most 2³² pages, and with the minimum innodb_page_size=4k we have the maximum number of extent descriptor pages: 2^(32-12)=2²⁰=1,048,576 pages. If these are the only pages that need to be accessed during the shrinking, then for any innodb_page_size, the worst case buffer pool consumption of the mini-transaction should be innodb_buffer_pool_size=4G, when shrinking from the maximum size (16 to 256 TiB depending on the page size) to the minimum size.

Based on the output of the above test, it looks like currently, no log writing whatsoever is taking place, and the shrinking may not be crash-safe at the moment?

2023-07-04 10:25:30 0 [Note] InnoDB: Initializing buffer pool, total size = 4.000MiB, chunk size = 1.000MiB

2023-07-04 10:25:30 0 [Note] InnoDB: Completed initialization of buffer pool

2023-07-04 10:25:30 0 [Note] InnoDB: End of log at LSN=47046

2023-07-04 10:25:30 0 [Note] InnoDB: 128 rollback segments are active.

2023-07-04 10:25:30 0 [Note] Truncating system tablespaces from 14680064 to 3072 pages

2023-07-04 10:25:36 0 [Note] System tablespace truncated successful

2023-07-04 10:25:36 0 [Note] InnoDB: Setting O_DIRECT on file ./ibtmp1 failed

2023-07-04 10:25:36 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...

2023-07-04 10:25:36 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.

2023-07-04 10:25:36 0 [Note] InnoDB: log sequence number 47046 (memory-mapped); transaction id 4

I think that we need some additional testing along these lines. For the record, the above truncation only accessed one page of the system tablespace:

Thread 1 hit Breakpoint 2, sql_print_information (format=format@entry=0x560400f53238 "Truncating system tablespaces from %d to %d pages\n") at /mariadb/11/sql/log.cc:9447

9447	{

(rr) continue

Continuing.

2023-07-04 10:25:30 0 [Note] Truncating system tablespaces from 14680064 to 3072 pages

Thread 1 hit Breakpoint 1, buf_page_get_low (page_id={m_id = 0}, zip_size=0, rw_latch=4, guess=0x0, mode=16, mtr=0x7ffdae517b80, err=0x7ffdae517b74) at /mariadb/11/storage/innobase/buf/buf0buf.cc:2270

2270	{

Thread 1 hit Breakpoint 1, buf_page_get_low (page_id={m_id = 0}, zip_size=0, rw_latch=4, guess=0x0, mode=16, mtr=0x7ffdae517b80, err=0x7ffdae517ae0) at /mariadb/11/storage/innobase/buf/buf0buf.cc:2270

2270	{

Thread 1 hit Breakpoint 1, buf_page_get_low (page_id={m_id = 0}, zip_size=0, rw_latch=4, guess=0x0, mode=16, mtr=0x7ffdae517b80, err=0x7ffdae517ae0) at /mariadb/11/storage/innobase/buf/buf0buf.cc:2270

2270	{

Thread 1 hit Breakpoint 1, buf_page_get_low (page_id={m_id = 0}, zip_size=0, rw_latch=2, guess=0x0, mode=10, mtr=0x7ffdae517b80, err=0x7ffdae517b78) at /mariadb/11/storage/innobase/buf/buf0buf.cc:2270

2270	{

Thread 1 hit Breakpoint 2, sql_print_information (format=format@entry=0x560400f60720 "System tablespace truncated successful") at /mariadb/11/sql/log.cc:9447

9447	{

Actually, the above test did write 12 bytes of log record payload (growing the LSN by 12+5 bytes), consisting of 2 log records:

In fact, I think that the log record size optimization for updating FSP_SIZE must be disabled, so that the file size change may be crash-safe. That is, the second log record should have been one byte longer:

Here is a script that extends the system tablespace with actual data, so that fil_system.sys_space->free_limit will grow from its minimum value. It works on a debug build of the server:

#!/bin/sh

set -eux

rm -fr /dev/shm/data /dev/shm/rr

mkdir /dev/shm/data

echo "create database test;

use test;

create table t(a int,b char(255) not null default'')

engine=innodb stats_persistent=0;

set unique_checks=0,foreign_key_checks=0;

set global innodb_limit_optimistic_insert_debug=2;

insert into t(a) select * from seq_1_to_1000000;

drop table t;"|

./mariadbd --innodb-page-size=4k --innodb-log-file-size=4g --innodb-buffer-pool-size=4g --datadir /dev/shm/data --innodb-fast-shutdown=0 --innodb-file-per-table=0 --bootstrap

_RR_TRACE_DIR=/dev/shm/rr rr record \

./mariadbd --innodb-page-size=4k --innodb-log-file-size=4g --innodb-buffer-pool-size=4m --datadir /dev/shm/data --bootstrap < /dev/null

I tested today’s revised version with that:

2023-07-07 16:02:49 0 [Note] Truncating system tablespaces from 101376 to 3072 pages

2023-07-07 16:02:49 0 [Note] System tablespace truncated successful

Now the buffer pool usage has been optimized, and the mini-transaction only comprises the tablespace latch and a latch on page 0 of the system tablespace. It will write 125 bytes of redo log for the shrinking, as follows:

If we are ever going to modify at most 2 extent pages, then we could keep copies of both those pages and emit the difference, like we do in btr_page_reorganize_low() for B-tree pages.

I will conduct another test where the system tablespace will be shrunk to something above 0x1000 or 0x2000 pages, so that two extent descriptor pages 0 and 0x1000 or 0x2000 will be modified.

Another test:

#!/bin/sh

set -eux

rm -fr /dev/shm/data /dev/shm/rr

mkdir /dev/shm/data

echo "create database test;

use test;

create table t(a int,b char(255) not null default'')

engine=innodb stats_persistent=0;

set unique_checks=0,foreign_key_checks=0;

set global innodb_limit_optimistic_insert_debug=2;

insert into t(a) select * from seq_1_to_100000;

create table u like t;

insert into u(a) select * from seq_1_to_1000000;

drop table u;"|

./mariadbd --innodb-page-size=4k --innodb-log-file-size=4g --innodb-buffer-pool-size=4g --datadir /dev/shm/data --innodb-fast-shutdown=0 --innodb-file-per-table=0 --bootstrap

_RR_TRACE_DIR=/dev/shm/rr rr record \

./mariadbd --innodb-page-size=4k --innodb-log-file-size=4g --innodb-buffer-pool-size=4m --datadir /dev/shm/data --bootstrap < /dev/null

2023-07-07 16:51:57 0 [Note] Truncating system tablespaces from 117760 to 10240 pages

2023-07-07 16:51:58 0 [Note] System tablespace truncated successful

When debugging the rr replay, set watch fil_system.sys_space.free_limit to see the mtr contents. In this case, we expect at least the pages 0 and 0x2000 to be modified. Somewhat unexpectedly, the m_memo also contains a latch for page 0x1000, which was not modified. Maybe if the allocation patterns would have been different, also this page would have been modified. Now there is 209 bytes of mtr.m_log produced.

I am interested in these, because I want to make sure that we will not run out of buffer pool when shrinking a very large system tablespace to very small size, or we do not exceed the maximum size of a mini-transaction, which I think is something like 4 megabytes. Let me decode the log:

This is not too bad, but I would like to improve this to have a known worst case. If I understood correctly, the worst case should be as follows:

I don’t think that there currently is any clear limit of the amount of redo log written. The easiest would be to keep copies of the 1 or 2 modified pages and optimize the log writes by comparing the pages. Why 2 pages? We assume that no extent descriptor page before the last surviving one can contain pointers to extent descriptor pages that would be discarded by shrinking the tablespace. I hope that thiru can add some debug assertions to prove this, or point to existing debug assertions for checking this.

One more thing that we discussed is something to avoid truncation and expansion on every startup. I would not want to add any start-up parameter for that. I think that a good rule would be that if the size of the system tablespace could be shrunk by more than 1/8 (12.5%) during startup, then it will be shrunk. Currently this is missing: we always try to shrink, no matter what. It could be useful to add some DBUG_EXECUTE_IF to enable the "always shrink" logic for testing.

Even though the extent descriptor page 0x1000 was not modified in my above test, thiru says that if the page allocation patterns had been different, this could be needed.

The actual worst-case limit of the number of pages latched (and possibly modified) is truncated_size_in_pages/innodb_page_size.

The worst case would be shrinking a innodb_page_size=4k tablespace so that the last possible descriptor page 0xfffff000 is removed. All 1,048,575 descriptor pages (every 0x1000 bytes) from 0x00000000 to 0xffffe000 would have to be accessed. If we held latches to all these pages at the same time, we would need 4GiB of buffer pool, which is more than 1,000 times the minimum innodb_buffer_pool_size. So, we should avoid doing that.

The typical amount of redo log written for one page should be much smaller than the page size, hopefully something like 50 bytes. If the maximum mini-transaction size is 4MiB, that should be enough for modifying some 80,000 extent descriptor pages.

I do not think that the worst case would involve modifying all 1,048,575 descriptor pages. There is only a limited number of list nodes in a descriptor page, let’s call it N. So, at most N pages can have pointers to the last descriptor page and may have to be modified. If we shrink the tablespace so that the M last descriptor pages ending at 0xfffff000 are modified, then we may have to modify N*M surviving descriptor pages. What is the maximum possible M*N?

Because there are 256 extent descriptor list elements (XDES_FLST_NODE) per extent descriptor page, shrinking the system tablespace by 1 extent will involve modifying at most 256 other extent descriptor pages. Shrinking by 2 extents may require modifying up to 2*256=512 extent descriptor pages.

Close to the worst case should be to shrink a tablespace with innodb_page_size=4k from the maximum number of extents (1,048,576) by something like 3,584 extents to 1,044,992 extents. If everything is laid out in the pessimal way, this would involve modifying 3584*256=917504 extent descriptor pages, requiring 3.5 GiB of buffer pool. If the maximum mini-transaction log size is 2MiB (before MDEV-14425, it might be even less), we would only have some 2.28 bytes per page available for logging the modifications to each page. That is clearly not enough, since the minimum size of the record header is 3 bytes (4 bytes for logging changes to any other extent descriptor page than the first one).

We clearly must implement some logic to determine if the shrinking operation is possible at all (if we have enough buffer pool and log space available). Only if the system tablespace will be shrunk little enough or to a small enough final size, it can succeed. We must first validate everything, before committing to shrink the tablespace.

Furthermore, to avoid any bugs or extra overhead at startup related to this, it would be good to make this feature opt-in, that is, only attempt shrinking the system tablespace if innodb_data_file_path contains an extra attribute :autoshrink to enable the attempt.

I think that implementing the following will address the biggest concerns. I verified that the first two of my list were done today:

• Write all 32 bits of FSP_SIZE at 0x2e.
• Make this opt-in by requiring an :autoshrink attribute to be added to innodb_data_file_path.
• Check that we will not run out of buffer pool.
• Check that we will not write too much redo log: the maximum mini-transaction size in the MDEV-14425 format should be 2MiB, including the 5 bytes of terminator and checksum.
• If either limit is exceeded, revert any changes to the buffer pool and discard the mini-transaction log.
• For test purposes, introduce some debug parameter or DBUG_EXECUTE_IF to make the maximum mini-transaction size smaller, say, 100 to 1000 bytes.
• Add debug code to invoke flst_validate() on all the extent descriptor lists in the system tablespace, before and after attempting truncation.

Optimizing the redo log size is optional and might be done later, if there is real customer demand for it. I would anticipate other demands for follow-up improvements first, such as these:

• Defragmenting or optimizing the system tables, to ensure that all data will be stored near the start of the system tablespace.
• Repairing a system tablespace to minimal size by copying the actual needed contents, exclusing things like SYS_TABLESPACES or SYS_DATAFILES tables that existed between MariaDB Server 10.0 and 10.5 until MDEV-22343 removed them.

Complete patch is in bb-11.2-MDEV-14795_1

origin/bb-11.2-MDEV-14795_1 6575d969aef4d06fb529f467f6c9d3ca60b0bb53 2023-07-17T10:43:15+05:30 (build type RelWithDebInfo)
performed well in RQG testing. No new problems.

Statistics from the remaining test results:
smallest system tablespace ever shrinked 1024 pages
biggest system tablespace ever shrinked 16640 pages
biggest shrink operation ~ 16000 pages truncated
smallest shrink operation ~ 832 pages truncated
I assume that the difference to the statistics from the previous run
seems caused by the different source tree.
A build with debug+asan+Optimization Og showed similar numbers like the
RelWithDebInfo build.

I posted some review comments, mostly about the formatting of messages, and code that is not covered by mtr tests. I think that most code should be covered if you create a debug-instrumented big_test that will use innodb_page_size=4k and extend the system tablespace to slightly more than 16 MiB (4096*4096 bytes). With innodb_limit_optimistic_insert_debug=2 (like my previously posted script does) it should be relatively fast. I think that the mandatory MSAN builder should run such a test.

Tomorrow, I will review the logic to "roll back" a mini-transaction when the size constraints are exceeded.

The general logic looks good to me. I think that this needs one more review round after the code coverage in the regression test suite has been improved.

The code coverage looks acceptable to me now. I made some minor suggestions on improving the out-of-memory detection. This should be OK to push after addressing the last round of my comments.

Together with MDEV-19229, this allows the storage space consumed by the InnoDB system tablespace to be reclaimed.

The defragmentation that is mentioned in the Description never shrunk any files; it only tried to move some B-tree pages within a data file in an attempt to improve performance on HDDs. That code was removed in MDEV-30545.

Does this change mean that there will be a delay in startup for particularly large ibdata1 files?

i.e. I've got a Galera cluster where ibdata1 has over time grown to > 300GB, and it definitely doesn't need to be this big with `file_per_table` having been set since database creation.

psumner, shrinking the system tablespace involves:

1. traversing every allocation bitmap page (pages 0, innodb_page_size, 2*innodb_page_size…) within the old size,
2. adjusting the bitmap pages within the shrunk size,
3. durably writing log for the change, and
4. shrinking the last system tablespace file by ftruncate()

When using the default innodb_page_size=16k, there will be an allocation bitmap page every 16384*16384 bytes (256 megabytes, or quarter gigabyte). Shrinking a 300GiB file would involve traversing 1,200 bitmap pages. In an extreme case, the file could be shrunk to 256 megabytes or less, that is, we would write log covering just the first page of the file. In such an extreme case, the time should be dominated by the file system operation. Truncating or deleting a heavily fragmented file can take considerable time. How much, depends on the type of storage and the file system, and the operating system kernel version.

By popular demand, I filed MDEV-32452 so that this cleanup can be executed on shutdown rather than startup.