Piwik und LOAD DATA INFILE

Wer die freie Webanalyse-Software Piwik einsetzt und eine gut besuchte Website betreibt, kennt vielleicht das Problem hoher Serverlast durch die regelmäßigen Archivierungen, die für die Berichtserstellung nötig sind.

Piwik nutzt für die Speicherung der Daten MySQL und führt die Archivierung für Berichte in der Weboberfläche bei Bedarf automatisch aus – z.B. dann, wenn die Daten der anzuzeigenden Berichte älter als 10 Minuten sind.

Solange die Zahl der monatlichen Zugriffe nur ein paar tausend sind, verursacht die Archivierung auch keine große Verzögerungen. Bei größeren Datenmengen empfiehlt die Dokumentation von Piwik aber, die automatische Archivierung abzuschalten und diesen Prozess in einen cron-Job zu verlagern, der stündlich ausgeführt wird.

Die Verarbeitung in einem cron-Job sorgt zumindest dafür, dass die Weboberfläche von Piwik nicht durch Archivierungen verlangsamt wird. Aber auch dieser Prozess verursacht bei steigenden Zugriffszahlen regelmäßig über mehrere Minuten hinweg eine starke Last des Servers und kann auch die Ladezeiten von Websites spürbar verlängern.

Eine Lösung bietet Piwik für dieses Problem auch an: Die Verwendung von LOAD DATA INFILE in MySQL. Das reduziert die Last erheblich, da MySQL in diesem Fall nicht mehr tausende von INSERT-Statements ausführen muss, sondern die Datensätze direkt aus einer Datei übernommen werden, die Piwik erzeugt. Ob diese Möglichkeit besteht, kann man in der Systemprüfung von Piwik sehen.

LOAD DATA INFILE kann ein Sicherheitsrisiko darstellen! Bitte überprüfen Sie sorgfältig, dass die folgenden Änderungen in Ihrem System keine Sicherheitsprobleme verursachen! Es gibt Distributionen, in denen MySQL grundsätzlich keine Unterstützung LOAD DATA INFILE bietet – in diesem Fall müsste man zuerst eine angepasste Version von MySQL selber bauen und installieren, was ich nicht empfehle, wenn man damit nicht vertraut ist.

Aus Sicherheitsgründen ist es normalerweise nicht möglich, LOAD DATA INFILE zu verwenden. Damit MySQL direkt auf Dateien zugreifen kann, die Piwik für den Import schreibt, sind mehrere Punkte zu ändern – hier am Beispiel von Ubuntu Linux 12.04:

Der MySQL-Benutzer, der für Piwik verwendet wird, benötigt das globale FILE-Zugriffsrecht, was man mit folgendem SQL-Statement erreichen kann (Benutzername und Host sind entsprechend anzupassen):

GRANT FILE ON *.* TO 'piwik_user'@'localhost'

In MySQL muss die Einstellung local-infile aktiviert sein. Dazu ergänzt man in der Datei /etc/mysql/my.cnf in den Abschnitten [mysqld] und [mysql] jeweils eine Zeile:

[mysqld]
local-infile = 1

[mysql]
local-infile = 1

Nach dieser Änderung muss MySQL neu gestartet werden.

Des Weiteren muss der User, mit dem MySQL ausgeführt wird, zur Gruppe hinzugefügt werden, die Eigentümerin des Verzeichnisses der Piwik-Website ist, damit er Zugriffsrechte darauf hat. Ist der User mysql und die Gruppe www-data, kann man das mit folgendem Shell-Befehl erreichen:

usermod -a -G www-data mysql

Hinweis zur Sicherheit: www-data ist normalerweise die Gruppe, in der alle Websites auf einem Server laufen. Mit dieser Änderung hat der MySQL-User auch Zugriff auf Daten anderer Websites, nicht nur Piwik. Um die Sicherheit zu verbessern, empfehle ich die Ausführung von PHP in einem eigenen Prozess mit fcgi und die Verwendung einer Gruppe, die ausschließlich für die Piwik-Website verwendet wird und nichts Anderes.

Danach sollte man bei der Systemprüfung von Piwik auch die entsprechende Angabe mit einem grünen Haken für „LOAD DATA INFILE“ sehen.

