Kostenloses Nutzungskontingent für Amazon Web Services

Die Vielfalt der Amazon Web Services ist gelinde gesagt abschreckend, wenn man sich dem Thema Cloud Computing das erste Mal nähern will. Das liegt aber auch daran, dass die Nutzung sofort mit Kosten verbunden ist. Damit nicht allzu viele potentielle Kunden zu Konkurrenten mit kostenlosen Einstiegstarifen abwandern – beispielsweise phpfog.com -, bietet Amazon Neukunden ein Jahr lang einen Teil seiner Infrastruktur-Ressourcen kostenlos an.

Enthalten ist zuerst einmal eine Micro EC2 (Elastic Cloud) Instanz mit 750 Stunden Rechenkapazität. Wie bei Amazon üblich wird nur die Nutzung bezahlt, d.h. wenn der Server nichts zu tun hat bzw. die CPU nicht belastet wird, geht das nicht zu Lasten des Stundenkontingents. Passend dazu gibt es einen 750 Stunden Load Balancer, der sich natürlich nur lohnt, wenn man mehr als eine Instanz am Laufen hat.

Zur Speicherung von Daten und Ressourcen (Dateien) steht eine Vielzahl von Services bereit, u.a. Amazon Elastic Block Store (EBS), Amazon Simple Storage Service (S3) oder auch Amazon SimpleDB, das sich noch im Betastatus befindet. Eine Übersicht findet man auf AWS in der Rubrik EC2 – Preise:

Eine detailliertere Übersicht findet sich hier.

Advertisements

Scriptella Version 1.0

Ich habe diese Script-ETL-Engine schon lange auf meiner Watchlist. Und auch wenn sich das Projekt sehr langsam entwickelt – sogar oft nach Stillstand aussah – lohnt sich immer ein Seitenblick.
Die Version 1.0 steht unter der Apache License (2.0) und ist somit auch in kommerzielle Projekte integrierbar. Da sich Skripte sehr gut über die Kommandozeile ausführen lassen, ist der Einsatz nicht auf Java beschränkt. Sofern ich die Zeit finde, werde ich mal ein Beispiel posten. Bis dahin, einfach mal unter http://scriptella.javaforge.com reinschauen.

CouchDB reif für die Produktion

Die Freigabe der Version 0.11.0 markiert den Meilenstein zur Produktionsreife von CouchDB. Das Release wird als „feature-freeze“ bezeichnet und ist als Release Candidate für die anstehende Version 1.0 zu verstehen.

Damit tut neben MongoDB ein zweiter NoSQL Vertreter Ende März einen weiteren Schritt Richtung Produktion. MongoDB 1.4 kam am 26.03.2010.

Der Download von CouchDB 0.11.0 steht für Unix im Apache Incubator unter http://couchdb.apache.org/downloads.html zur Verfügung und ist unter der Apache License 2.0 zu haben. Das Installationspaket für Windows ist zwar etwas versteckt, findet sich wie sein Vorgänger aber hier – die Lizenz ist selbstverständlich die gleiche.

MySQL Performance bei INNER Join vs. OUTER Join

Bei der Universenerstellung mit Business Object kam neulich die Frage nach dem Performancetuning auf, speziell in Bezug auf die verwendeten Joins. Zitat meines BO-erfahrenen Kollegen war: „Die Datenbank freut sich jedes Mal, wenn sie einen INNER Join statt OUTER Join machen darf.“.  Eine Google-Suche zu dem Thema ergab wenig quantifizierbare, valide Ergebnisse, also habe ich zuhause eine eigene Testreihe gestartet.

Mein Setup:

  • MySQL 5.4.2 Beta
  • Employees Datenbank, mit vervielfachter employees-Tabelle auf 10 Millionen Datensätze

Als Testabfrage dient ein Self-Join der Tabelle employees, der für OUTER und INNER Joins jeweils an fett gedruckter Stelle angepasst wird:

SELECT o.first_name, i.last_name
FROM employees o
{LEFT OUTER | INNER} JOIN employees i ON o.emp_no = i.emp_no
WHERE o.last_name =  'Sichman';

