Danilo Endesfelder 10. März 2015

Logfiles mit PostgreSQL aggregieren und auswerten

Ziel ist es, Logdateien zu importieren und zu aggregieren. © depositphotos.com / olly18

Wer beruflich oder privat Kontakt mit Webservern hat, kommt irgendwann in die Situation, die Logfiles seiner Zugriffe untersuchen zu wollen oder zu müssen. Hier gibt es allerhand bereits vorgefertigte Möglichkeiten beispielsweise mit awstats, Piwik oder Webalizer. Diese sind zum einen nur mit gewissem Aufwand einzurichten und meist nur über eine grafische Weboberfläche bedienbar. Auch sind die Möglichkeiten der Auswertung – wenn auch umfangreich – vorgegeben und können nur begrenzt an persönliche Vorlieben angepasst werden.

Es gibt alternativ die Möglichkeit, die Logausgaben der Webserver direkt in eine Datenbank schreiben zu lassen. Dies hat jedoch den entscheidenden Nachteil, das kurze ad-hoc-Prüfungen der Logfiles wie ein einfaches „tail -f“ nur sehr umständlich möglich sind. Davon abgesehen muss hier sichergestellt sein, dass die Datenbank immer verfügbar ist, da ansonsten die Ausgaben verloren gehen. Ideal wäre, die Möglichkeit zu haben, Logdateien ohne großen Aufwand in die Datenbank importieren und auswerten bzw. aggregieren zu können und dennoch die Debuggingmöglichkeiten wie die Suche nach einzelnen Requests zu erhalten.

In PostgreSQL ist es glücklicherweise möglich, eben dies als Funktionen direkt innerhalb der Datenbank zu implementieren. Dies hat unter anderem den entscheidenden Vorteil, dass bei einem Backup der Datenbank die Funktionen selbst gesichert und jederzeit wieder importiert werden können. Es stehen verschiedene Skriptsprachen innerhalb von PostgreSQL zu Verfügung. Die bekanntesten Beispiele dürften hier Python, Perl, Java und Googles V8 sein. Diese werden als „procedural languages“ bezeichnet und müssen als Extension innerhalb einer Datenbank einmalig aktiviert werden. Es setzt natürlich voraus, das diese auf dem System zur Verfügung stehen.

Allgemeines

Das Ziel ist es, die Logdateien mindestens eines Webservers in PostgreSQL unter Zuhilfenahme von PL/Python zu importieren sowie die Daten nach bestimmten Kriterien zu aggregieren. Sämtliche Prozesse laufen dabei innerhalb der Datenbank ohne externe Skripte/Programme ab. Der Import der Logdateien selbst wird dabei ebenfalls durch eine PL/Python-Funktion durchgeführt.

Das Grundsystem

Genutzt wird ein CentOS 6 Server mit aktuellem PostgreSQL 9.4 ohne spezielle Anpassungen. Die Installation der Datenbank wurde wie in YUM Installation [1] durchgeführt. Zusätzlich wurde das Paket postgresql94-plpython installiert, welches die erforderliche PL/Python-Extension zur Verfügung stellt. Die Extension wird in der Templatedatenbank template1 aktiviert. Dies hat zur Folge, dass alle neu erstellten Datenbanken die Extension bereits geladen haben. Die Aktivierung erfolgte dabei mittels folgender Befehle in der psql-cli:

# CREATE EXTENSION plpythonu;

Dies kann natürlich nur mit Superuserrechten durchgeführt werden. PL/Python steht aktuell nur in der plpythonu-Version zur Verfügung. Das „u“ am Ende einer PL bedeutet, dass diese als „untrusted language“ geladen wird. Die entscheidendsten Unterschiede sind, dass diese Zugriff auf das Dateisystem haben und Funktionen in dieser Sprache nur von Superusern erstellt und geändert, jedoch von jedem Nutzer mit Zugriff ausgeführt werden dürfen. Andere Sprachen wie PL/Perl stehen mittlerweile auch als "trusted languages" zur Verfügung. Damit ist die Vorbereitung der Datenbank auch bereits abgeschlossen und es können die benötigten Funktionen erstellt werden.

Import der Daten

In welches Format sollen die Daten importiert werden?

Bevor man die Daten importieren kann, sollte man sich natürlich Gedanken machen, wie diese am Ende in der Datenbank vorliegen sollen. In PostgreSQL gibt es dafür mehrere Möglichkeiten. Für welche man sich entscheidet ist abhängig von den persönlichen Vorlieben und vor allem auch der weiteren Verarbeitung, da jede Ihre Vor- und Nachteile hat.

