SQLite Caching mit Zend_db

Bei größeren Projekten kommen die Themen Performance & Caching irgendwann in den Fokus – falls es nicht schon vorher der Fall war. In diesem Artikel möchte ich besonders auf Architekturen eingehen, die aus mehreren Applikations-Servern und einem Datenbank-Server bestehen. Mit den Hits auf die Applikationen steigen die Datenbankanfragen und im schlimmsten Fall wird die Last durch Anfragen von den verschiedenen Applikations-Servern zu hoch.

Um die Applikationen jedoch dauerhaft stabil und performant zu halten, sind Änderungen zwingend notwendig. Die Hardware kann in diesem Fall natürlich aufgerüstet werden, ich denke jedoch für uns als Entwickler ist die Herausforderung das Problem auf der Entwicklungsebene zu lösen. Nahe liegend ist es also die Last durch die Zugriffe auf den Datenbank-Server zu verringern und die Übertäter – lange oder oft genutzte Queries – zu optimieren oder zu cachen . Wie der Titel sagt, gehe ich heute auf das Caching ein. Bei meiner aktuellen Einarbeitung in das Zend Framework, bin ich relativ schnell über Zend_db gestolpert und hatte das Caching im Hinterkopf. Da kam die kleine, schnelle Datenbank SQLite, die von Zend_db unterstützt wird, gerade richtig.

Für alle, die noch keinen Kontakt mit SQLite hatten: Es ist eine auf C basierende Datenbank, die ohne jegliche Konfiguration direkt genutzt werden kann. Eine Datenbank wird bei Erstellung in eine einzige Datei geschrieben und ist somit äußerst platzsparend und backupfreundlich. Seit PHP5 ist die SQLite-Extension standardmäßig integriert. Mit PHP 5.3 ist die Erweiterung SQLite3 erschienen, die zusätzliche Funktionen bietet.

Um nun also das Caching effizient umzusetzen, sollte die SQLite Datenbank aus Performance-Gründen direkt auf dem Applikations-Server platziert werden, wo die Daten benötigt werden.
Dadurch wird erstens der ausgelagerte MySQL Server entlastet und zweitens muss die Anfrage nicht durch das Netzwerk geschickt werden, sondern kann direkt innerhalb der Applikationverarbeitet werden.

Im folgenden Beispiel werden die meist verkauften Artikel (Topseller) aus einer fiktiven Online-Shop-MySQL-Datenbank gezogen und in eine SQLite-Datenbank geschrieben.
Somit ist es möglich, die oft in dem Shop aufgerufenen Topseller abzufragen, ohne bei jedemAufruf eine Verbindung zur MySQL-Datenbank herzustellen.

<?php>
require_once('Zend/Db/Adapter/Pdo/Sqlite.php');
require_once('Zend/Db/Adapter/Mysqli.php');

$config_sqlite =     array(
  'dbname' => 'sqlite_dev.sqlite',
  'sqlite2' => true
);

$config_mysql =    array(
  'host' => '192.168.0.22',
  'username' => 'dev_user',
  'password' => '123456789',
  'dbname' => 'dev'
);

$sqlite_db = Zend_Db::factory('pdo_sqlite', $config_sqlite);
$mysql_db = Zend_Db::factory('mysqli', $config_mysql);

