Bayes 0.3.9.1: Datenbankperformance

Die Import- und Exportfunktion arbeiteten einwandfrei in meinen Tests mit kleinen Beispieldaten, scheiterten aber an der Größe der Datenbanktabellen bei Dirk und YellowLed. Bei Dirk klappte der Export, aber der Import starb mit einem Timeout, bei Yellowled riss der Export die Speichergrenze von 64 MB. Mit der Hilfe der beiden konnte ich beide Fehler - zumindest auf meinem Testsystem - beheben, und ich werde hier beschreiben wie.

Export

Der Export scheiterte an der Speichergrenze, also musste der Speicherbedarf reduziert werden. Es passiert eigentlich nichts anderes, als das mit einem Select die ganze Datenbank abgefragt und diese Daten mittels fputcsv in eine Datei geschrieben werden. Um also den Speicherbedarf zu reduzieren, wird das nun nur noch in Blöcken von 10.000 Zeilen gemacht:

while ($amount > ($start = $runs * 10000)) {
    $sql = "SELECT 
           token, ham, spam, type  
        FROM
           spamblock_bayes
        LIMIT $start, 10000";
    $database = serendipity_db_query($sql);
    
    foreach ($database as $fields) {
        fputcsv($fp, $fields);
    }
    $runs++;
}

Ich bin mir bei Limit immer etwas unsicher, aber da es von sqlite ebenfalls unterstützt wird und in meinem Test so der Export auch bei einer Grenze von 16 MB durchlief, dürfte das eine akzeptable Lösung sein.

Import

Der Import war kniffliger, hier griff ich massiv auf Hilfe von Dirk zurück. Zuerst einmal das bisherige Datenbankschema:

| token (varchar) | ham (int) |spam (int) | type (varchar) |

Er stellte fest, dass Duplikate in der Datenbank waren - zumindest in seiner MySQL-Datenbank. Denn bei ihr ist "hallo" und "Hallo" identisch, in PHP (und sqlite) jedoch nicht, wodurch doppelte Datensätze angelegt wurden. Ein Key, der das verhindert hätte, war nicht vorhanden. Diesen jedoch nun auf token und type zu legen erschien sinnvoll, weil das an sich eine eindeutige Kombination ist und beim Import abgefragt wurde:

$sql = "SELECT 
            token 
        FROM 
            spamblock_bayes
        WHERE 
             token = '$token' AND type = '$type'";

$tester = serendipity_db_query($sql);

if (empty($tester[0])) {
    $sql = "INSERT INTO 
            spamblock_bayes 
                (token, ham, spam, type)
        VALUES('$token', $ham, $spam, '$type')";
} else {
    $sql = "UPDATE spamblock_bayes
        SET 
            ham = ham + $ham,
            spam = spam + $spam
        WHERE token = '$token' AND type = '$type'";
}

Da Duplikate in der Datenbank waren, konnte da kein Key und damit kein Index drübergelegt werden, der die SELECT-Abfrage mit dem WHERE token = '$token' AND type = '$type'"; massiv beschleunigt. Also mussten diese entfernt und danach der Key darübergelegt werden.

Duplikate entfernen

Dirk schlug folgendes Vorgehen vor, das ich allerdings nicht nutzen konnte:

ALTER TABLE serendipity_spamblock_bayes ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);

select a.id, a.token, a.type from serendipity_spamblock_bayes as a, serendipity_spamblock_bayes as b where a.token=b.token and a.type=b.type and a.id <> b.id;

Das funktionierte auch und hätte die Duplikate geliefert. Aber an der folgenden Stelle scheiterte ich:

Anschliessend summiert man die gleichen Datensätze zusammen: "select sum(ham),sum(spam) where id in (1,2,3,4)" oder gleich als neuer Datensatz "insert into tabelle (token,type,spam,ham) values (wert1,wert2,select sum(ham),sum(spam) where id in (1,2,3,4))" und anschliessend "delete from tabelle where id in (1,2,3,4)".

