Piwik Datenbank verkleinern

In der derzeitigen Version 1.2 von Piwik ist noch kein automatisches Bereinigen der durchaus richtig groß werdenden Datenbanktabellen log_link_visit_action und log_visit integriert. Laut Entwickler-Bereich ist dies aber in Planung. Je nach Besucherzustrom auf den zu trackenden Webseiten können diese – wie in diesem Beispiel – mehrere Millionen Einträge erzeugen und somit die MySQL-Datenbank mehrere Gigabyte groß werden lassen. Das ist natürlich auch dann problematisch, wenn die Festplattenkapazität zwar an den Terrabyte-Bereich kratzt und ein paar Gigabyte Datenbank-Speicher nichts ausmachen, aber spätestens ein endlos dauernder Datenbank-Dump bei der Datensicherung oder der Traffic zum Backup-System bringen auch größere Server-Umgebungen an ihre Leistungsgrenzen.

Dieser Artikel soll als Anleitung dienen, um die Datenbankgröße in einem vernünftigen Rahmen zu halten. Im Piwik-Forum sowie in den Piwik-FAQ ist diese Vorgehensweise ebenso beschrieben. Die erste Anregung mich mit diesem Thema intensiver auseinander zu setzen, bekam ich auf dem Blog vom pcdoc. Das Vor-sich-herschieben hatte somit ein Ende!

1. Datenbankgröße
2. Automatische Archivierung einrichten
3. SQL zum Bereinigen der Logeinträge
4. Löschen alter Archiv-Tabellen
5. Speicherfresser access_logs

Datenbankgröße auf 6 Gigabyte angewachsen

Die Gesamtgröße der Datenbank ist bei meiner Installation auf über 6 Gigabyte angewachsen. Allein die Tabelle log_link_visit_action hat über 22 Millionen Einträge und das Backup der Piwik-Datenbank nimmt somit jede Nacht eine ganze Menge Ressourcen in Anspruch. Probleme bei den Piwik-Laufzeiten konnte ich auf einem Hetzner EQ6 Server allerdings noch nicht feststellen. Seit über 2 Jahren werden rund 20.000 Besucher am Tag erfasst und nun ist es an der Zeit ein wenig aufzuräumen.

Cronjob zum Archivieren der Berichte einrichten

In den allgemeinen Piwik-Einstellungen wird darauf hingewiesen, dass größere Installationen die Berichterstellung nicht im Browser sondern per Cronjob ablaufen lassen sollten. Ab wann die Bezeichnung „groß“ zutrifft wird an dieser Stelle nicht verraten. Allerdings macht die Einrichtung eines Cronjobs keine Arbeit und somit spricht aus meiner Sicht nichts gegen dessen standardmäßige Nutzung.

Die Piwik-Installation bringt ein unter misc/cron liegendes Shell-Script namens archive.sh mit. Dieses lässt sich automatisch per Cronjob ausführen (z.B. jede Stunde oder einmal am Tag). Wer bereits mein Rechte-Script für Piwik nutzt, hat in diesem Script bereits das benötigte Ausführungsrecht für den Webserver-User (bei mir www-data) für diese Datei gegeben. Ansonsten könnt ihr dies natürlich auch per Hand setzen, sicherlich bei den meisten 755.

Ein Cronjob lässt sich auf einem root-Server unkompliziert auf der Kommandozeile einrichten. Der hier aufgeführte wird alle 6 Stunden gestartet.

~# crontab -e -u www-data

* */6 * * * /euer/pfad/zu/piwik/misc/cron/archive.sh > /dev/null

Sicherlich lässt sich dies auch über die Oberflächen der verschiedenen ISP regeln, da hilft fast immer ein Blick in die Anleitung, in die FAQ oder ein Anruf beim Support. Ein manuelles Ausführen der Archivierung bzw. der Test, ob alles reibungslos funktioniert, lässt sich auf der Linux-Shell mit dem folgenden Aufruf realisieren.

~# su www-data -c "sh /euer/pfad/zu/piwik/misc/cron/archive.sh"

Wenn alles funktioniert, dann erfolgen einige XML-Ausgaben und die abschließende Meldung: Finished Scheduled tasks.

SQL-Aufruf zum Säubern der Log-Einträge

In den Piwk-FAQ #42 wird auf das Thema Piwik-Logs bereinigen eingegangen. Von dieser Seite stammt auch der hier aufgeführte MySQL-Aufruf, der z.B. im phpMyAdmin ausgeführt werden kann und alle Logs, die älter als 30 Tage sind, löscht.

DELETE piwik_log_visit, piwik_log_link_visit_action
FROM piwik_log_visit INNER JOIN piwik_log_link_visit_action
WHERE piwik_log_visit.idvisit = piwik_log_link_visit_action.idvisit
AND visit_first_action_time <= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
OPTIMIZE TABLE piwik_log_visit, piwik_log_link_visit_action;

Oder direkt auf der Linux-Shell, was ich aufgrund der Datenmenge bei meiner Installation angewandt habe:

~# mysql -udatenbankuser -p
Enter password:*****
mysql> use piwik;
mysql> DELETE piwik_log_visit, piwik_log_link_visit_action
    -> FROM piwik_log_visit INNER JOIN piwik_log_link_visit_action
    -> WHERE piwik_log_visit.idvisit = piwik_log_link_visit_action.idvisit
    -> AND visit_first_action_time <= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
mysql> OPTIMIZE TABLE piwik_log_visit, piwik_log_link_visit_action;

Das Ergebnis sind zwei immens verkleinerte Tabellen.

Die bereits verarbeiteten und archivierten Berichte werden hierbei nicht gelöscht. Es ist aber darauf zu achten, dass die Archivierung auch wirklich durchgeführt wurde.

Löschen alter Archiv-Tabellen

Wer die Piwik-Datenbank noch mehr verkleinern möchte, kann alte Archiv-Tabellen löschen. Die Tabellen heißen archive_blob_YYYY_MM und archive_numeric_YYYY_MM. Hier sind die Berichte aller Webseiten, in denen Piwik eingebunden ist, gespeichert. Wenn man die Daten nur für die vergangenen 12 Monate benötigt, spricht nichts dagegen ältere Tabellen einfach zu löschen. Ein vorheriges Wegsichern bietet natürlich die Möglichkeit, irgendwann einmal diese zurück zu spielen. Ebenso könnte man diese in eine lokale Piwik-Installation kopieren und hätte so aus seiner Entwicklungsumgebung o.ä. heraus immer Zugriff auch auf ältere Datenbestände.

Speicherfresser access_logs

Schnell übersehen könnte man bei einem eigenen Hosting von Piwik, dass der Apache eine access- und error-Logdatei anlegt und diese mit jedem Zugriff auf den Piwik-JavaScript-Code fleißig befüllt. Dieses Logging habe ich nur die ersten Tage nach der Inbetriebnahme von Piwik genutzt oder wenn nach einem Update Probleme auftraten. Ansonsten macht das keinen Sinn. Ebenso würde man hiermit natürlich nicht mehr den hier beschriebenen Vorgehen beim Thema Datenschutz entsprechen, da standardmäßig die IP-Adressen in diesen Dateien gespeichert werden.