Die WHERE Klausel ist lediglich eingebaut, um die Ergebnismenge zu verkleinern. Als Ergebnis zeigt sich, dass der INNER Join in diesem Fall deutlich schneller ist (Durchschnittswerte nach 10 Iterationen mit FLUSH Statements, Angaben in Sekunden):

Jointyp Zeit in Sekunden
INNER JOIN 4.20
OUTER JOIN 12.17

By the way: Der Teufel steckt hier wie auch in obigem Business Objects Fall im Detail. Zeigt die WHERE Klausel auf die i-Tabelle, wird der Vorteil des INNER Join gegenüber dem OUTER Join dadurch zunichte gemacht – beide Anfragen haben fast identische Laufzeiten von über einer Minute.

Neuigkeiten in MySQL 5.4

Die Halbwertzeit von MySQL 5.1 als neueste Version der Open-Source-Datenbank war gering. Schon im April 2009, wenige Monate nach dem Produktivrelease von MySQL 5.1 (5.1.30 GA), kam die erste 5.4 Beta (5.4.0 Beta). Verantwortlich dafür war Internetgigant Google, der eigenentwickelte Patches in den offiziellen Entwicklungszweig übergab. Doch was haben diese Patches für den Normal-Anwender für nennenswerte Vorteile?

Das ChangeLog unter dev.mysql.com liest sich als Sammlung technischer Tuningmaßnahmen. Vorteile gibt es in folgenden Bereichen:
– Skalierbarkeit für Mehrkernprozessoren (Symmetric MultiProcessors), was bei steigender Anzahl paralleler Verbindungen schlagend wird
– Tuning der InnoDB Storage Engine durch Rekonfiguration
– Integration des InnoDB Plugins als Ersatz für die fest eingebaute Storage Engine
– Unterstützung von Pluggable Storage Engines unter Windows
– Stabilitäts- und Performancefixes im Rahmen der Replikation und Partitionierung
– Subquery und Join Optimierung
– Implementierung von SIGNAL und RESIGNAL (Dokumentation mittlerweile wieder entfernt)

MySQL 5.4 befindet sich zum Zeitpunkt dieses Posts (Oktober 2009) noch in der Betaphase. MySQL liegt in der Version 5.1.39 als produktives Releas vor. Bislang sind keine weiteren „Abweichungen“ von der ursprünglichen Roadmap dokumentiert.

PHP 5.3 und MySQL 5.1 als Online-Buch

Ab dem 19.Oktober 2009 wird es die Produktpalette des Verlages Galileo Press als Online-Buch geben. Mit dabei ist unser „PHP 5.3 und MySQL 5.1“. Die Bücher können dann von jeden onlinefähigen Computer gelesen werden. Technisch vergleichbar ist dies etwa mit Google Books. D.h. die Bücher werden nicht als PDF Version verteilt, sondern sind nach Anmelde-, Einlog- und Kaufprozess am Bildschirm zu betrachten.

Das Angebot sieht vor, Bücher alternativ online oder zusätzlich zur gedruckten Version zu kaufen – in entsprechend angepasstem Preismodell. Die in einer gedruckten Version vorhandene Registriernummer bietet dem Leser unabhängig davon eine Woche lang den Vorteil, auch die Onlineversion zu testen. Weitere Informationen und die Möglichkeit, das Angebot wahrzunehmen, finden sich unter http://booksonline.galileo-press.de.

Performance der SCD2-Lösung

Der Einsatz der von mir beschriebenen Lösung zur „Emulation“ von Slowly Changing Dimensions macht nur dann Sinn, wenn der Durchsatz von Daten akzeptabel hoch ist. Um das zu überprüfen, habe ich einige Performancetests auf MySQL-Basis gemacht.

Erzeugen von Datasets