Json(b)
Die Speicherung als json(b) ist sehr flexibel. So ist nicht festgelegt, welche auszuwertenden Teile ein Logeintrag enthalten muss, was vor allem bei der Handhabung verschiedener Logformate praktisch ist. Der Unterschied zwischen json und jsonb ist, grob umrissen, dass im json-Format der Eingabestring wie übergeben gespeichert wird. Bei jsonb wird dieser in einem, laut Aussage der Dokumentation, „zerteilten binären String“ gespeichert. Dieser benötigt etwas mehr Zeit beim Import, jedoch ist der Zugriff auf Datensätze dieses Typs sehr viel schneller, da hier nicht erst der Datensatz durch die Datenbank geparsed werden muss, sondern direkt auf die einzelnen Schlüssel zugegriffen werden kann. Allerdings werden im jsonb-Format doppelte Schlüsselwerte nicht übernommen, sondern nur der zuletzt im String vorkommende. So wird zum Beispiel aus „{'a':2, 'a':4}“ nach dem Einfügen in die Spalte „{'a':4}“. Auch wird die Reihenfolge der einzelnen Schlüssel innerhalb des Strings nicht beibehalten. Dies ist jedoch für die meisten Anwendungsfälle kein Problem, da auf diese per Namen zugegriffen wird und auch die Schlüssel nur einfach vertreten sind. Natürlich sind die Daten auch indexierbar. Hier ist jedoch darauf zu achten, dass einerseits der Datensatz als ganzes, zum anderen aber auch einzelne Schlüsselwerte separat indexiert werden können. Details dazu sind der offiziellen Dokumentation zu entnehmen.

Schwierig gestaltet sich hier die Änderung der Daten nach dem Import. So ist es nicht möglich, einen Schlüsselwert innerhalb eines Datensatzes mit einem einfache Update-Statement zu ändern. Hierzu muss der gesamte Datensatz ausgelesen, angepasst und erneut in die Datenbank geschrieben werden. Eine Änderung der Daten ist also aus praktischer Sicht nur über eine zusätzliche Funktion möglich.

Speicherung als Datensatz in einer Tabelle
Die Speicherung der Daten in einer einzelnen Tabelle, welche die Teile des getrennten Logeintrages als Spalten enthält, ist eine der einfachsten Möglichkeiten, die Daten in die Datenbank zu importieren. So können die einzelnen Spalten bzw. deren Datentypen genau an entsprechende spätere Auswertungswünsche angepasst werden. Die zugreifende IP kann als Datentyp „inet“ hinterlegt werden, um eine spätere Auswertung der zugreifenden IPs nach Netzen vorzunehmen, sowie die Zugriffszeit als „timestamp“ um die zeitliche Auswertung zu vereinfachen. Auch sind die Daten ohne „Spezialkenntnisse“ über den Umgang mit anderen Datentypen weiter verwertbar.

Um Speicherplatz zu sparen und den Anforderungen der Normalisierung gerecht zu werden, ist man geneigt, mehrfach vorkommende Texteinträge wie eventuell Domainnamen, Referer, URLs etc. in zusätzliche Tabellen auszulagern und nur mit einem Fremdschlüssel in der Hauptdatenbank zu verknüpfen. Dies würde auch den Speicherverbrauch senken, da z. B. in der Haupttabelle nur der Integerwert des Keys der Domaintabelle hinterlegt werden muss, und nicht der gesamte Text der Domain.

Hier sei gewarnt, dass dies den Import der Daten teils extrem verzögern kann, da beim Import der einzelnen Datensätze stets geprüft werden muss, ob der verknüpfte Datensatz bereits importiert wurde oder noch importiert werden muss. Zusätzlich muss natürlich noch der Primärschlüssel ausgelesen werden. Das bedeutet z. B. pro ausgelagerter Spalte mindestens eine Extra-Abfrage pro zu importierendem Datensatz. Dies kann sich bei größeren Logdateien von mehreren Millionen Einträgen pro Datei und 4 ausgelagerten Spalten so äußern, dass sich der Import von Minuten auf Stunden verzögert.

Der größte Nachteil an dieser Option ist, dass im Nachhinein Anpassungen der Datenstruktur einigen Aufwand erfordern: Spalten müssen entfernt bzw. neu hinzugefügt werden - zum Beispiel auch dann, wenn sich das Logformat ändert oder mehrere Webserver mit unterschiedlichen Logformaten ihre Daten bereitstellen.

