Tablespace-Verwaltung und file-per-table in der Praxis

1. Was ein Tablespace in InnoDB wirklich ist

Ein Tablespace ist die physische Speicherstruktur, in der InnoDB Tabellendaten, Indizes und teilweise auch Undo-Informationen ablegt. Intern besteht jeder Tablespace aus Extents und Pages, standardmäßig 16 KB groß, die zu logischen Segmenten für Daten, Indizes und Rollback-Bereiche gruppiert werden. Wer InnoDB einsetzt, arbeitet immer mit mindestens einem Tablespace, auch wenn das im Alltag selten sichtbar wird, weil MySQL die Verwaltung standardmäßig automatisiert.

Historisch kannte InnoDB nur einen einzigen system tablespace, üblicherweise die Datei ibdata1 im Datenverzeichnis. Darin lagen früher alle Tabellendaten, der Data Dictionary, der Doublewrite-Buffer und die Undo-Logs gemeinsam. Dieses Modell funktioniert technisch, hat aber einen entscheidenden Nachteil: Ein Tablespace, der einmal wächst, gibt den belegten Speicherplatz im Dateisystem praktisch nie mehr zurück, selbst wenn Daten gelöscht werden. Genau dieses Problem hat MySQL mit innodb_file_per_table adressiert.

Seit MySQL 5.6 ist ein eigener Tablespace pro Tabelle der Standard, was die Verwaltung grundlegend verändert hat. Wer heute einen Server administriert, der noch mit einem monolithischen system tablespace läuft, hat entweder eine sehr alte Installation geerbt oder die Einstellung bewusst deaktiviert. Beide Fälle verdienen eine genaue Prüfung, denn die Konsequenzen für Speicherplatz und Wartung unterscheiden sich erheblich.

2. innodb_file_per_table: eine .ibd-Datei pro Tabelle

Mit aktiviertem innodb_file_per_table legt InnoDB für jede Tabelle eine eigene .ibd-Datei im entsprechenden Schema-Verzeichnis an. Diese Datei enthält die Tabellendaten und alle zugehörigen Indizes als eigenständigen Tablespace. Der system tablespace ibdata1 beschränkt sich dann im Wesentlichen auf den Doublewrite-Buffer, Change-Buffer-Daten und, je nach Version, Teile der Undo-Logs, sofern keine separaten Undo-Tablespaces konfiguriert sind.

Der praktische Vorteil eines eigenen Tablespace pro Tabelle zeigt sich sofort beim Löschen: DROP TABLE oder TRUNCATE TABLE gibt bei file-per-table den belegten Speicherplatz sofort ans Dateisystem zurück, weil einfach die .ibd-Datei entfernt wird. Bei einem monolithischen Tablespace bleibt der Platz dagegen innerhalb von ibdata1 als intern freier, aber vom Betriebssystem aus nicht sichtbarer Bereich bestehen. Das Prüfen der aktuellen Einstellung und das Anlegen einer Tabelle mit eigenem Tablespace sieht in der Praxis so aus:

-- Check current tablespace mode
SHOW VARIABLES LIKE 'innodb_file_per_table';
-- +-----------------------+-------+
-- | Variable_name         | Value |
-- +-----------------------+-------+
-- | innodb_file_per_table | ON    |
-- +-----------------------+-------+

-- New tables automatically get their own tablespace file
CREATE TABLE order_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Resulting file on disk: /var/lib/mysql/shop/order_log.ibd

Ein zweiter, oft übersehener Vorteil betrifft Backups: Werkzeuge wie Percona XtraBackup können mit file-per-table einzelne Tablespace-Dateien kopieren und mit ALTER TABLE ... IMPORT TABLESPACE gezielt einzelne Tabellen wiederherstellen. Bei einem einzigen großen system tablespace ist das nicht möglich, weil alle Tabellendaten physisch in derselben Datei liegen und sich nicht isoliert extrahieren lassen.

3. System-Tablespace vs. file-per-table im Detail

