mySQL, ElasticSearch und MongoDB müssen sich einem echten Anwendungsfall stellen. Bei der Installation der Clients und Server war mySQL außen vor und das gleiche gilt für den Import der Testdaten: Diese liegen in einer mySQL-Datenbank mit einem praxistauglichen Normalisierungsgrad vor und müssen für einen echten Vergleich jetzt in beide NoSQL-Lösungen übernommen werden.
Basis ist - wie schon im ersten Teil geschrieben - eine Tabelle mit knapp 300.000 Einträgen. Sie hat 62 Spalten, die alle Arten von Daten (VARCHAR, Zahlen, ENUM und Datum/Uhrzeit) enthalten. 20 verschiedene Indices sollen Abfragen beschleunigen.
In drei weiteren Tabellen stehen als One-to-Many Relation manchmal gar keine, manchmal auch mehrere Einträge mit Zusatzdaten, die sich auf die Haupttabelle beziehen. In zwei der Zusatztabellen stehen jeweils etwa 100.000 Datensätze, in der dritten sind es knapp 15 Mio., aber längst nicht alle Einträge haben auch passende Zeilen in der Haupttabelle. Das kommt zwar auch im Livesystem gelegentlich vor, ist aber hauptsächlich den Testdaten geschuldet. Trotzdem sehe ich die Daten als repräsentativ an.
Im Livesystem existieren mehrere Instanzen dieser Daten. Einige haben nur 50.000, andere bis zu 11 Millionen Datensätze in der Haupttabelle, allerdings steht dort auch wesentlich mehr Hardware zur Verfügung, als für meinen Test. Auch dort gibt es Einträge ohne passende Zeilen in den Zusatztabellen und Extremfälle mit teilweilse mehreren hundert verknüpften Datensätzen.
Für den Import in MongoDB und ElasticSearch werde ich zunächst die Haupttabelle vom mySQL-Server lesen, um die weiteren Daten anreichern und dann möglichst in Paketen aus mehreren Dokumenten in den NoSQL-Systemen ablegen. Alle Spalten der Haupttabelle werden dabei zu Schlüsseln im NoSQL-Dokument. Die verknüpften Tabellenzeilen werden um die zur Zuordnung in mySQL gespeicherten Referenz auf den Primärschlüssel der Haupttabelle bereinigt und dann in einem Array pro Zusatztabelle abgelegt. Jeder Array-Eintrag ist wieder ein Key-/Value Hash mit Spaltennamen und Werten der entsprechenden Zusatztabelle.
MySQL-Export
Für die Nutzung des mySQL-Clusters und Workarounds für ein paar mySQL-Bugs existieren in diesem Projekt bereits verschiedene, auf DBI und DBD::mySQL aufsetzende, Module mit insgesamt 2355 Zeilen Quellcode.
#!/usr/bin/perl
$|=1;
use strict;
use warnings;
use Search::Elasticsearch;
use MyProject::DB; # mySQL Modul
use MyProject::Environment; # Projekt-Umgebung
my $dbh_local = MyProject::DB::connect_db('main') or die 'Error connecting to main DB';
# Statement-Handles zum Lesen der Daten
my $sth_main = $dbh_local->prepare('SELECT * FROM MAIN_TABLE WHERE id > ? ORDER BY id ASC LIMIT 100');
my $sth_sub1 = $dbh_local->prepare('SELECT * FROM SUB_TABLE1 WHERE main_id=?');
my $sth_sub2 = $dbh_local->prepare('SELECT * FROM SUB_TABLE2 WHERE main_id=?');
my $sth_sub3 = $dbh_local->prepare('SELECT * FROM SUB_TABLE3 WHERE main_id=?');
# Alle MAIN_TABLE-Einträge in 100er Paketen lesen
my $max_id = 0;
while ($sth_main->execute($max_id) > 0) {
print "Got ".$sth_main->rows." rows with id > $max_id \r";
while (my $doc = $sth_main->fetchrow_hashref) {
$max_id = $doc->{id} if $doc->{id} > $max_id;
# Verknüpfte Informationen anreichern
if ($sth_sub1->execute($doc->{id}) > 0) {
while (my $row = $sth_sub1->fetchrow_hashref) {
delete $row->{main_id}; # Referenzfeld wird nicht mehr benötigt
push @{$doc->{sub1}}, $row;
}
}
if ($sth_sub2->execute($doc->{id}) > 0) {
while (my $row = $sth_sub2->fetchrow_hashref) {
delete $row->{main_id}; # Referenzfeld wird nicht mehr benötigt
push @{$doc->{sub2}}, $row;
}
}
# Verknüpfte Informationen anreichern
if ($sth_sub3->execute($doc->{id}) > 0) {
while (my $row = $sth_sub3->fetchrow_hashref) {
delete $row->{main_id}; # Referenzfeld wird nicht mehr benötigt
push @{$doc->{sub3}}, $row;
}
}
Der Export könnte effizienter bzw. datenbankschonender erfolgen, allerdings sind weder Zeit noch Geschwindigkeit bei diesem Schritt relevante Größen und mit der Entwicklungsdatenbank muss ich nicht ganz so schonend umgehen, wie mit den Livesystemen.
Die id-Primärschlüssel-Spalten der Zusatztabellen werden z.T. von anderen Projekten referenziert, deswegen belasse ich sie im Dokument.
ElasticSearch
Die Verbindung zum ElasticSearch-Cluster gestaltet sich erfreulich einfach, unabhängig davon ob nur eine Node oder ein voll ausgebauter Cluster eingesetzt werden:
my $dbh_es = Search::Elasticsearch->new(
nodes => '10.53.125.33:9200', # Eine, im Testsystem die einzige, Cluster-Node
cxn_pool => 'Sniff', # Weitere Nodes sollen automatisch erkannt werden
) or die 'Error connecting to ElasticSearch';
# Handle für Bulk-Inserts
my $es_bulk = $dbh_es->bulk_helper(
index => instance().'.test',
type => 'main',
) or die 'Error creating ES bulk handler';
Der Client braucht nur eine beliebige Node um alle anderen Nodes des Clusters zu finden. Im Produktiveinsatz würde ich selbstverständlich zumindest mehrere Nodes angeben, ansonsten wäre der Client aufgeschmissen, wenn genau die initial angegebene Node beim Scriptstart nicht verfügbar ist.
Die instance()-Funktion ist übrigens Teil des Projekt-Frameworks. Die einzelnen Instanzen arbeiten unabhängig voneinander, könnten sich aber live problemlos den gleichen ElasticSearch-Cluster teilen.
Zum Schreiben werden dem oben erstellten Bulk-Handle einfach die zu speichernden Dokumente übergeben. Da ich diese später mit einem neuen Import überschreiben können möchte, wird die bestehende id auch zur neuen id.
$es_bulk->index({
id => $doc->{id},
source => $doc,
}) or die "Error indexing ".$doc->{id}." into ElasticSearch";
Ohne weitere Parameter schreibt der Bulk-Handle die neuen Dokumente in Pakete zu 1000 oder sobald sich ein Megabyte an Daten angesammelt hat. Nach dem letzten Datensatz muss $es_bulk->flush aufgerufen werden, um die letzten verbliebenen Daten zu schreiben. Eigentlich schade, ein DESTROY bzw. DEMOLISH im Bulk-Handler-Modul könnte das automatisiert erledigen.
MongoDB
Die Verbindung zum MongoDB-Server ist nur wenig aufwendiger als zu ElasticSearch. Anstatt Datenbank und Collection im Bulk-Handler anzugeben, werden für beide eigene Objekte erstellt. Ein ElasticSearch Index entspricht eher einer MongoDB Collection als einer Datenbank, weil beide für einen dedizierten Datenspeicher stehen. Der Type von ElasticSearch ordnet zwar Dokumente innerhalb des Index, trennt diese aber nicht auf der Serverfestplatte.
my $dbh_mongo = MongoDB::Connection->new(
host => '10.53.125.33',
) or die 'Error connecting to MongoDB';
my $mongo_db = $dbh_mongo->get_database(instance()) or die 'Error accessing MongoDB database';
my $mongo_collection = $mongo_db->get_collection('main') or die 'Error accessing MongoDB collection';
my $mongo_bulk = $mongo_collection->initialize_unordered_bulk_op or die 'Error creating MongoDB bulk op';
Den Bulk-Insert von MongoDB habe ich bisher noch nie benutzt, aber der Aufruf ist nicht sonderlich kompliziert:
$doc->{_id} = $doc->{id}; # Set MongoDB id key
$mongo_bulk->insert($doc);
Leider fehlt die Fähigkeit, bei einer gewissen Bulk-Größe selbstständig die Schreibvorgänge auszuführen und jeder Bulk-Handle kann nur einmal benutzt werden. Deswegen wird ->execute nach jedem gelesenen 100er Block aufgerufen und ein neuer Bulk-Handle erstellt:
$mongo_bulk->execute;
$mongo_bulk = $mongo_collection->initialize_unordered_bulk_op;
Leider funktioniert das nur in der Theorie. In der Praxis bricht das Script mit der Meldung writeErrors: 100 ab, sobald der erste ->execute aufgerufen wird. Der Server meinte in seinem /var/log/mongodb/mongodb.log dazu:
[conn2] insert instance.main query: { ... } ninserted:1 keyUpdates:0 numYields:0 locks(micros) w:188812 188ms
[conn2] command instance.$cmd command: insert { $msg: "query not recording (too large)" } keyUpdates:0 numYields:0 locks(micros) w:114 reslen:40 196ms
Selbst mit 10 Elementen war der Bulk-Request zu groß, obwohl die 16 MB Grenze eigentlich nicht erreicht sein kann. Als einziger Ausweg bleibt die Rückkehr zum einzelnen Schreiben jedes Dokumentes:
$doc->{_id} = $doc->{pk}; # Set MongoDB id key
$mongo_collection->save($doc);
Das erste Rennen
Zusätzlich habe ich noch ein paar Zeilen hinzugefügt, um die benötigte Zeit der beiden NoSQL-Systeme zu messen. Der Vergleich ist nicht ganz richtig, weil MongoDB die Schreiboperation als "fire&forget" ausführt, also nicht wartet, bis die Daten auf der Serverfestplatte angekommen sind. Beide sollen als Suchmaschine und nicht als Datenbank genutzt werden - wenn einzelne Datensätze verloren gehen, ist das verschmerzbar. Ein solcher Verlust ist ohnehin ziemlich unwahrscheinlich. ElasticSearch bietet diese Möglichkeit nicht, also darf MongoDB diesen Vorteil für sich verbuchen.
Dennoch gewinnt ElasticSearch klar: 140 Sekunden braucht die Suchmaschine um knapp 300.000 Einträge zu speichern während MongoDB sich 386 Sekunden Zeit lässt. MongoDB hat alle Dokumente gespeichert, ElasticSearch hat zwei wegen falschen Datumsangaben abgelehnt:
Bulk error [index]: {"_index":"instance.main","status":400,"_id":"21643529","error":"MapperParsingException[failed to parse [dateofbirth]]; nested: MapperParsingException[failed to parse date field [0000-00-00], tried both date format [dateOptionalTime], and timestamp number with locale []]; nested: IllegalFieldValueException[Cannot parse \"0000-00-00\": Value 0 for monthOfYear must be in the range [1,12]]; ","_type":"client"} at /usr/local/share/perl/5.10.1/Search/Elasticsearch/Role/Bulk.pm line 52.
Bulk error [index]: {"_index":"instance.main","status":400,"_id":"21774890","error":"MapperParsingException[failed to parse [dateofbirth]]; nested: MapperParsingException[failed to parse date field [0000-00-00], tried both date format [dateOptionalTime], and timestamp number with locale []]; nested: IllegalFieldValueException[Cannot parse \"0000-00-00\": Value 0 for monthOfYear must be in the range [1,12]]; ","_type":"client"} at /usr/local/share/perl/5.10.1/Search/Elasticsearch/Role/Bulk.pm line 52.
MongoDB hat die Installtion gewonnen, ElasticSearch das Schreiben. Beides war aber nur Vorspiel, spannend wird es beim Selektionstest in der nächsten Folge.
Noch keine Kommentare. Schreib was dazu