Speicherung im hstore-Format
hstore ist ein PostgreSQL-eigener Schlüssel-Wert Datentyp. Er ähnelt am ehesten dem Hash-Datentyp in Perl, wird hier allerdings nur der Vollständigkeit halber genannt. Einer der größten Nachteile gegenüber dem json-Format ist, dass hstore keine verschachtelten Daten erlaubt sowie nur mit Strings arbeiten kann. So ist es zum Beispiel nicht möglich, hinter einem Schlüssel eine weitere Liste als Wert zu hinterlegen. Allerdings ist dieser Datentyp ebenfalls sehr performant und auch indexierbar. Lediglich für den vorliegenden Zweck ist er nur bedingt geeignet. Weitere Informationen dazu gibt es in der offiziellen Dokumentation.

Implementation der Auswertung von Log-Dateien in der Datenbank

Nach einigen Tests hat sich die Speicherung in Form klassischer Spalten als passendes Format erwiesen, sofern das Format der zu importierenden Daten gleich bleibt. Zum einen ist der Import hier schneller, zum anderen der Speicherverbrauch geringer. Davon abgesehen hat man die Möglichkeit, ein weiteres - in diesem Zusammenhang sehr praktisches - Feature von PostgreSQL zu nutzen: die Vererbung von Tabellen oder auch Inheritance. Dabei geht es um die Möglichkeit, aus einer Elterntabelle Kindtabellen zu generieren. Diese erben die Struktur der Elterntabelle und werden auch bei Abfragen auf die Elterntabelle berücksichtigt. Im aktuellen Szenario wird eine Tabelle „logdata“ wie folgt angelegt:

CREATE TABLE logdata (
    pk_data_id      BIGSERIAL PRIMARY KEY,
    domain          TEXT ,
    remip           cidr,
    remtime         TIMESTAMP,
    method          TEXT,
    request         TEXT,
    status          SMALLINT,
    size            BIGINT,
    referer         TEXT,
    agent           TEXT
    );

Im Laufe des Importskriptes wird für jede Domain eine eigene Kindtabelle mit dem Namen der Domain und einer Verknüpfung auf die logdata-Tabelle erstellt. Dies kann wie folgt aussehen:

CREATE TABLE IF NOT EXISTS foobar_de (
    CHECK(domain = 'foobar.de')
) 
INHERITS(logdata);

Es empfiehlt sich aus Gründen der Sicherheit und auch Konsistenz, einen Check zu integrieren, damit auch wirklich nur die Daten der gewünschten Domain in die Tabelle geschrieben werden.

Mit dieser Konstruktion hat man nun die Möglichkeit, eine Statistik über den erzeugten Traffic aller Domains zu erstellen. Dazu selektiert man die Tabelle logdata: Bei dieser Abfrage werden sämtliche Kindtabellen abgefragt. Alternativ bezieht man bei der Abfrage nur die Tabelle einer bestimmten Domain ein, was die Auswertung natürlich beschleunigt. Zusätzlich wäre es möglich, die Kindtabellen auf einem anderen Tablespace zu erstellen, falls der Platz auf der aktuellen Partition nicht mehr ausreicht oder aber die Performance durch die Verteilung auf mehrere Festplatten gesteigert werden soll.

Da während des Importes normalerweise das Log eines ganzen Tages importiert wird und man dank Python mehr Möglichkeiten hat, als nur die Daten einzulesen und aufzubereiten, empfiehlt es sich, in diesem Zuge auch gleich die Statistik für die Tageswerte anzufertigen. So könnte z. B. mittels geoip eine Statistik der länderspezifischen Zugriffe oder die Traffic- sowie allgemeine Zugriffszahlen erstellt werden. Da es sich hier um Werte handelt, welche sich pro Domain und Import unterscheiden können, bietet es sich an, diese im jsonb-Format zu speichern. So kann eine Tabelle für Tageswerte wie folgt erstellt werden:

CREATE TABLE daily_data (dailys JSONB);

Somit können zum Beispiel folgende Datensätze im Anschluss des Imports eingetragen werden:

{ domain:'foobar.de', date:'2015-01-01 00:00:00', traffic:'466054', count:'6354', de:'4723', en:'64', … }

Dies erspart eine tägliche Auswertung nach dem Import und ist zusätzlich sehr flexibel, da sich die Kriterien der Auswertung durchaus im Laufe der Zeit ändern können und dafür nicht erst die Tabelle angepasst werden muss. Längerfristige Statistiken – wie wöchentlich, monatlich oder jährlich – können ebenfalls bei jedem Durchlauf entsprechend aktualisiert werden, falls eine Statistik über die laufenden Vorgänge gewünscht ist.