Auch die Serverlast (load) sollte danach deutlich sinken. In meinem Fall war sie vorher regelmäßig über 1,5 – danach selten über 0,5. In diesem Zusammenhang empfehle ich auch Tools wie Munin, um die Entwicklung über längere Zeiträume zu beobachten.

Auf dem folgenden Munin-Diagramm sieht man sehr deutlich den Punkt, ab dem durch die Aktivierung von LOAD DATA INFILE die Serverlast reduziert wurde:

Serverlast durch Piwik-Archivierung nach Aktivierung

Weitere Möglichkeiten

Wenn die oben genannten Schritte nicht ausreichen, kommen eventuell noch weitere Optionen in Betracht:

MySQL-Einstellung secure_file_priv

In der Konfiguration von MySQL muss evtl. noch die Variable secure_file_priv gelöscht (oder auf das korrekte Verzeichnis gesetzt werden, in dem Piwik die Import-Dateien ablegt).

[mysqld]
local-infile = 1
secure_file_priv = ""

[mysql]
local-infile = 1

Piwik-Datenbankadapter

Piwik kann verschiedene Datenbankadapter verwenden. Normalerweise kommt PDO_MYSQL zum Einsatz. Dieser Adapter verursacht manchmal Probleme, wenn Piwik abhängig von der PHP-Konfiguration das SQL-Statement erstellt. Es kann helfen, auf MYSQLI umzustellen, was in der Datei config/config.ini wie folgt vorgenommen wird:

[database]
adapter = "MYSQLI"

4 Gedanken zu „Piwik und LOAD DATA INFILE“

  1. ich

    Keine gute Idee!

    Die Berechtigung FILE gibt Ihnen die Erlaubnis, Dateien auf dem Serverhost mit den LOAD DATA INFILE- und SELECT … INTO OUTFILE-Anweisungen zu lesen bzw. zu schreiben. Ein Benutzer mit der Berechtigung FILE kann jede Datei auf dem Server lesen, die entweder von allen oder vom MySQL-Server gelesen werden kann. (Daraus folgt, dass der Benutzer jede Datei in jedem Datenbankverzeichnis lesen kann, da der Server auf all diese Dateien zugreifen darf.) Die Berechtigung FILE erlaubt dem Benutzer auch die Erstellung neuer Dateien in allen Verzeichnissen, auf die der MySQL-Server Schreibzugriff hat.

    1. Arno Welzel

      Völlig richtig – Danke für die deutliche Warnung. Deswegen auch mein deutlicher Hinweis im Artikel, dass diese Änderung ein Sicherheitsrisiko darstellt. Keinesfalls sollten solche Änderungen vorgenommen werden, wenn man sich nicht genau der möglichen Konsequenzen bewusst ist! Dennoch muss dazu gesagt werden, dass ein Angreifer es erst schaffen muss, den verwendeten MySQL-User von Piwik in Erfahrung zu bringen und eine Lücke in Piwik zu finden, mit der sich solche Aktionen von aussen einschleusen lassen (dass MySQL nicht öffentlich zugänglich ist, setze ich als minimale Sicherheitsmaßnahme voraus).

  2. Bei mir klappt Piwik da nicht und kommt immer wieder meldung wie folgt.

    LOAD DATA INFILE
    Die Benutzung von LOAD DATA INFILE erhöht die Geschwindigkeit des Piwik Archivierungsprozesses erheblich. Um dies für Piwik verfügbar zu machen setzen Sie bitte eine neuere PHP & MySQL Software ein und stellen Sie sicher, dass der gewählte Datenbankbenutzer das FILE Recht besitzt.
    Falls Ihr Piwik Server Websites mit erhöhtem Verkehrsaufkommen (z.B.: > 100.000 Seiten pro Monat) verarbeiten muss empfehlen wir dieses Problem zu beheben.
    Fehler: LOAD DATA INFILE failed… Error was:
    Try #1: LOAD DATA INFILE : SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user ‚piwik_d’@’%‘ (using password: YES)[28000]

    Habt Ihr noch einen anderen Tip

    1. Arno Welzel

      Ein „Access denied“ deutet darauf hin, dass dem MySQL-Benutzer für Piwik noch das Recht für LOAD DATA INFILE oder das verwendete Verzeichnis für die Import-Datei fehlt. Ansonsten kann ich nur auf das Forum von Piwik verweisen, wo dieser Fehler ebenfalls angesprochen wird.

Kommentar hinterlassen

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