// MySQL
$query = ("select sum(a.number) as sales_count,
  b.description,
  c.picture,
  c.price,
  c.tax
  from
  art_order as a,
  art_description as b,
  art_data as c
  where
  b.id = a.article_id and
  b.language = 'de' and
  c.id = a.article_id
  group by
  a.article_id
  order by
  sales_count
");
$result = $mysql_db->fetchAssoc($query);

// SQLITE
$sqlite_db->query(" DROP TABLE 'topseller'");

$sqlite_db->query(" 	CREATE TABLE 'topseller' (
  'rank' INTEGER PRIMARY KEY,
  'picture' TEXT,
  'description' TEXT,
  'price' REAL,
  'tax' REAL
  );
");

$insert_data = array();
$rank = 1;

foreach($result as $value){
$insert_data = array(
  'rank' => $rank,
  'picture' => $value['picture'],
  'description' => $value['description'],
  'price' => $value['price'],
  'tax' => $value['tax']
     );

$sqlite_db->insert('topseller', $insert_data);
$rank++;
}
?>

Die Kombination von MySQL und SQLite im Quellcode, dient in diesem Fall der Übersichtlichkeit und Vergleichbarkeit der beiden Methoden über Zend_db.

Im SQLite Konfigurationsarray ($config_sqlite) habe ich den Parameter (’sqlite2′ => true) absichtlich stehen lassen. Wer PHP 5.3 bereits nutzt, kann diesen einfach entfernen, wodurch automatisch die SQLite3-Extension verwendet wird. Zudem ist es wichtig zu wissen, dass SQLite3 die Datentypen INTEGER, REAL, TEXT & BLOB als Hauptdatentypen unterstützt, jedoch durch ein dynamisches Datentyp System jedem Wert einen eigene Datentyp zuweist.

Um die Topseller innerhalb der SQLite-Datenbank aktuell zu halten, wäre ein Cronjob eine elegante Lösung bzw. die Einbindung in eine Caching-Klasse zur Erweiterung einer schon bestehenden Struktur.

Aus eigener Erfahrung kann ich berichten, dass SQLite bei Abfragen auf eine einzelne Tabelle schneller ist, als eine Abfrage auf eine MySQL-Tabelle. Somit eignet sich SQLite im Besonderen für diese Methode. Für komplexere Abfragen und sehr große Datenmengen, gibt es jedoch geeignetere Methoden. Bei Interesse an der Vielfältigkeit, sowie der Nutzung von Zend_db oder SQLite, werde ich dieses Thema gerne aufgreifen und in Kürze weiter darüber berichten.

16 Comments

Willst du SQLite als Cache verwenden über Zend_Db? Das kommt mir bei dem Artikel gerade so rüber …

Zend_Cache bietet als Cache Backend auch SQLite an, was den Quellcode sicherlich um einiges lesbarer machen würde. Je nachdem würde ich aber eher zu einem Cache Backend wie Memcached oder Zend Server ShMem (oder gleich das TwoLevel Cache Backend) verwenden.

*räusper*
http://www.cwd.at/2009/05/18/caching-zend_db_table-models/

(kann ohne probleme auch sqlite als cache backend verwenden)

🙂

@Dennis Die Zend_Cache Methode ist für das Beispiel natürlich auch gut geeignet.
Mir geht es in dem Artikel allerdings um die allgemeine Vorgehensweise einen externen Datenbankserver zu entlasten.
Häufig genutze Tabellen können mit dieser Methode leicht komplett ausgelagert werden und sind durch die genannten Beispiele besser zu erreichen.
Den großen Unterschied sehe ich daran, dass bei der beschriebenen Methode weitere Queries auf die Daten möglich sind. Bei Zend_Cache geht es um das Caching bestimmter Daten.
Ich habe den Vorgang als Caching bezeichnet, weil um eine Speicherung mit Performancesteigerung geht.

Also ich persönlich halte nicht viel davon, die Daten zu trennen oder doppelt vorzuhalten. Das widerspricht doch eigentlich der kompletten Architektur, diem an sich da aufgebaut hat und bedeutet meiner Meinung nach ein Rückschritt. Wenn ich wirklich mehr Performance rausholen will, setze ich halt einen Memcached-Server ein, so dass erst garnicht von der langsamen Festplatte gelesen werden muss.

Zudem ist der Faktor Netzwerk eigentlich eher zu vernachlässigen, da normalerweise alle Server am selben Standort sind und somit eine Verzögerung von max. 1-2ms durch die Netzwerkübertragung verursacht wird.

Das nächste Problem bei deiner Variante: was mache ich, wenn ich mehrere Webserver habe? Soll ich dann die Daten auf allen Servern ablegen? Die Skalierbarkeit geht doch eigentlich gen null, wenn teilweise Daten auf dem Webserver abgelegt werden. Ich sehe eigentlich viel mehr Nachteile als Vorteile.

Ich verstehe die Idee mehr als Möglichkeit SQLite als Slave-DB-Server für einen MySQL-Server zu verwenden.

Somit können auch kleinere individuelle Abfragen auf Standard-Tabellen die Last vom MySQL-Server nehmen.
Es geht weniger um das Caching von Abfrageergebnissen.

Also irgendwie hab ich das gefühl, keiner will oder mag mich nicht verstehen 😉

Selbst wenn man einen Slave nutzen möchte, bietet sich immernoch MySQL selbst dafür an. Wenn man sich mal vorstellt, die Web-Applikation läuft auf 3 Webservern, dann würde niemand auf die Idee kommen, auf jedem Webserver lokal nochmal ne SQLite DB mit einem Teil der Daten aus dem MySQL Server laufen zu lassen. Wer garantiert am Ende, dass die Daten immer aktuell sind auf allen Systemen?

Mit einem Webserver mag das ganze ja ganz nett laufen und scheint temporär auch ne nette Lösung zu sein, aber da kann man auch lieber gleich andere Maßnahmen ergreifen, die dazu noch skalierbar sind.

@Dennis Bekanntlich führen viele Wege nach Rom.
Meine Meinung ist, es gibt zu diesem Thema kein Richtig oder Falsch. Es gibt verschiedene Lösungsansätze. Der Artikel zeigt einen möglichen Ansatz.

Was die Kommentare ergeben ist doch letztendlich, abhängig von der Auslastung / Anforderung gibt es eine Ideale Lösung.

Diese Lösung mit Sqlite und Queries bezogen auf nur eine Tabelle ist bis zu einem gewissen Volumen / Auslastung schon OK.

An einem gewissen Punkt wechselt man nicht nur die Datenbank, sondern nutzt auch PHP nicht mehr…

… viele Wege führen nach Rom …

@Dennis: Was ich nicht ganz verstehe warum du die last vom Datenbankserver auf den Applikationsserver verlegst ?

Gerade so simple queries wie du sie in der SQLite abbildest (sagst ja selbst, es macht nur mit einfachen tabellen sinn weil die eben schnell sind) lassen sich leicht über den Query_Cache von MySQL selbst lösen.

Wenn aber wie in deinem fall die daten in mehreren tabellen verteilt liegen, kannst du die daten ja in einen temporäre tabelle ablegen. Über eine stored procedure lässt sich die einfahc uptodate halten. (Man könnte die tablle auch gleich mit dem Typ Memory anlegen, schneller gehts dann kaum noch (solange du nicht mit anderen joinen musst)

Damit hier nicht der Falsche eindruck entsteht, ich will deine Idee und/oder deinen Artikel nicht schlecht machen. Ich weiß selbst was es arbeit ist einen Artikel zu schreiben.

@LubwigR: ich glaube du meintest Christian Wald und nicht mich 😉

Ich seh das übrigens genauso.

Warum man unbedingt von PHP weggehen sollte verstehe ich allerdings weniger und ist ohne sinnvolle Begründung auch nicht nachvollziehbar.

Ups natürlich, sorry Dennis.

Ich hab zu dem Thema nicht wirklich eine Meinung. Hatte aber eigentlich erwartet, dass es ein recht ruhiger Beitrag wird. Umso mehr freue ich mich natürlich über die rege Diskussion 🙂

Sorry! wollte das letzte Wort haben 🙂

Zum Caching halte ich ja eine Mischung aus Memcache Server und MySQL Tabellen mit der Memory Engine für besser geeignet. Besonders der Memcache ist sehr schnell und kann auch gut auf einem alten ausgemusterten Server betrieben werden, nur genug RAM sollte der Server haben. Der Memcache Server kann auch noch Skalieren, was bei SQLite nicht ganz so einfach geht.

SQLite halte ich ideal für z.B. I18n Daten.

Ich bin auch etwas erstaunt. So hätte ich es auch nicht gemacht um ehrlich zu sein.

1. Wir nutzen auch Zend_Cache, das ist genau dafür gemacht (in diesem Fall dann File- oder Memcached-Backend).
2. Wenn man mehrere Webserver betreibt (wie du es als Vorbedingung genannt hast), muß man unbedingt erwähnen, dass man dadurch evtl. unterschiedliche Daten auf der Webseite sieht, je nachdem, wann die Caches aktualisiert werden auf den verschiedenen Servern. Mit dem mysql-query-Cache ist das nicht der Fall (oder falls man die sqlite-Datei ins Netzwerk verschiebt)
3. Wenn die Tabelle recht groß wäre (mehrere 1000 oder 100.000 Zeilen), die es zu cachen gilt, wäre die Methode mit der foreach-Schleife und den einzelnen ->insert() recht ungeeignet (da jeder Insert eine eigene Transaktion ist), das kann man optimieren: http://www.sqlite.org/faq.html#q19

@Jörg & Michael: Danke für das Feedback.

Zumindest in PDO ist übrigens SQLite3 schon länger vorhanden. Entsprechender Treiber existiert auch für Zend_Db. SQLite ist auf jeden Fall zu bevorzugen 😉

[1] http://de2.php.net/manual/en/ref.pdo-sqlite.php