Ist man bei seinem Hoster zum Beispiel im Traffic pro Monat begrenzt, so kann man an dieser Stelle den gesamten Traffic für den aktuellen Monat berechnen und ggf. direkt aus dem Skript heraus eine Mailbenachrichtigung senden, sobald Grenzwerte überschritten sind. Da dies jedoch auch häufig automatisch geschieht, soll es nur als einfaches Beispiel dienen. Interessanter wäre hier vielleicht eher eine E-Mail mit den tagesaktuellen Zugriffswerten an die Marketingstelle. So zum Beispiel eine Top 10 der aufgerufenen URLs bei einem Onlineshop.

Der Vorteil dieser Lösung ist, das zum einen die Möglichkeiten sehr weitreichend sind, zum anderen auch die Implementation jederzeit ohne großen Aufwand angepasst werden kann. Es ist jedoch zu empfehlen, dies in eine zusätzliche Funkion auszulagern, wenn nicht zwingend Daten aus dem aktuellen Skriptdurchlauf benötigt werden. So wird die Funktion, in welcher der Import stattfindet, nicht unnötig kompliziert und Änderungen am Code können differenzierter durchgeführt werden.

Ebenfalls zu erwähnen sind rechtliche Einschränkungen in der Speicherung von IPs. So dürfen auch Provider IPs nur für 7 Tage speichern. Auch diese Problematik lässt sich recht einfach lösen, indem man entweder die IPs gar nicht erst importiert oder aber bei der Erstellung der wöchentlichen Statistiken löscht bzw. auf eine feste Dummy-IP ändert. Beides bedeutet natürlich zusätzliche Last und Laufzeit bei der Auswertung. Davon abgesehen werten nur wenige die Zugriffe pro IP oder Netz aus, meist ist eher das Land interessant. Wie genau verfahren werden soll, sei jedem selbst überlassen. Am einfachsten ist es jedoch, wenn die IP in der Statistik entweder gar nicht erst erfasst wird und nur das Land in die tägliche Auswertung einfließt oder die IP direkt während des Importes angepasst wird.

Import der Log-Dateien

Wie funktioniert nun genau die Implementierung? Es folgen einige Auszüge aus einem Beispiel mit Erläuterungen.

Die Erstellung einer plpythonu-Funktion in PostgreSQL unterscheidet sich nicht grundlegend von anderen Funktionen:

CREATE FUNCTION funcname (argument-list)
  RETURNS return-type
AS $$
  # PL/Python function body
$$ LANGUAGE plpythonu;

Der Funktionskörper enthält normalen Python-Code. Es können Module importiert und auch sonst alle Funktionen genutzt werden. Der Zugriff auf die Datenbank selbst geschieht mittels des Moduls „plpy“, welches bereits initial geladen ist. Ein Beispiel:

impath_q=plpy.execute("SELECT configvalue FROM config WHERE configname = 'importpath';")
impath=impath_q[0]['configvalue']

Damit könnte der Pfad zu den zu bearbeitenden Logdateien aus einer zusätzlichen Tabelle namens „config“ ausgelesen werden. „plpy.execute“ liefert hier ein Liste mit Dictionaries zurück, wobei auf die einzelnen Zeilen der Antwort mittels „[Nummer][Spalte]“ zugegriffen werden kann. Zusätzliche Informationen sind der offiziellen Dokumentation zu entnehmen, für das aktuelle Beispiel ist jedoch nicht mehr nötig.

Da die Erläuterung der gesamten Funktion inkl. Code zu umfangreich wäre, folgt hier nur eine grobe Darstellung mit Codebeispielen an wichtigen Punkten. Zunächst wird eine Liste der im Importordner befindlichen Dateien erstellt, welche anschließend nacheinander bearbeitet wird. Hier sollte eine Prüfung auf bereits gepackte Dateien durchgeführt werden. Anschließend wird jede Zeile der aktuellen Logdatei mittels regulärem Ausdruck eingelesen und in ein Dictionary umgewandelt. Dies kann wie folgt aussehen:

# LogFormat "%v %{X-Forwarded-For}i %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\""
parts = [
    r'(?P<host>\S+)',
    r'(?P<rip>\S+)',
    r'\S+',
    r'(?P<user>.+)',
    r'\[(?P<time>.+)\]',
    r'"(?P<request>.+)"',
    r'(?P<status>([0-9]+|-))',
    r'(?P<size>\S+)',
    r'"(?P<referer>.*)"',
    r'"(?P<agent>.*)"',
    r'".*"'
    ]
