One feature that really excited me in the new MariaDB 11.4 LTS release is the shrinking of the system tablespace (ibdata1). I have been eagerly waiting for this feature since around 2006 and now it has finally arrived with MariaDB 11.4.
Actually, this feature has been available since MariaDB 11.2 IR (June 2023).
Unfortunately, the announcement of this feature came a little too short. In the MariaDB release notes it says succinctly:
The InnoDB system tablespace is now shrunk by reclaiming unused space at startup (MDEV-14795)
The reasons why this file can grow immeasurably have actually been known for a long time and the measures against it are also clear (see literature). But again and again we see MariaDB users out in the field who didn't realise the problem or realised it too late and are now left with an ibdata1 file that is far too large...
How can the problem be provoked?
The problem can be provoked by creating a table in the system tablespace:
SQL> SET global innodb_file_per_table = off;
SQL> CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` varchar(128) DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
and then filling it with data:
SQL> INSERT INTO test
SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW()
;
SQL> INSERT INTO test
SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW()
FROM test LIMIT 1000000
;
...
While the table is being filled, you can observe how the file ibdata1 swells on the file system:
$ while [ 1 ] ; do ll -h ibdata1 ; sleep 5 ; done
-rw-rw---- 1 mysql mysql 12M Jun 2 13:57 ibdata1
-rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1
-rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1
-rw-rw---- 1 mysql mysql 140M Jun 12 13:58 ibdata1
-rw-rw---- 1 mysql mysql 204M Jun 12 13:58 ibdata1
-rw-rw---- 1 mysql mysql 268M Jun 12 13:58 ibdata1
-rw-rw---- 1 mysql mysql 332M Jun 12 13:59 ibdata1
-rw-rw---- 1 mysql mysql 396M Jun 12 13:59 ibdata1
-rw-rw---- 1 mysql mysql 460M Jun 12 13:59 ibdata1
-rw-rw---- 1 mysql mysql 524M Jun 12 13:59 ibdata1
-rw-rw---- 1 mysql mysql 588M Jun 12 13:59 ibdata1
-rw-rw---- 1 mysql mysql 652M Jun 12 13:59 ibdata1
-rw-rw---- 1 mysql mysql 716M Jun 12 13:59 ibdata1
-rw-rw---- 1 mysql mysql 780M Jun 12 14:00 ibdata1
-rw-rw---- 1 mysql mysql 844M Jun 12 14:00 ibdata1
-rw-rw---- 1 mysql mysql 908M Jun 12 14:00 ibdata1
-rw-rw---- 1 mysql mysql 972M Jun 12 14:00 ibdata1
-rw-rw---- 1 mysql mysql 1.1G Jun 12 14:00 ibdata1
-rw-rw---- 1 mysql mysql 1.2G Jun 12 14:00 ibdata1
-rw-rw---- 1 mysql mysql 1.3G Jun 12 14:00 ibdata1
-rw-rw---- 1 mysql mysql 1.4G Jun 12 14:00 ibdata1
If the ibdata1 file is large enough, you can move the table from the system tablespace to a dedicated tablespace:
SQL> SET global innodb_file_per_table = off;
SQL> ALTER TABLE test.test FORCE;
Query OK, 0 rows affected (33.764 sec)
And you can see how the new file is created:
$ ll -h ibdata1 test/*
-rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1
-rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/#sql-alter-dca30-12.frm
-rw-rw---- 1 mysql mysql 696M Jun 12 14:01 test/#sql-alter-dca30-12.ibd
-rw-rw---- 1 mysql mysql 1.1K Jun 12 13:56 test/test.frm
-rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1
-rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/test.frm
-rw-rw---- 1 mysql mysql 1.4G Jun 12 14:02 test/test.ibd
So now we have once the data but twice as much space used.
And how can you make the system tablespace smaller again?
Unfortunately, this information is somewhat hidden and has to be gathered from the documentation and the MariaDB Jira issues (see literature):
SQL> SET GLOBAL innodb_fast_shutdown=0;
SQL> SHUTDOWN;
When shutting down, you can see the corresponding entries in the MariaDB error log:
[Note] bin/mariadbd (initiated by: root[root] @ localhost []): Normal shutdown
[Note] InnoDB: FTS optimize thread exiting.
[Note] InnoDB: Truncating system tablespace from 90880 to 768 pages
[Note] InnoDB: System tablespace truncated successfully
[Note] InnoDB: Starting shutdown...
[Note] InnoDB: Dumping buffer pool(s) to /home/mysql/database/mariadb-114/data/ib_buffer_pool
[Note] InnoDB: Restricted to 2016 pages due to innodb_buf_pool_dump_pct=25
[Note] InnoDB: Buffer pool(s) dump completed at 240612 14:11:11
[Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
[Note] InnoDB: Shutdown completed; log sequence number 4011132308; transaction id 139
[Note] bin/mariadbd: Shutdown complete
And if you look at the file ibdata1 on disc afterwards, it is as small as it was at the beginning of the experiment:
$ ll ibdata1* -h
-rw-rw---- 1 mysql mysql 12M Jun 12 14:11 ibdata1
