Speicherverbrauch der Magento-Datenbank um mehr als 80% reduzieren

Ungenutzte Tracking-Informationen können über 80% des Speicherplatzes ausmachen, den die Magento-Datenbank belegt.

Üblicherweise wird eine andere Tracking-Software eingesetzt (z. B. Google Analytics), sodass die Tracking-Daten von Magento ungenutzt bleiben und nur die Größe der Datenbank und folglich aller Backups aufblähen.

Speicherverbrauch analysieren

Magento speichert Tracking-Daten in den Tabellen log_url, log_url_info, log_visitor, log_visitor_info. Bei einem gut besuchten Shop belegen diese vier Tabellen stets die ersten Plätze bei der Sortierung nach Datenlänge:

Tabellenstatus im MySQL Administrator

Mit dem passenden SHOW-Befehl lassen sich Informationen über alle Log-Tabellen einschließlich Tracking-Tabellen anzeigen:

SHOW TABLE STATUS LIKE 'log%';

Eine SELECT-Abfrage auf INFORMATION_SCHEMA bietet mehr Möglichkeiten für die Aufbereitung und Sortierung der Ausgabetabelle. Mit dem folgenden Befehl lässt sich die oben gezeigte grafische Ansicht des MySQL Administrators nachbilden:

SELECT
   TABLE_NAME AS 'Tabelenname',
   ENGINE AS 'Engine',
   TABLE_ROWS AS 'Zeilen',
   DATA_LENGTH / 1048576 AS 'Datenlänge',
   INDEX_LENGTH / 1048576 AS 'Indexlänge',
   UPDATE_TIME AS 'Aktualisierungszeit'
FROM
   information_schema.TABLES t
WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME LIKE 'log%'
ORDER BY DATA_LENGTH DESC;

Mit den nachfolgenden Befehlen lässt sich der prozentuale Speicherverbrauch berechnen. Der erste Befehl speichert dabei Spaltensummen in Variablen, damit die nachfolgenden zwei Befehle auf verlangsamende Unterabfragen verzichten können:

-- 1. Gesamten Speicherverbrauch der aktuellen Datenbank in Variablen speichern und anzeigen
SELECT
  (@data_length := SUM(DATA_LENGTH)) / 1048576 AS data_length,
  (@index_length := SUM(INDEX_LENGTH)) / 1048576 AS index_length,
  (@total_length := SUM(INDEX_LENGTH + DATA_LENGTH)) / 1048576 AS total_length
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

-- 2.1. Prozentualen Speicherverbrauch der log-Tabellen
SELECT TABLE_NAME,
  ROUND(DATA_LENGTH / @data_length * 100, 2) AS DATA_LENGTH_RATIO,
  ROUND(INDEX_LENGTH / @index_length * 100, 2) AS INDEX_LENGTH_RATIO,
  ROUND((INDEX_LENGTH + DATA_LENGTH) / @total_length * 100, 2) AS TOTAL_LENGTH_RATIO
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME LIKE 'log%'
ORDER BY TOTAL_LENGTH_RATIO DESC;

-- 2.2. Prozentualen Speicherverbrauch der log-Tabellen zusammengefasst
SELECT
  ROUND(SUM(DATA_LENGTH / @data_length * 100), 2) AS DATA_LENGTH_RATIO,
  ROUND(SUM(INDEX_LENGTH / @index_length * 100), 2) AS INDEX_LENGTH_RATIO,
  ROUND(SUM((INDEX_LENGTH + DATA_LENGTH) / @total_length * 100), 2) AS TOTAL_LENGTH_RATIO
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME LIKE 'log%'
ORDER BY DATA_LENGTH_RATIO DESC;

Beispielausgabe für Abfrage 1 (Anzeige in Megabyte):

+-------------+--------------+--------------+
| data_length | index_length | total_length |
+-------------+--------------+--------------+
|    277.4169 |      54.4785 |     331.8954 |
+-------------+--------------+--------------+
1 row in set (3.48 sec)

Vor dem Ausführen der Abfragen 2.1. und 2.2. muss die Abfrage 1 ausgeführt werden.
Beispielausgabe für Abfrage 2.2 (Anzeige in Prozent):

+-------------------+--------------------+--------------------+
| DATA_LENGTH_RATIO | INDEX_LENGTH_RATIO | TOTAL_LENGTH_RATIO |
+-------------------+--------------------+--------------------+
|             89.27 |              41.64 |              81.45 |
+-------------------+--------------------+--------------------+
1 row in set (0.50 sec)

In diesem Beispiel belegt die Datenbank von Magento 332 MB. Davon nehmen die Daten der Log-Tabellen beträchtliche 81,45% ein!

Tracking in Magento deaktivieren

Eine Deaktivierung des Tracking ist in Magento nicht vorgesehen. Mit einem Wartungsskript könnten die entsprechenden Tabellen regelmäßig geleert werden. Durch den Wechsel der Storage Engine auf BLACKHOLE geht es noch eleganter:

ALTER TABLE log_url ENGINE = BLACKHOLE;
ALTER TABLE log_url_info ENGINE = BLACKHOLE;
ALTER TABLE log_visitor ENGINE = BLACKHOLE;
ALTER TABLE log_visitor_info ENGINE = BLACKHOLE;

Im Code von Magento sind keine Änderungen nötig. Die Anwendungsschicht sendet Tracking-Daten weiterhin an die Datenbank, wo sie von dem „schwarzen Loch“ verschluckt werden. Die Tabellen bleiben leer.

3 Gedanken zu „Speicherverbrauch der Magento-Datenbank um mehr als 80% reduzieren“

  1. Danke für den interessanten Post, wieder was gelernt, wozu die blackhole Engine genutzt werden kann 🙂
    Nur als Hinweis: das product-compare Feature von Magento für Gäste funktioniert nur im Zusammenhang mit dem visitor tracking. Auch die Besucher-Segmentierung, die in der Version 1.12 zu der Kunden-Segmentierung in der Enterprise Edition hinzugekommen ist, funktioniert mit dieser Modifikation nicht mehr.

    1. Vielen Dank für diesen Hinweis!

      Von Herrn Kopp gibt es übrigens ein sehr gutes Buch „Magento – Das Handbuch für Entwickler“:
      Magento - Das Handbuch für Entwickler
      ISBN-10: 389721928X
      ISBN-13: 978-3897219281

  2. Vielen Dank für den Tipp. Meine DB-Backups sind jetzt (als unkomprimierter sql-Skript) statt 1,2 GB nur noch 300 MB groß!
    Ich habe für die vier Tabellen die Engine auf Blackhole gesetzt und der Vergleich funktioniert auch für Gäste völlig einwandfrei (ausprobiert auf Magento 1.7.0.2). Was ist mit Besuchersegmentierung gemeint?

Schreibe einen Kommentar zu Vinai Kopp Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.