pattern = re.compile(r'\s+'.join(parts) + r'\s*\Z')
…
m = pattern.match(line)
…
res = m.groupdict()

Anschließend werden einige der Werte zur Erleichterung der späteren Auswertung angepasst. Z. B. wenn der Request keine Daten übermittelt, wird der Wert von „size“ von „-“ auf „0“ geändert. Zusätzlich werden hier einzelne Werte für die tägliche Statistik in den entsprechenden Datenstrukturen hinterlegt.

Nun wird ggf. die neue Tabelle für die Domain angelegt. Hierbei prüft das Skript, ob in der aktuellen Datenbank eine Tabelle mit diesem Namen existiert:

table_exists=plpy.execute("SELECT relname FROM pg_class where relname=%s;" % ( plpy.quote_nullable(tablename),))
    if not table_exists:
           plpy.execute("CREATE TABLE IF NOT EXISTS %s (check(domain = %s))                 
 inherits(logdata);" % (tablename,plpy.quote_nullable(res['host'])))

Im Anschluss werden diese dann in die Tabelle eingetragen:

plpy.execute("INSERT INTO %s (domain, remip, remtime, method, request, status, size, referer, agent) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s);" % (
    tablename,
       plpy.quote_nullable(res['host']),
       plpy.quote_nullable(res['rip']),
       plpy.quote_nullable(res['time']),
       plpy.quote_nullable(res['method']),
       plpy.quote_nullable(res['request']),
       plpy.quote_nullable(res['status']),
       res['size'],
       plpy.quote_nullable(res['referer']),
       plpy.quote_nullable(res['agent'])))

„Tablename“ entspricht dem domainspezifischen Tabellennamen, welcher vorher natürlich noch formatiert werden muss. So empfiehlt es sich, alle „.“ und „-“ im Domainnamen durch „_“ zu ersetzen. Wichtig ist auch das Quoting der einzelnen Strings, da es sonst zu Fehlern beim Eintragen der Datensätze kommen kann.

Der Domainname wird zusätzlich noch einmal eingetragen, da dies bei einer Gesamtauswertung relevant sein kann. Zum Abschluss können die täglichen Statistiken in die „daily“-Tabelle geschrieben und ggf. die nächste Datei verarbeitet werden. Natürlich sollte nach der Bearbeitung der Datei darauf geachtet werden, dass diese aus dem Importordner entfernt oder anderweitig vom Import ausgeschlossen wird, damit bei einem erneuten Funktionsaufruf keine Daten doppelt importiert werden.

Bleibt noch die Frage, wie die Dateien in das Importverzeichnis gelangen und die Funktion aufgerufen wird. Die einfachste Möglichkeit ist, mittels Anpassung der jeweiligen logrotate-Regel die Dateien in den Importordner kopieren zu lassen. Das hat den Vorteil, dass nach dem Funktionsaufruf die verarbeitete Datei einfach gelöscht werden kann. Leider gibt es aktuell in PostgreSQL keine Funktion zum zeitgesteuerten Aufruf einer Funktion, wie dies z. B. in MySQL mittels Event-Funktionalität möglich ist. Wer der Sprache C mächtig ist, hat die Möglichkeit, mittels eines Background-Workers in PostgreSQL eine Art Cron zu implementieren, welcher die Importfunktion zu einem bestimmten Zeitpunkt aufruft. Alternativ könnte ein Benutzer im System erstellt werden, der außer dem Aufruf der gewünschten Funktionen keinerlei Rechte besitzt und via Cron-Daemon die Aufrufe übernimmt.

Fazit

Die Möglichkeiten der Logdateiverarbeitung in PostgreSQL sind vielfältig, aber einfach umzusetzen. Es muss dabei auch nicht auf PL/Python gesetzt werden, da verschiedene Sprachen unterstützt werden. Auch ist die hier vorgestellte Möglichkeit natürlich nur eine von vielen. Für sehr große Umgebungen mit täglich Gigabytes an Webserverlogs ist die Lösung natürlich auch nur bedingt geeignet, da abgesehen von der Verarbeitungszeit auch noch die Größe des Datenbestandes nicht zu unterschätzen ist. Aber für alle, denen die Installation und Pflege von zusätzlichen Webdiensten wie Webalizer oder awstats zu aufwändig oder unsicher ist, ist es auf jeden Fall eine Alternative.