Comment charger rapidement des données dans la base de données ?

Chez notre dernier client, nous avons vraiment dû résoudre quelques questions passionnantes ! Notamment parce que la base de données n’était pas vraiment petite.

Voici quelques données clés : CPU : 2 sockets x 24 cœurs x 2 threads = 96 vCores, 756 G RAM, 2 x 10 To SSD PCIe en RAID-10 et 7 To de données, plusieurs milliers de clients, en forte croissance.

Débit actuel : 1 M SELECT/min, 56 k INSERT/min, 44 k UPDATE/min , 7 k DELETE/min en moyenne sur 30 jours. Tendance en forte hausse. Application et requêtes non optimisées de manière cohérente. Configuration de la base de données : « à la pointe de la technologie », non vérifiée par des benchmarks. Utilisation du CPU d’environ 50 % en moyenne, avec des pics supérieurs. Le système d’E/S dispose encore de capacités libres.

Le client collecte des données de position et d’autres données relatives aux appareils et les stocke dans la base de données. Il s’agit donc d’un problème IoT classique (avec séries chronologiques, table indexée en grappes, etc.).

La question qu’il a posée est la suivante : comment copier le plus rapidement possible des données d’une table (données en attente, une sorte de file d’attente) vers une autre table (données finales, par client) ?

Le flux de données se présente comme suit :

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

Trois variantes différentes pour copier les données étaient disponibles.

Variante 1: INSERT et DELETE (forme la plus simple)

La variante la plus simple est INSERT et DELETE. Cette variante est particulièrement problématique car MariaDB/MySQL et PostgreSQL ont activé AUTOCOMMIT par défaut (ici, ici, ici et ici).

Pour mieux comprendre, voici un exemple de pseudocode :

// 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
  }
}

Si nous voulons copier 20 000 lignes, cette variante génère 40 000 COMMIT (fsync) et 42 000 allers-retours réseau !

Variante 2: START TRANSACTION et INSERT et DELETE

Cette variante est utilisée par les développeurs de bases de données plus expérimentés. Voici le pseudocode correspondant :

// 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;
}

Si nous voulons copier 20 000 lignes dans cet exemple, cette variante ne génère plus que 2 000 COMMIT (fsync) ! Soit 20 fois moins ! Mais en contrepartie, 46 000 allers-retours réseau (10 % de plus).

Variante 3: START TRANSACTION et INSERT et DELETE optimisés

Cette variante est un peu plus exigeante sur le plan technique. Elle est utilisée lorsque l’on souhaite se rapprocher un peu plus des limites du possible. Voici le pseudocode correspondant :

// 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;
}

Cette troisième variante ne génère également que 2 k COMMIT (fsync) pour 20 k lignes, mais évite la boucle via les instructions INSERT et DELETE dans la base de données. Nous économisons ainsi d’une part les allers-retours réseau (plus que 10 k) et les cycles CPU sur la base de données (qui sont difficilement évolutifs) pour analyser les requêtes.

Configuration du test

Pour tester le tout, nous avons préparé un petit script : load_data.php

Préparation et exécution avec MariaDB/MySQL

Vous pouvez effectuer vous-même ces tests à l’aide des commandes suivantes :

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

Chacun peut obtenir les valeurs mesurées à l’aide du script de test correspondant.

Préparation et exécution avec PostgreSQL

Vous pouvez effectuer ces tests vous-même à l’aide des commandes suivantes :

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

Chacun peut obtenir les valeurs mesurées à l’aide du script de test correspondant.

Résultats

Afin d’éviter toute discussion inutile, nous n’avons indiqué ici « que » les performances relatives (durée d’exécution), comme le fait MarkC ces derniers temps. Nos valeurs mesurées peuvent être mises à disposition de manière bilatérale. Elles peuvent toutefois être facilement reproduites à l’aide du script de test.

Moins, c’est mieux :

**Attention ** : les valeurs de MariaDB/MySQL et PostgreSQL ne peuvent PAS être comparées directement !

Et voici l’évaluation graphique :

Remarques

Avec des disques plus rapides, la différence entre 1 et 2/3 n’aurait probablement pas été aussi importante. Les tests effectués chez le client n’ont montré « qu’un » facteur 5 (au lieu d’un facteur 9 à 16) de différence.

Il existe certainement d’autres possibilités pour optimiser encore ce processus de chargement. Voici celles qui me viennent à l’esprit :

• INSERT INTO ... SELECT * FROM
• LOAD DATA INFILE/COPY, si possible
• Prepared Statements
• Server side Stored Language (SQL/PSM, PL/pgSQL, …) :-(
• PDO-Fetch des résultats ?
• etc.

Je devrais peut-être me procurer un profileur (Xdebug ou xhprof) ?

Autres articles

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

Cette page a été traduite à l’aide de deepl.com.