Der system tablespace bleibt auch bei aktivem innodb_file_per_table relevant, weil bestimmte interne Strukturen dort verbleiben. Dazu zählen der Doublewrite-Buffer, der beschädigte Pages nach einem Crash erkennbar macht, sowie, sofern keine gesonderten Undo-Tablespaces angelegt wurden, die Rollback-Segmente für Transaktionen. In modernen MySQL-8-Installationen sind Undo-Tablespaces standardmäßig bereits ausgelagert, was ibdata1 zusätzlich entlastet.

Ein wichtiger Unterschied zwischen den beiden Modellen betrifft die I/O-Charakteristik. Bei einem einzigen system tablespace konkurrieren alle Tabellen um denselben Datei-Deskriptor und dieselbe Datei auf dem Dateisystem, was bei sehr hohem Parallelbetrieb theoretisch zu Kontention führen kann. Mit file-per-table verteilt sich der I/O auf viele einzelne Dateien, was auf den meisten modernen Dateisystemen und mit SSD-Storage keinen relevanten Nachteil mehr darstellt, aber die Anzahl offener Dateideskriptoren erhöht, was in innodb_open_files berücksichtigt werden muss.

Für die Praxis heißt das: Ein einzelner Tablespace pro Tabelle ist heute die richtige Standardeinstellung für praktisch jede Installation, außer bei sehr speziellen Szenarien mit extrem vielen kleinen Tabellen, wo die Dateisystem-Overhead-Kosten pro .ibd-Datei ins Gewicht fallen könnten. Für einen typischen Magento- oder Shopsystem-Betrieb mit einigen hundert Tabellen ist dieser Fall praktisch irrelevant.

4. Speicherplatz zurückgewinnen mit OPTIMIZE TABLE

Auch mit aktiviertem file-per-table wächst die .ibd-Datei einer einzelnen Tabelle durch häufige UPDATE- und DELETE-Operationen mit der Zeit, ohne dass der intern freigewordene Speicherplatz automatisch an das Dateisystem zurückgegeben wird. InnoDB markiert freigewordene Pages intern als wiederverwendbar, gibt sie aber nicht an das Betriebssystem frei. Genau hier setzt OPTIMIZE TABLE an, das für InnoDB-Tabellen im Hintergrund ein ALTER TABLE ... ENGINE=InnoDB ausführt.

Dieser Vorgang erstellt die Tabelle als neuen, kompakten Tablespace neu, kopiert alle noch gültigen Zeilen hinein und tauscht anschließend die alte gegen die neue Datei aus. Der alte, fragmentierte Tablespace wird gelöscht, wodurch der Speicherplatz tatsächlich freigegeben wird. Der Nachteil: Für die Dauer der Kopie wird temporär zusätzlicher Speicherplatz in Höhe der Tabellengröße benötigt, und bei sehr großen Tabellen kann der Vorgang je nach Hardware Stunden dauern.

-- Check current and free space per table
SELECT table_name,
       ROUND(data_length / 1024 / 1024, 1)  AS data_mb,
       ROUND(index_length / 1024 / 1024, 1) AS index_mb,
       ROUND(data_free / 1024 / 1024, 1)    AS free_mb
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY data_free DESC
LIMIT 10;

-- Reclaim disk space for a heavily updated table
-- (rebuilds the tablespace, needs roughly table-size extra disk space)
OPTIMIZE TABLE shop.order_log;

-- Equivalent explicit rebuild, same effect for InnoDB
ALTER TABLE shop.order_log ENGINE=InnoDB;

In Produktionsumgebungen sollte OPTIMIZE TABLE nie unkoordiniert laufen. Ab MySQL 5.6 mit ALGORITHM=INPLACE für viele Online-DDL-Operationen bleibt die Tabelle zwar lesbar und meist schreibbar, aber die zusätzliche I/O- und CPU-Last kann Replikations-Lag verursachen und sollte in Wartungsfenstern mit reduziertem Traffic stattfinden. Bei sehr großen Tabellen ist ein Tool wie pt-online-schema-change von Percona oft die sicherere Wahl, weil es die Last granularer steuert und einen kontrollierten Abbruch erlaubt.

5. General Tablespaces: gemeinsame Dateien für mehrere Tabellen

