Beitrag wurde zuletzt am aktualisiert

Piwik Datenbank verkleinern

Piwik Datenbank Archivierung

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.

Die Piwik Datenbank

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.

Piwik Berichterstellung per Cronjob
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.

Datenbank-Speicherplatz veringert
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.

9 Kommentare zu "Piwik Datenbank verkleinern"

  1. Ich habe gerade mal versucht, deine Tipps nachvollzuziehen. Bei deinem SQL-Aufruf zum Säubern der Log-Einträge erhalte ich allerdings eine Fehlermeldung:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‚= DATE_SUB(CURDATE(), INTERVAL 30 DAY)‘ at line 4

    Scheinbar gibt es einen Fehler in der Datumsabfrage. Auf den ersten Blick sieht aber alles richtig aus.

    Hast du vielleicht eine Idee, wo der Fehler liegen könnte?

  2. Im 2. Versuch hat es geklappt :)

    Der SQL-Aufruf hat zwar über 10 Minuten gedauert, hat die Datenmenge aber auch um über 90 % reduziert.

    Nicht schlecht, Herr Specht :-D

  3. Kristofer Golderer | 11. August 2011 um 19:27 | Antworten

    Vielen Dank für die Infos. Ich persönlich freue mich auch immer, wenn zu einem Thema Videos verfügbar sind. Vielleicht könnte man die ja noch entsprechend verlinken?

  4. Vielen Dank für diese tolle Anleitung! Ich habe Piwik seit gestern am Laufen und bin gerade auf der Suche nach einem Script bzw. nach einer Möglichkeit mit dem ich meinen Cronjob testen kann. Bei dir wurde ich fündig, aber wie kann ich den Cronjob eigentlich testen?

    Benötige ich da einen Shell-Zugang von meinem ISP Alfahosting (den ich glaube ich nicht habe), oder wie geht das? Benötige ich eine spezielle Software, oder wie und wo muss ich die Datei „archive.sh“ aufrufen?

    Ich bin leider nicht fit genug in diesen Dingen, da ich mit Cronjobs noch nie gearbeitet habe.

    Über ein Feedback würde ich mich freuen.

    LG, Joachim

    • Da musst du deinen Hoster „Alfahosting“ zu befragen. Ich denke, dass nicht überall die Möglichkeit bestehen wird, Shell-Scripts auszuführen – auch wenn hier eine Ausführung lediglich mit den Rechten des Webservers notwendig sind. Wenn es keine Besucherzahlen über 1.000 pro Tag sind, dann wird auch die Archivierung im Browser genügen.

      • Vielen Dank für die schnelle Antwort.

        Ich werde mal meinen ISP anschreiben und ihn danach fragen. Aber ich denke eher, dass ich keinen Shell-Zugang haben werde.

        Und auf 1000+ Besucher komme ich auch nicht, von daher hat es keine arg große Priorität.

  5. Hallo Daniel, ich habe nach Deinen Angaben einige archive_blob und archive_numeric Tabellen als dump gesichert und gelöscht. Piwik zeigt mir allerdings trotzdem die Liste der Suchbegriffe, Besucheranzahl u.ä., nur die Seitenaufrufe nicht mehr. Merkwürdigerweise stellt Piwik mir die Seitenaufrufe aber auch nicht mehr dar, wenn ich die gesicherten Tabellen wieder einspiele. Woran kann das denn liegen?

    • @Ruth: Hast du die archive.sh schon durchlaufen lassen?

      Ansonsten beherrscht Piwik seit einiger Zeit das Aufräumen von Haus aus. Schau mal in den Einstellungen nach und lass‘ Piwik alle x Tage eine Bereinigung durchführen.

      Beim Verfassen dieses Beitrags zum Thema Piwik Datenbank verkleinern gab es die automatische Bereinigung noch nicht.

Hinterlasse einen Kommentar

E-Mail Adresse wird nicht veröffentlicht.