Zuerst benötigen wir Daten, die wir dann per Bulkload in unsere Schattentabelle laden können. Dazu erstellen wir mit einem einfachen PHP-Skript randomisierte Datensätze. Der Quellcode für das PHP-Skript ist an diesen Artikel angehängt. Ergebnis nach Ausführung ist eine Reihe von Dateien von jeweils n Datensätzen – mit dem Namen n.txt.

Standardmäßig habe ich die folgenden Datenmengen erstellt: 100,500,1000,5000,10000,50000,100000. Für die Darstellung der Ergebnisse und die Zeitmessung sind noch weitere Dateien hinzugekommen.

Testreihe 1 – Laden der Dateien nach MySQL

Die Dateien werden dann nacheinander in die Datenbank geladen. Dies geschieht über den Befehl

LOAD DATA INFILE './n.txt' INTO TABLE cds_schatten FIELDS TERMINATED BY ';' LINES TERMINATED BY 'rn';

Nach einem Load werden die Daten wieder entfernt.

Die Ergebnisse sind ernüchternd. Ohne zusätzliche Strukturen wie Indizes verhundertfacht sich die Ausführungszeit bei der Verzehnfachung der Testdatenmenge. Abhilfe schafft ein kombinierter UNIQUE-Index über die Felder ean und gueltig_bis (in genau dieser Reihenfolge) in der Tabelle cds. Dieser Index bringt zwei Vorteile: Zum einen wird dadurch sichergestellt, dass es pro EAN keine zwei gültigen Datensätze gibt, da gültige Datensätze ja immer denselben Wert in der Zukunft haben. Zum anderen wird dadurch das Attribut ean über den Index auswertbar – eine Eigenheit von MySQL ist, dass bei kombinierten Indizes Anfragen auf die zuerst definierten Indexteile davon profitieren. Mehr zu den MySQL-Index-Eigenschaften unter nebenstehendem Link.

Testreihe 2 – Laden der Dateien in Tabellen mit Indizes

Das Vorgehen bei dieser zweiten Testreihe bleibt natürlich das gleiche. Der Index verfehlt seine Wirkung nicht. Die Zeit zum Laden der Daten steigt nun proportional zur Datenmenge. Der Durchsatz liegt bei auswertbaren Datenmengen gleichbleibend hoch bei über 5000 Rows pro Sekunde. Alle Ergebnisse sind im nebenstehende PDF tabellarisch und grafisch zusammengefasst.

Slowly Changing Dimensions Typ 2 mit MySQL

Über das folgende Problem bin ich neulich bei dem Aufbau eines DWH gestolpert. Eine Datenbank-Tabelle wird per Bulkload befüllt, soll aber als Slowly Changing Dimension (SCD) vom Typ 2 historisiert werden. Der Bulkload wird per Perlskript angestoßen, das nicht unter der eigenen Hoheit liegt und somit nicht mit eigener Logik ausgestattet werden kann. Die Lösungsidee ist einfach: nur mit Bordmitteln der Datenbank eine SCD Typ2 emulieren…
Gegeben ist die folgende, historisierte Dimensionstabelle – in unserem Beispiel in MySQL 5.0.51 hinterlegt:

CREATE TABLE IF NOT EXISTS `cds` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`ean` varchar(13) collate latin1_general_ci NOT NULL,
`md5` varchar(32) collate latin1_general_ci NOT NULL,
`titel` varchar(200) collate latin1_general_ci default NULL,
`interpret` varchar(200) collate latin1_general_ci default NULL,
`jahr` bigint(4) default NULL,
`gueltig_ab` datetime NOT NULL,
`gueltig_bis` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

Die Tabelle besitzt zwei unterschiedliche Schlüssel: ID ist ein technischer Schlüssel, der über AUTO_INCREMENT hochgezählt wird. Die Spalte EAN, die für Handelsware gebräuchliche European Article Number, ist der Fachschlüssel eines jeden Datensatzes. Neben diesen beiden Schlüsseln existiert noch die Hilfsspalte MD5, in der die fachlich relevanten Spalten, in diesem Fall der Titel, der Interpret und das Erscheinungsjahr, gehasht werden. Mehr zu dem Sinn der Hashspalte, wenn wir zu den Historisierungsmechanismen kommen.

