Wer sich ein wenig mit MySQL beschäftigt hat, weiß, dass es nicht nur in den Features, sondern auch im Handling einige Unterschiede zwischen den DB-Engines MyISAM und InnoDB gibt.
Die Transaktionssicherheit von InnoDB erkauft man sich u.a. durch eine geringere Transparenz, wo welche Daten liegen und auch wie viel Speicherplatz von den einzelnen Tabellen und Datenbanken belegt werden, da die InnoDB im Gegensatz zur MyISAM ihre Daten nicht in separaten Dateien ablegt, sondern alle Daten aller Datenbanken in nur einer Datei vereint.. Hat man viele Datenbanken mit vielen Tabellen, erschwert dies die Fehlersuche und Optimierung ungemein.
Ich habe hier ein paar MySQL-Queries zusammen gestellt, welche die gewünschten Informationen aus den Tabellen der MySQL-eigenen Datenbank „information_schema“ lesen.
Wer sich ein wenig mit MySQL beschäftigt hat, weiß, dass es nicht nur in den Features, sondern auch im Handling einige Unterschiede zwischen den DB-Engines MyISAM und InnoDB gibt.
Die Transaktionssicherheit von InnoDB erkauft man sich u.a. durch eine geringere Transparenz, wo welche Daten liegen und auch wie viel Speicherplatz von den einzelnen Tabellen und Datenbanken belegt werden, da die InnoDB im Gegensatz zur MyISAM ihre Daten nicht in separaten Dateien ablegt, sondern alle Daten aller Datenbanken in nur einer Datei vereint.. Hat man viele Datenbanken mit vielen Tabellen, erschwert dies die Fehlersuche und Optimierung ungemein.
PHPMyAdmin zeigt zwar die Größe der Tabellen an, aber man muss dazu jede einzelne Datenbank anklicken und verliert so den Überblick.
Ich habe hier ein paar MySQL-Queries zusammen gestellt, welche die gewünschten Informationen aus den Tabellen der MySQL-eigenen Datenbank „information_schema“ lesen.
Voraussetzung für alle folgenden Queries ist eine SQL-Verbindung zum Server mit einem Account, der für das Lesen der Informationen aus der Datenbank „information_schema“ berechtigt ist – also z.B. „root“;
Warnung: Diese Anweisungen sammeln Daten über sämtliche Inhalte eines Servers, bei großen Datenmengen und langsamen Servern können diese eine Laufzeit von mehreren Minuten haben und den Betrieb des Servers stark beeinflussen!
Gesamt-Verbrauch aller DBs
Möchte man die Größe aller Datenbanken auf einem Datenbank-Server erfahren, so kann dazu der folgende SQL-Befehl verwendet werden:
SELECT concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size
FROM information_schema.TABLES ;
Hierbei wird die Speicherplatzbelegung, die in Bytes vorliegt gleich in MB umgerechnet, für Fälle, in denen das nicht sinnvoll ist, lässt man natürlich die Funktionen round und concat weg und teilt auch nicht durch ( 1024*1024 ).
Diese Abfrage addiert den Speicherplatz-Verbrauch der Daten und ihrer Indices, wer diese Größen getrennt braucht, kann obige Abfrage leicht modifizieren.
Diese Abfrage lässt sich natürlich weiter verfeinern. Da man die Speicherplatzbelegung der MyISAM-Tabellen leicht anhand der Größen der einzelnen Dateien ablesen kann, gehen wir ab hier nur noch auf die InnoDB ein.
Gesamt-Verbrauch aller InnoDB-Tabellen
Die Daten der InnoDB-Engine liegen, wie gesagt, in nur einer Datei. Häufig ist diese Datei mit der Option „autoextend“ konfiguriert, so dass die Datei automatisch vergrößert wird, sobald neue Daten in die Datenbank geschrieben werden. Jedoch schrumpft sie nicht, wenn Daten gelöscht werden. Der freigewordene Speicherplatz innerhalb der Datei, kann wieder von Daten in der InnoDB belegt werden, es dauert also, bis die InnoDB-Datei wieder voll ist, und sie mittels autoextend vergrößert werden muss, aber auf der Festplatte des Servers wird dieser Speicherplatz nie wieder freigegeben. So kann es sein, dass die Datei auf der Festplatte deutlich größer ist, als die Nutzdaten darin.
Um zu erfahren, wie viele Nutzdaten sich also derzeit wirklich in der InnoDB befinden, ist folgende Abfrage nützlich:
SELECT concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size
FROM information_schema.TABLES
WHERE ENGINE='InnoDB' ;
InnoDB-Speicherverbrauch pro Datenbank
Um zu erfahren, in welcher Datenbank der Speicher verbraucht wird, kann man sich hiermit leicht einen Überblick verschaffen:
SELECT table_schema 'database', concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size
FROM information_schema.TABLES
WHERE ENGINE='InnoDB'
GROUP BY table_schema
ORDER BY sum( data_length + index_length );
In diesem Fall wird das Resultat auch noch nach der Größe sortiert.
Hinweis: Nicht vergessen, in diesen Größen-Angaben werden alle Daten, die nicht mit der Engine InnoDB verwaltet werden ignoriert. Will man alle Daten sehen muss die Anweisung WHERE ENGINE='InnoDB' weggelassen werden.
InnoDB-Speicherverbrauch pro Tabelle
Um zu erfahren, in welcher Tabelle einer bestimmten Datenbank der Speicher verbraucht wird, kann hiermit die Datenbank näher inspizieren, dabei muss in der Abfrage natürlich der Name der betreffenden Datenbank anstelle des Platzhalters [DATABASE_NAME] eingetragen werden:
SELECT table_name 'table', concat(round((data_length+index_length)/(1024*1024),2),'M') size
FROM information_schema.TABLES
WHERE ENGINE='InnoDB'
AND table_schema = '[DATABASE_NAME]'
ORDER BY ( data_length + index_length );
Große InnoDB-Tabellen auf einem Server finden
Möchte man einen ganzen Server nach Tabellen einer bestimmten (Mindest-)Größe durchsuchen, ohne den Umweg über die Größe der Datenbanken zu nehmen, kann man dies auch direkt in einer Anweisung formulieren. Da viele Tabellen sehr klein sind, ist es selten sinnvoll sich die Größe aller Tabellen eines Servers anzuzeigen, daher dient die letzte hier vorgestellte Abfrage dazu, alle Tabellen eines Servers anzuzeigen, die in der InnoDB mindestens 100MB belegen. Die Angabe der Mindestgröße geschieht hierbei in Byte, daher steht in der WHERE-Anweisung des SQL die Zahl 104857600 (=100*1024*1024):
SELECT table_schema 'database', table_name 'table', concat(round((data_length+index_length)/(1024*1024),2),'M') size
FROM information_schema.TABLES
WHERE ENGINE='InnoDB'
AND data_length + index_length >104857600
ORDER BY ( data_length + index_length );
