Wie lädt man Daten am schnellsten in die Datenbank?

Beim letzten Kunden hatten wir wirklich ein paar spannende Fragen zu lösen! Insbesondere auch, weil die Datenbank nicht ganz klein war.

Hier kurz einige Eckdaten: CPU: 2 Sockel x 24 Kerne x 2 Threads = 96 vCores, 756 G RAM, 2 x 10 Tbyte PCIe SSD im RAID-10 und 7 Tbyte Daten, einige Tausend Mandanten, stark wachsend.

Der aktuelle Durchsatz: 1 M SELECT/min, 56 k INSERT/min, 44 k UPDATE/min , 7 k DELETE/min gemittelt über 30 Tage. Tendenz stark steigend. Applikation und Queries nicht durchgängig optimiert. Datenbank Konfiguration: “state of the art” nicht mit Benchmarks verifiziert. CPU-Auslastung ca. 50% im Schnitt, in der Spitze mehr. I/O System hat noch Luft nach oben.

Der Kunde sammelt Positions- und sonstige Gerätedaten ein und speichert diese in der Datenbank. Also ein klassisches IoT Problem (mit Zeitreihen, Index Clustered Table, etc.).

Die Frage, die er gestellt hat, ist: Wie kriege ich am schnellsten Daten von einer Tabelle (pending data, eine Art Queue) in eine andere Tabelle (final data, pro Mandant) kopiert.

Der Datenfluss sieht in etwa wie folgt aus:

+------------+
| IoT Device |--+
+------------+   \
                  \     +-----+
+------------+     \    | AS  |   +--------------+  Processing  +------------+
| IoT Device |------+-->|     |-->| Pending data |------------->| Final data |
+------------+     /    | 400 |   +--------------+   of data    +------------+
                  /     +-----+
+------------+   /
| IoT Device |--+
+------------+

3 verschiedene Varianten, die Daten zu kopieren, standen zur Auswahl.

Variante 1: INSERT und DELETE (einfachste Form)

Die einfachste Variante ist simples INSERT und DELETE. Diese Variante ist insbesondere darum problematisch, weil MariaDB/MySQL und PostgreSQL per default AUTOCOMMIT eingeschaltet haben (hier, hier, hier und hier).

Damit man sich das ein bisschen besser vorstellen kann, hier etwas Pseudocode dazu:

// 20k rows
for (i = 1; i <= 2000; i++) {

  SELECT * FROM pending LIMIT 10;
  foreach ( row ) {
    INSERT INTO final;
    -- implicit COMMIT
    DELETE FROM pending WHERE id = row[id];
    -- implicit COMMIT
  }
}

Wenn wir also 20 k Zeilen umkopieren wollen, verursacht diese Variante: 40 k COMMITs (fsync) und 42 k Netzwerk-Roundtrips!

Variante 2: START TRANSACTION und INSERT und DELETE

Diese Variante wird von versierteren Datenbank-Entwicklern verwendet. Hier der passende Pseudocode dazu:

// 20k rows
for (i = 1; i <= 2000; i++) {

  SELECT * FROM pending LIMIT 10;
  START TRANSACTION;
  foreach ( row ) {
    INSERT INTO final;
    DELETE FROM pending WHERE id = row[id];
  }
  COMMIT;
}

Wenn wir in diesem Beispiel 20 k Zeilen umkopieren wollen verursacht diese Variante nur noch 2 k COMMITs (fsync)! Also 20 mal weniger! Aber dafür 46 k Netzwerk-Roundtrips (10% mehr).

Variante 3: START TRANSACTION und optimiertes INSERT und DELETE

Diese Variante ist programmiertechnisch ein klein wenig anspruchsvoller. Sie wird verwendet, wenn man den Grenzen des Möglich etwas näher kommen will. Hier der Pseudocode dazu:

// 20k rows
for (i = 1; i <= 2000; i++) {

  SELECT * FROM pending LIMIT 10;
  START TRANSACTION;
  INSERT INTO final (), (), (), (), (), (), (), (), (), ();
  DELETE FROM pending WHERE id = IN (...);
  COMMIT;
}