Ich hatte ein Array voller Duplikate, geordnet nach id oder type oder token. Bei denen hätte ich nun - zumindest verstand ich das so - einzeln heraussuchen müssen, welche zusammengehören, und diese dann wie beschreiben zusammenfügen müssen. Da schon das Select in Dirks Tabelle 5 Minuten dauerte, erschien mir das als nicht in akzeptabler Rechenzeit durchführbar, überhaupt verknotete sich an der Stelle mein Gehirn. Normalerweise hätte ich hier nachgehakt (ergänze mich doch einfach hier, wenn ich einen einfachen Lösungsweg schlichtweg nicht sah), aber ich stolperte nebenbei über eine alternative Lösung (die er ebenfalls erwähnt hatte), die direkt funktionierte.

Diese Lösung lautet: Nutzung einer temporären Tabelle und von ON DUPLICATE KEY UPDATE. Beim Update auf die neue Version des Plugin wird eine temporäre Tabelle angelegt, die bereits den Primary Key auf token und type hat. In diese werden der bisherige Dateninhalt inserted. Dabei werden die Konflikte auftreten, doch dank ON DUPLIKATE KEY kann MySQL darauf reagieren:

INSERT INTO 
    spamblock_bayes_temp 
        (token, ham, spam, type) 
    SELECT 
        orig.token, orig.ham, orig.spam, orig.type 
    FROM 
        spamblock_bayes as orig 
ON DUPLICATE KEY UPDATE 
    ham = spamblock_bayes_temp.ham + VALUES(ham), 
    spam = spamblock_bayes_temp.spam + VALUES(spam);

Danach wird die originale Tabelle gedroppt, mit dem Key neu erstellt und die alten Daten da hineingesetzt.
Bei sqlite oder anderen Datenbanken wird stattdessen wie oben gezeigt mit einem Tester gearbeitet.

Der Import selbst

Das Vorgehen beim Import selbst wurde äquivalent angepasst. Statt da wie oben gezeigt mit einem Select einen Tester zu holen und je nach dem zu inserten oder upzudaten, wird dort ebenfalls erstmal inserted und bei einem Konflikt mit einem ON DUPLICATE KEY UPDATE reagiert. Das löste bei mir das Problem, das das Importieren der 22000 Zeilen langen CSV-Datei aus Dirks Blog der Import länger als 10 Minuten dauerte. Das geht auf meinem Testsystem nun in 30 Sekunden.

Ich war jedoch ehrgeizig und schaute mir an, wie das ganze mit sqlite gehen könnte. Denn dort gibt es kein ON DUPLICATE KEY UPDATE. Aber es gibt ein INSERT OR IGNORE. Damit kann man ein Insert durchführen, wobei ein Fehler einfach ignoriert wird. Das Vorgehen ist dort also, erst immer zu inserten mit Wert 0 für spam und ham und direkt danach mit einem Update den Wert zu setzen. Ich war etwas stolz, so eine schöne Lösung gefunden zu haben, stieß den Import an - und wartete, wartete, wartete...
Als nach einer halben Stunde das immer noch nicht fertig war suchte ich nach Performance-Tipps für sqlite. Dabei stieß ich auf den Hinweis, man solle unbedingt Transaktions nutzen, selbst wenn man nur eine Datenbank liest - das Plugin schreibt, sollte sie also auf jeden Fall nutzen. MySQL scheint das von selbst ordentlich zu machen, sqlite (sqlite 2, warum auch immer nicht automatisch von PHP unter Ubuntu 10.04 das installierte sqlite 3 genutzt wird) nicht. Also ein

serendipity_db_begin_transaction();

vor den Code und ein

serendipity_db_end_transaction(true);

dahinter, und schon lief der Import in 40 Sekunden.

Vielleicht sind das keine Spitzenzeiten und es geht noch mehr, aber es sind Verbesserungen um (mehr als) 1500%. Daher ist das Update nun hochgeladen, um Export und Import überhaupt erstmal zu ermöglichen. Wie immer gilt: Sollten Probleme auftauchen, bitte melden.

Download: serendipity_event_spamblock_bayes-0.3.9.1.tar.gz