Ein Feature, das mich im neuen MariaDB 11.4 LTS Release wirklich begeistert hat, ist das Verkleinern bzw. Schrumpfen des System-Tablespaces (ibdata1). Auf dieses Feature habe ich seit ca. 2006 sehnsüchtig gewartet und nun ist es mit MariaDB 11.4 endlich gekommen.
Eigentlich gibt es dieses Feature schon seit dem MariaDB 11.2 IR (Juni 2023).
Leider ist die Ankündigung dieses Features etwas zu kurz gekommen. In den MariaDB Release Notes heisst es lapidar:
The InnoDB system tablespace is now shrunk by reclaiming unused space at startup (MDEV-14795)
Die Gründe, warum dieses Datei ins Unermessliche wachsen kann, sind eigentlich schon lange bekannt und die Massnahmen dagegen sind auch klar (siehe Literatur). Nur sehen wir immer wieder MariaDB-Anwender draussen im Feld, die das Problem nicht oder zu spät auf dem Schirm hatten und nun mit einer viel zu grossen ibdata1-Datei da stehen...
Wie kann das Problem provoziert werden?
Das Problem kann provoziert werden, indem man eine Tabelle im System-Tablespace anlegt:
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;
und diese dann mit Daten befüllt:
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
;
...
Während die Tabelle gefüllt wird, kann man auf dem Dateisystem beobachten, wie die Datei ibdata1 anschwillt:
$ 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
Wenn die Datei ibdata1 gross genug ist, kann man die Tabelle vom System-Tablespace in einen dedizierten Tablespace verschieben:
SQL> SET global innodb_file_per_table = off;
SQL> ALTER TABLE test.test FORCE;
Query OK, 0 rows affected (33.764 sec)
Und man sieht, wie die neue Datei aufgebaut wird:
$ 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
Wir haben jetzt also einmal die Daten aber doppelte so viel Platz verbraucht.
Und wie kann man den System-Tablespace wieder verkleinern?
Diese Information ist leider etwas versteck und muss aus der Dokumentation und den MariaDB Jira Issues (siehe Literatur) zusammengesucht werden:
SQL> SET GLOBAL innodb_fast_shutdown=0;
SQL> SHUTDOWN;
Beim Herunterfahren sieht man die entsprechenden Einträge im 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
Und wenn man sich die Datei ibdata1 danach auf Platte anschaut, ist sie wieder so klein wie zu Beginn des Experiments:
$ ll ibdata1* -h
-rw-rw---- 1 mysql mysql 12M Jun 12 14:11 ibdata1