Und diese 3. Variante verursacht bei 20 k Zeilen ebenfalls nur noch 2 k COMMIT’s (fsync), spart sich aber die Schleife über die INSERT und DELETE Statements in der Datenbank. Wir sparen also einerseits Netzwerk Round-Trips (nur noch 10 k) und CPU-Cycles auf der Datenbank (welche sich schlecht skalieren lassen) zum Parsen der Queries.

Test Set-up

Um das Ganze zu testen haben wir ein kleines Script vorbereitet: load_data.php

Vorbereitung und Ausführung mit MariaDB/MySQL

Mit folgenden Befehlen kann man diese Tests selber ausführen:

SQL> CREATE DATABASE test;
SQL> CREATE USER 'app'@'127.0.0.1' IDENTIFIED BY 'secret';
SQL> GRANT ALL ON *.* TO 'app'@'127.0.0.1';

$ ./load_data.php --database-type=mysql --database=test --host=127.0.0.1 --port=3306 --user=app --password=secret --prepare

$ for i in $(seq 5) ; do
  ./load_data.php --database-type=mysql --database=test --host=127.0.0.1 --port=3306 --user=app --password=secret --run --variant=1
  ./load_data.php --database-type=mysql --database=test --host=127.0.0.1 --port=3306 --user=app --password=secret --run --variant=2
  ./load_data.php --database-type=mysql --database=test --host=127.0.0.1 --port=3306 --user=app --password=secret --run --variant=3
done

$ ./load_data.php --database-type=mysql --database=test --host=127.0.0.1 --port=3306 --user=app --password=secret --clean-up

Die Messwerte kann sich jeder selbst mit dem entsprechenden Test-Skript erarbeiten.

Vorbereitung und Ausführung mit PostgreSQL

Mit folgenden Befehlen kann man diese Tests selber ausführen:

postgres# CREATE DATABASE test;
postgres# CREATE USER app PASSWORD 'secret';
postgres# GRANT ALL ON DATABASE test TO app;
postgres# GRANT ALL ON SCHEMA public TO app;

$ ./load_data.php --database-type=postgresql --database=test --host=127.0.0.1 --port=5432 --user=app --password=secret --prepare

$ for i in $(seq 5) ; do
  ./load_data.php --database-type=postgresql --database=test --host=127.0.0.1 --port=5432 --user=app --password=secret --run --variant=1
  ./load_data.php --database-type=postgresql --database=test --host=127.0.0.1 --port=5432 --user=app --password=secret --run --variant=2
  ./load_data.php --database-type=postgresql --database=test --host=127.0.0.1 --port=5432 --user=app --password=secret --run --variant=3
done

$ ./load_data.php --database-type=postgresql --database=test --host=127.0.0.1 --port=5432 --user=app --password=secret --clean-up

Die Messwerte kann sich jeder selbst mit dem entsprechenden Test-Skript erarbeiten.

Resultate

Um unnötige Diskussionen zu vermeiden, haben wir hier “nur” die relative Performance (Laufzeit) aufgeführt, wie es MarkC in letzter Zeit auch macht. Unsere Messwerte können gerne bilateral zur Verfügung stellen. Sie lassen sich aber einfach mit dem Test-Skript selber reproduzieren.

Weniger ist besser:

Achtung: Die Werte von MariaDB/MySQL und PostgreSQL lassen sich NICHT direkt vergleichen!

Und hier noch die graphische Auswertung:

Bemerkungen

Mit schnelleren Disks, wäre der Unterschied zwischen 1 und 2/3 wahrscheinlich nicht mehr ganz so gravierend ausgefallen. Tests beim Kunden haben “nur” ca. Faktor 5 (statt Faktor 9 bis 16) unterschied gezeigt.

Es gibt sicher weitere Möglichkeiten, wie man diesen Lade-Vorgang noch optimieren kann. Hier kommen mir in den Sinn:

• INSERT INTO ... SELECT * FROM
• LOAD DATA INFILE/COPY, wenn möglich
• Prepared Statements
• Server side Stored Language (SQL/PSM, PL/pgSQL, …) :-(
• PDO-Fetch der Resultate?
• etc.

Vielleicht sollte ich mich mal um einen Profiler kümmern (Xdebug oder xhprof)?

Weitere Beiträge

• Load CSV files into the database
• MariaDB Prepared Statements, Transactions and Multi-Row Inserts
• How good is MySQL INSERT TRIGGER performance