Ja, der Umgang mit aufgeblähten Datenbanken kann wirklich lästig sein, besonders wenn sie mit der Zeit immer größer werden. Falls Ihre MySQL- oder MariaDB-Instanz träge reagiert oder Ihr Speicherplatz schneller schwindet als erwartet, sollten Sie sich mit Tabellenkomprimierung und Defragmentierung auseinandersetzen. Diese Methoden können überraschend viel Speicherplatz freigeben und die Leistung steigern – allerdings ist es immer ein Balanceakt, vorher Daten zu sichern, die Konfigurationen zu überprüfen und darauf zu hoffen, dass niemand ohne Sicherheitsvorkehrungen alles über den Haufen wirft. Hier sind einige Tipps, die sich in der Praxis bewährt haben – nach einigem Ausprobieren.
Wie man Speicherplatz- und Leistungsprobleme bei MySQL/MariaDB behebt
Kampf gegen die ibdata1- und ib_log-Dateien – Teilen Sie Ihre InnoDB-Daten auf
Wenn Ihre riesigen ibdata1- und ib_log-Dateien viel Speicherplatz belegen, liegt das wahrscheinlich daran, dass alle Ihre InnoDB-Tabellen ihre Daten in einer einzigen, monolithischen Datei speichern. Das ist zwar das Standardverhalten, aber ineffizient und macht die Bereinigung extrem schwierig. Bei manchen Konfigurationen verkleinern sich diese Dateien nicht von selbst, was ärgerlich ist. Der Trick besteht darin, MySQL anzuweisen, jede Tabelle separat zu speichern – so lässt sich der Speicherplatz später deutlich einfacher freigeben.
So gehen Sie vor:
- Sichern Sie zuerst alle Ihre Datenbanken, insbesondere wenn sie wichtig sind – mysqldump ist Ihr Freund:
# mysqldump -u [username] -p [database_name] > backup.sql - Stoppen Sie den MySQL- oder MariaDB-Server:
# sudo systemctl stop mysqloder# sudo systemctl stop mariadb - Bearbeiten Sie Ihre Konfigurationsdatei – diese befindet sich üblicherweise unter /etc/mysql/my.cnf oder /etc/mysql/mariadb.conf.d/50-server.cnf. Suchen Sie die folgende Zeile unter [mysqld] oder fügen Sie sie hinzu:
innodb_file_per_table=1 - Löschen Sie die großen Dateien ibdata1 und ib_log. MySQL erledigt das natürlich nicht automatisch; Sie müssen sie manuell löschen (stellen Sie sicher, dass Sie vorher eine Sicherungskopie haben!).
# sudo rm /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1 - Starten Sie den Server erneut:
# sudo systemctl start mysql - Stellen Sie gegebenenfalls Ihre Backups wieder her und überprüfen Sie dann, ob neue Tabellen in eigenen .ibd- Dateien gespeichert werden – wenn Sie Glück haben, werden die alten Dateien nicht mehr größer.
Diese Methode ist zwar etwas radikal, aber sie hilft wirklich, die ibdata-Dateien im Griff zu behalten. Auf manchen Systemen muss die Datenbank möglicherweise gesichert, neu erstellt und wieder geladen werden, damit der Wechsel ordnungsgemäß funktioniert. Deshalb sind Backups so wichtig.
Tabellenkomprimierung in InnoDB – Speicherplatz sparen mit ROW_FORMAT=COMPRESSED
Bei textlastigen Tabellen oder BLOB-Daten kann die Komprimierung viel Speicherplatz freigeben. Der Haken? Die CPU-Auslastung kann dadurch stark ansteigen. Daher ist diese Methode nur sinnvoll, wenn die CPU Ihres Servers nicht bereits voll ausgelastet ist oder der Speicherplatz ein dringendes Problem darstellt.
Wechseln Sie in Ihre MySQL-Shell:
# mysql -u root -p
Wechseln Sie zu der Datenbank, die Sie bereinigen möchten:
# use your_database_name;
Schauen Sie sich an, welche Tische viel Platz beanspruchen:
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in MB" FROM information_schema. TABLES WHERE table_schema = "your_database_name" ORDER BY (data_length + index_length) DESC;Hier sehen Sie, welche Tabellen am größten sind. Wählen Sie nun eine oder mehrere Tabellen zum Komprimieren aus. Wenn beispielsweise eine Tabelle b_crm_event_relationsmit großem Inhalt angezeigt wird, führen Sie Folgendes aus:
ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;
Danach sollte die Tabellengröße sinken – ich habe schon erlebt, dass sie von etwa 26 MB auf 11 MB gefallen ist, was wirklich super ist. Aber Achtung: Die Komprimierung kann Lese- und Schreibvorgänge vorübergehend verlangsamen.Übertreiben Sie es also nicht mit der Komprimierung, wenn gerade viel los ist.
MyISAM-Tabellen – Komprimieren und Speichern
Wenn Sie mit älteren MyISAM-Tabellen arbeiten, können Sie diese mit dem Tool myisampack komprimieren. Dies entspricht eher dem Komprimieren der Tabellendatei als einer Komprimierung innerhalb der Datenbank.
Führen Sie einfach diesen Befehl in Ihrem Terminal aus – ersetzen Sie den Pfad durch den Speicherort Ihrer Tabelle:
# myisampack -b /var/lib/mysql/test/modx_session
Dadurch wird eine komprimierte Version Ihrer. MYD-Datei erstellt, etwa so modx_session. MYI:.Sie können die Größe vor und nach der Komprimierung mit dem Befehl `du -sh` überprüfen.
Führen Sie unbedingt `myisampack` auf der. MYD-Datei aus und anschließend `myisamchk -rq` auf der Tabelle. Es mag etwas ungewöhnlich klingen, aber diese Methode hilft tatsächlich, sehr kleine Tabellen zu optimieren, wenn der Speicherplatz knapp ist.
Regelmäßige Wartung: Tabellen defragmentieren und optimieren
Im Laufe der Zeit können Tabellen fragmentiert werden und ungenutzten Speicherplatz enthalten. Durch Ausführen von OPTIMIZE TABLE lässt sich dieser Speicherplatz oft freigeben und der Datenzugriff beschleunigen.
Melden Sie sich in Ihrer MySQL-Shell an und wählen Sie Ihre Datenbank aus:
# mysql -u root -p
Wählen Sie Ihre Datenbank aus:
# USE your_database;
Und lauf:
OPTIMIZE TABLE b_crm_timeline_bind, b_disk_deleted_log_v2, b_disk_object_path;
Dadurch werden die Tabellen neu erstellt und ihre Größe bei Fragmentierung reduziert. Die Ausgabe sollte die erfolgreiche Durchführung bestätigen, wobei data_free auf Null sinkt. In einigen Fällen kann die Meldung „Tabelle unterstützt keine Optimierung“ erscheinen; in diesem Fall wird eine Neuerstellung mit anschließender Analyse durchgeführt. In beiden Fällen wird die Tabellengröße dadurch deutlich reduziert und die Ausführungsgeschwindigkeit erhöht.
Wenn Sie eine schnelle Komplettlösung wollen – dann los:
# mysqlcheck -o --all-databases -u root -p
Es ist eine gute Angewohnheit, das ab und zu zu machen. Denk aber daran, vorher alles zu sichern, denn, nun ja, Murphys Gesetz und so.
Zusammenfassung
- Sichern Sie Ihre Datenbanken, bevor Sie an Konfigurationen oder der Komprimierung herumexperimentieren.
- Bearbeiten Sie die Datei /etc/mysql/my.cnf, um dies zu aktivieren
innodb_file_per_table=1. - Nach dem Stoppen des Servers sollten die alten ibdata1- und ib_log-Dateien gelöscht werden – durch das Neuerstellen neuer Dateien wird das Wachstum eingedämmt.
- Verwenden Sie diese
ALTER TABLE...ROW_FORMAT=COMPRESSEDFunktion sparsam bei großen Text- oder BLOB-Tabellen, um Speicherplatz zu sparen. - Komprimieren Sie die MyISAM-Tabellen mit myisampack und führen Sie myisamchk -rq aus.
- Führen Sie regelmäßig OPTIMIZE TABLE oder mysqlcheck -o aus, um die Tabelle zu defragmentieren.
Zusammenfassung
Das Ausprobieren dieser Tipps kann die Leistung Ihrer Datenbank verbessern und wertvollen Speicherplatz freigeben – insbesondere bei älteren Tabellen oder wachsenden Projekten. Zugegeben, es erfordert etwas Aufwand, aber die gewonnene Geschwindigkeit und der eingesparte Speicherplatz lohnen sich. Denken Sie daran, regelmäßig Backups zu erstellen, die Konfigurationen zu überprüfen und den Cronjob nicht zu vergessen, um alles im Blick zu behalten. Hoffentlich spart dies jemandem ein paar Stunden, denn eine reibungslose Datenbank sorgt für zufriedenere Nutzer.