Neben dem system tablespace und file-per-table kennt InnoDB seit MySQL 5.7 sogenannte General Tablespaces. Ein General Tablespace ist eine explizit angelegte, gemeinsame Datei, in der mehrere Tabellen zusammen abgelegt werden können, ähnlich dem alten system tablespace, aber unter voller administrativer Kontrolle. Das ist sinnvoll für Gruppen kleiner, thematisch zusammengehöriger Tabellen, bei denen der Dateisystem-Overhead vieler einzelner .ibd-Dateien vermieden werden soll.

Ein General Tablespace wird explizit erstellt und Tabellen werden bei der Definition diesem Tablespace zugeordnet. Wichtig zu wissen: Tabellen in einem General Tablespace unterstützen kein innodb_file_per_table-Verhalten mehr, das heißt TRUNCATE TABLE gibt den Speicherplatz nicht automatisch zurück, weil die Datei ja von mehreren Tabellen gemeinsam genutzt wird. Diesen Trade-off muss man bewusst eingehen.

-- Create a general tablespace as a shared file
CREATE TABLESPACE ts_reporting
  ADD DATAFILE 'ts_reporting.ibd'
  ENGINE=InnoDB;

-- Assign small, related tables to the shared tablespace
CREATE TABLE report_cache (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_key VARCHAR(64) NOT NULL,
  payload JSON NOT NULL,
  generated_at DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB TABLESPACE=ts_reporting;

CREATE TABLE report_meta (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_key VARCHAR(64) NOT NULL,
  label VARCHAR(128) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB TABLESPACE=ts_reporting;

Für die meisten Anwendungsfälle, insbesondere im Shop- und Magento-Umfeld, ist file-per-table dennoch die pragmatischere Wahl, weil Backup-Tools, Partitionierung und operative Werkzeuge am besten damit zusammenarbeiten. General Tablespaces lohnen sich vor allem, wenn eine sehr große Anzahl kleiner Tabellen, etwa bei Multi-Tenant-Systemen mit hunderten Schemas, die Anzahl offener Dateideskriptoren spürbar treibt.

6. Tablespace-Größen überwachen

Um einen Tablespace nicht unkontrolliert wachsen zu lassen, gehört Monitoring zur Routine jeder MySQL-Instanz. Die View information_schema.INNODB_TABLESPACES zeigt alle registrierten Tablespaces inklusive Dateiname, Zustand und Typ. Ergänzend liefert information_schema.TABLES Größenangaben je Tabelle, was für die Priorisierung von OPTIMIZE TABLE-Läufen entscheidend ist, weil man so gezielt die Tabellen mit dem größten Verhältnis von freiem zu belegtem Speicher findet.

Für den system tablespace selbst empfiehlt sich ein regelmäßiger Blick auf die tatsächliche Dateigröße von ibdata1 im Dateisystem, weil dieser Tablespace nicht automatisch schrumpft, egal wie viel intern frei ist. Wächst ibdata1 über Jahre unkontrolliert, bleibt oft nur ein vollständiger Dump-and-Reload der Instanz, um wieder eine kompakte Ausgangsgröße zu erreichen. Genau deshalb ist die frühzeitige Aktivierung von file-per-table so wichtig, denn sie verhindert dieses Problem von vornherein.

7. Migration von system tablespace zu file-per-table

Wird innodb_file_per_table auf einer Instanz aktiviert, die vorher ohne diese Option lief, betrifft das zunächst nur neu erstellte Tabellen. Bereits existierende Tabellen bleiben im system tablespace, bis sie explizit migriert werden. Der zuverlässigste Weg ist ein ALTER TABLE ... ENGINE=InnoDB pro Tabelle, das die Tabelle in einen frischen, eigenen Tablespace umschreibt, oder alternativ ein vollständiger Export und Reimport per mysqldump.

Für viele Tabellen in Produktion ist ein sequenzieller Migrationslauf während eines Wartungsfensters sinnvoll, wobei die größten Tabellen zuerst und mit ausreichend Zeitpuffer behandelt werden sollten, weil sie den system tablespace am meisten belasten. Nach Abschluss aller Migrationen bleibt ibdata1 in seiner aktuellen Größe bestehen, denn auch hier gilt: Der system tablespace selbst schrumpft nicht rückwirkend, nur weil er intern leerer wird.

#!/usr/bin/env bash
# migrate-to-file-per-table.sh: rebuild all InnoDB tables in a schema
set -euo pipefail

SCHEMA="shop"

tables=$(mysql -N -B -e \
  "SELECT table_name FROM information_schema.tables \
   WHERE table_schema='${SCHEMA}' AND engine='InnoDB'")

for t in $tables; do
  echo "Rebuilding tablespace for ${SCHEMA}.${t}"
  mysql -e "ALTER TABLE \`${SCHEMA}\`.\`${t}\` ENGINE=InnoDB;"
done

echo "Done. ibdata1 itself will not shrink; only new tablespaces do."

Wer den system tablespace anschließend komplett auf seine minimale Größe zurücksetzen will, kommt um einen vollständigen Dump, das Löschen aller InnoDB-Dateien inklusive ibdata1 und einen sauberen Reimport nicht herum. Das ist ein invasiver Eingriff, der eine vollständige Downtime erfordert und deshalb gut geplant sein will, aber langfristig die einzige Methode, um Jahre an angesammeltem, ungenutztem Speicherplatz im system tablespace tatsächlich zurückzugewinnen.

8. Praxisfallstricke bei Backup und Wartung

Ein häufiger Fehler in der Praxis: Teams aktivieren innodb_file_per_table, vergessen aber, dass Backup-Skripte, die bisher nur ibdata1 gesichert haben, jetzt auch alle .ibd-Dateien einschließen müssen. Physische Backup-Tools wie Percona XtraBackup handhaben das automatisch korrekt, aber selbstgebaute Kopier-Skripte, die nur bestimmte Dateien im Datenverzeichnis erfassen, können nach der Umstellung unvollständige Backups erzeugen, was erst beim Restore auffällt, wenn es zu spät ist.

Ein weiterer Fallstrick betrifft innodb_open_files: Mit vielen tausend Tabellen und aktivem file-per-table steigt die Anzahl gleichzeitig geöffneter Dateideskriptoren deutlich. Ist dieser Wert zu niedrig konfiguriert, öffnet und schließt MySQL Tablespace-Dateien ständig neu, was messbar Performance kostet. Für Instanzen mit vielen Tabellen sollte dieser Parameter zusammen mit dem Betriebssystem-Limit für offene Dateien (ulimit -n) explizit geprüft und angehoben werden.

9. Tablespace-Typen im direkten Vergleich

Die Wahl zwischen system tablespace, file-per-table und General Tablespace hängt vom konkreten Anwendungsfall ab. Die folgende Übersicht fasst die wichtigsten Unterschiede zusammen, die in der täglichen Administration relevant sind.

Für nahezu jede neue Installation ist file-per-table die richtige Grundeinstellung. General Tablespaces bleiben ein Werkzeug für Spezialfälle, während der klassische, monolithische system tablespace heute nur noch als Altlast in gewachsenen Installationen relevant ist, die man mittelfristig konsequent migrieren sollte.

10. Zusammenfassung

Die Tablespace-Verwaltung in InnoDB ist kein exotisches Detail, sondern entscheidet direkt darüber, ob Speicherplatz kontrolliert bleibt oder über Jahre unkontrolliert wächst. Der monolithische system tablespace ibdata1 gibt einmal belegten Platz praktisch nie wieder frei. innodb_file_per_table löst dieses Problem, indem jede Tabelle einen eigenen, unabhängigen Tablespace bekommt, der bei DROP TABLE vollständig verschwindet.

Auch mit file-per-table bleibt Fragmentierung ein Thema, das mit regelmäßigem, gut geplantem OPTIMIZE TABLE adressiert werden muss. General Tablespaces sind ein Werkzeug für Spezialfälle mit sehr vielen kleinen Tabellen. Für die meisten Installationen gilt: file-per-table aktivieren, Größenwachstum überwachen und bestehende Alt-Tabellen im system tablespace gezielt migrieren, statt das Problem auf unbestimmte Zeit zu verschieben.

11. FAQ: Tablespace-Verwaltung und file-per-table