Erzeugen einer Schattentabelle

Um die SCD2 emulieren zu können, benötigen wir einen Schatten zur Zieltabelle: Wir erzeugen eine zweite Tabelle ‚cds_schatten‘, die strukturell ähnlich zu ihrem Vorbild ist, nur die Historisierungsfelder und die ID entfallen. Die Schattentabelle hat also das Format, das wir von der Bulkload-Datei erwarten.

DROP TABLE IF EXISTS `cds_schatten`;
CREATE TABLE IF NOT EXISTS `cds_schatten` (
`ean` varchar(13) collate latin1_general_ci NOT NULL,
`md5` varchar(32) collate latin1_general_ci NOT NULL,
`titel` varchar(200) collate latin1_general_ci default NULL,
`interpret` varchar(200) collate latin1_general_ci default NULL,
`jahr` int(11) default NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Als Storage Engine wählen wir BLACKHOLE. Datensätze, die wir in diese Tabelle schreiben, wollen wir nämlich nicht permanent behalten. Wir benötigen sie nur, um die eigentliche Zieltabelle ‚cds‘ zu befüllen.

Implementierung der Historisierung

Die Historisierung wird nach dem folgenden Schema vorgenommen: Ein neuer Datensatz wird anhand des fachlichen Schlüssels, der EAN, als Version eines vorhandenen Satzes erkannt. Die Speicherung macht aber nur dann Sinn, wenn sich die Inhalte eines der fachlichen Attribute verändert hat, sprich, wenn sich die Hashes der beiden Versionen unterscheiden. Wir laden den Datensatz also zuerst in die Schattentabelle und schauen dann nach, ob sich gemäß dem oben definierten Schema die Notwendigkeit ergibt, den neuen Datensatz in die eigentliche Zieltabelle zu übertragen. Das realisieren wir über einen einfachen BEFORE INSERT Trigger:

DROP TRIGGER IF EXISTS `test`.`bi_cds_schatten`;
DELIMITER //
CREATE TRIGGER `test`.`bi_cds_schatten` BEFORE INSERT ON `test`.`cds_schatten`
FOR EACH ROW BEGIN
DECLARE hash VARCHAR(32);
SELECT md5 INTO hash FROM cds WHERE ean=NEW.ean AND gueltig_bis>NOW();
IF ((hash IS NULL) OR (hash<>NEW.md5)) THEN
UPDATE cds SET gueltig_bis=NOW() WHERE ean=NEW.ean AND gueltig_bis>NOW();
INSERT INTO cds (ean,md5,titel,interpret,jahr,gueltig_ab,gueltig_bis)
VALUES (NEW.ean,NEW.md5,NEW.titel,NEW.interpret,NEW.jahr,NOW(),21001231);
END IF;
END
//
DELIMITER ;

Für jeden Eintrag in die Schattentabelle wird dann der Trigger aufgerufen und trägt in Folge dessen eventuell Daten in die Zieltabelle ein.
Wir definieren im Trigger zuerst eine Variable hash. Als nächstes prüfen wir per SELECT, ob passend zu der neu eingetragenen EAN ein Gegenstück in der Zieltabelle existiert (ean=NEW.ean), das auch zurzeit gültig ist (gueltig_bis>NOW()). Das Ergebnis dieser Anfrage speichern wir in der Variable hash. Für den Inhalt von hash gibt es dann zwei Möglichkeiten: entweder steht darin ein 32-stelliger, alphanumerischer MD5-Wert, d.h. die Suchanfrage hat ergeben, dass es eine alte, aber gültige Version zu der aktuellen EAN gibt, oder der Wert der Variable ist NULL, d.h. es gab keine Vorgängerversion.
Folglich vergleichen wir den Variablenwert mit dem neu einzutragenen MD5-Wert. Ist der Hashwert ungleich (hash<>NEW.md5) oder von vornherein NULL, bedeutet das, dass wir den neuen Datensatz in die Zieltabelle eintragen müssen, ansonsten ist die alte Version unverändert. Dazu terminieren wir zuerst die Gültigkeit der alten Version per UPDATE-Befehl und tragen die neuen Daten ein – inklusive Historisierungsfelder.
Nach Ausführung des Triggers wird der Datensatz in der Schattentabelle nicht permanent gespeichert, sondern verworfen – ein angenehmer Effekt der BLACKHOLE Storage Engine.

Fazit
Die vorgestellte Lösung besitzt einige charmante Effekte: Zum einen müssen wir uns beim Eintragen der Daten nicht um die Befüllung der Historisierungsfelder und die Terminierung alter Versionen interessieren, dies übernimmt der Trigger. Zum zweiten müssen wir uns dank der Wahl der richtigen Storage Engine nicht um die Entsorgung der Inhalte in der Schattentabelle kümmern und zum dritten müssen wir für diesen Weg keinerlei Änderungen an der Zieltabelle selbst vornehmen.
Die Nachteile, die sich in Bezug auf die Performance ergeben, sind schwer zu bemessen. Sicherlich existieren Reibungsverluste durch die Ausführung des Triggers bei den Einträgen. Dem gegenüber stehen jedoch Gewinne bei der Erstellung klassischer ETL-Jobs, in denen die richtige Befüllung der SCD implementiert ist.
Dieses Konzept sollte sich prinzipiell auch auf andere DBMS übertragen lassen. Nicht überall existiert jedoch ein Konstrukt wie die BLACKHOLE Storage Engine, so dass man die Daten in der Schattentabelle eventuell händisch wieder löschen muss.

MySQL: Referentielle Integrität in MyISAM

Ein Blick in die Roadmap von MySQL verrät: Ab MySQL 6 soll die referentielle Integrität in allen Storage Engines gewährleistet werden können. Wer nicht solange wartet kann oder will, hat ab MySQL 5.0 die Chance, diese Funktionalität über Trigger nachzubilden.

Referentielle Integrität garantiert, dass in einer Gruppe von Tabellen, die semantisch miteinander in Beziehung stehen, keine verwaisten Einträge entstehen. Am konkreten Beispiel bedeutet das:

Wir haben eine Tabelle User, in der wir Benutzerdaten einer Blog-Community speichern. Darin enthalten sind Benutzername, Email-Adresse, Datum des Beitritts zur Community usw. Eine weitere Tabelle nimmt die Blogeinträge der User auf, also deren Überschrift, den Text und den Zeitpunkt des Eintrags. Gemäß der Normalisierung existiert in der Tabelle der Blogeinträge nur ein Verweis zur ID des Users (im Attribut autor), Abfragen wie die folgende müssen Userdaten somit via Join aus beiden Tabellen holen.

SELECT u.name, b.titel, b.text
FROM user u, blogbeitrag b
WHERE b.autor=u.uid AND b.bid = "123"

Beendet nun ein Benutzer seine Mitgliedschaft in der Community und wird der Datensatz aus der Tabelle Users gelöscht, erscheint für seine Blogeinträge kein Autorenname mehr. Blogeinträge werden zu so genannten Waisen. In der Praxis lassen sich solch unangenehmen Situation vermeiden, indem man nullwertige Referenzen filtert.

Abhilfe in diesem oben genannten Fall schafft ein Fremdschlüssel, der die referentielle Integrität gewährleistet. Das Attribut u.uid aus obigen Beispiel, also der Primärschlüssel der Usertabelle – hier die so genannte Elterntabelle-, wird in der Blogeintragstabelle – in diesem Fall als Kindtabelle bezeichnet – im Attribut b.autor referenziert. In MySQL ist das bis zur Version 5.1 nur in der InnoDB Storage Engine möglich.

Trigger

Um referentielle Integrität für eine MyISAM Tabelle in MySQL nachzubilden, benötigt man vier Trigger, jeweils zwei für die Eltern- und Kindtabelle. Die Trigger überprüfen jede Datenbankoperation, für die sie definiert sind – also INSERT-, UPDATE- und DELETE-Anweisungen -, vor ihrer Ausführung. Als Prüfung ist eine wechselseitige Abfrage nötig, das heißt die Trigger der Kindtabelle führen ein SELECT auf die Elterntabelle aus und umgekehrt. Gesucht wird die Anzahl der semantisch abhängigen Datensätze. Kommt der Trigger zu dem Ergebnis, dass Integritätsbedingungen durch die anstehende Operation verletzt werden, wird ein Fehler erzeugt. Da MySQL derzeit noch über kein entsprechendes Gegenstück zu dem SIGNAL aus Oracle oder ähnlichen Mechanismen anderer Datenbanksysteme verfügt, mit dem der Benutzer einen Systemfehler hervorrufen kann, muss man hierbei zu einem kleinen Trick greifen:

Durch eine fehlerhafte SQL-Anweisung verursacht man einen MySQL-Fehler, der nicht nur die Ausführung des Triggers beendet, sondern auch die ursprüngliche Datenbankoperation verhindert. Schöpfen kann man dabei aus dem reichhaltigen Fundus der MySQL-Server-Fehler, die im Anhang B des Handbuchs aufgelistet sind. Welchen Fehler man wählt, ist egal; nur muss man im Nachhinein noch wissen, dass man eben diesen Fehler, der einem beim Debugging begegnet, zweckentfremdet wurde.

Trigger nutzen wie Stored Procedures das Semikolon als Zeilenendzeichen, das standardmäßig auch alle anderen Datenbankoperationen beendet. Damit es zu keiner Fehlermeldung bei der Triggererstellung kommt, ersetzen wir den Standard durch eine doppelte Raute (##):

DELIMITER ##

Danach erzeugen wird zuerst die Trigger für die Kindtabelle:

CREATE TRIGGER bi_blogeintrag BEFORE INSERT ON blogeintrag FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(uid) INTO cnt FROM user WHERE uid=NEW.autor;
IF cnt=0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##

Das bi_* im Triggernamen steht für BEFORE INSERT und dient nur der schnellen Erkennung der Funktionalität. Der Name spielt für die Funktion keine Rolle und kann frei gewählt werden. Die Anzahl der abhängigen Datensätze speichern wir in cnt, um es gleich danach wieder abzufragen. Wurden keine Datensätze gefunden, erzeugen wir mit dem zweiten SELECT den Fehler #1096 – No table used. Der INSERT-Trigger stellt dadurch sicher, dass jeder Eintrag in der Kindtabelle einen korrespondierenden Eintrag in der Elterntabelle hat. Neben dem INSERT-Trigger brauchen wir für die Kindtabelle noch einen Trigger, der Updates überprüft – für den Fall, dass der Autor eines Eintrags verändert werden soll:

CREATE TRIGGER bu_blogeintrag BEFORE UPDATE ON blogeintrag FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(uid) INTO cnt FROM user WHERE uid=NEW.autor;
IF cnt=0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##

Für die Elterntabelle müssen andersartige Trigger definiert werden: jeweils einer für die Operationen UPDATE und einer für DELETE. Der Aufbau ist analog zu den Triggern der Kindtabelle.

CREATE TRIGGER bu_parent BEFORE UPDATE ON parent FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(bid) INTO cnt FROM blogeintrag WHERE b=OLD.uid;
IF cnt>0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##

Last but not least:

CREATE TRIGGER bd_parent BEFORE DELETE ON parent FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(bid) INTO cnt FROM blogeintrag WHERE bid=OLD.uid;
IF cnt>0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##

Fazit

Die Triggerlösung ist eine gangbare Alternative zu Fremdschlüsseln für die Einhaltung referentieller Integrität, solange MySQL 5.2 noch nicht veröffentlicht ist. Nichtsdestotrotz ist dies nur eine Übergangslösung, da das Nachprogrammieren von Kernfuktionalitäten selten an die Performance der eigentlichen Lösung heranreicht. Wie groß der Leistungsunterschied wirklich ist, werden jedoch erst spätere Tests zeigen.