Qual è il modo più veloce per caricare dati nel database?

Con l’ultimo cliente abbiamo dovuto risolvere alcune questioni davvero interessanti! Soprattutto perché il database non era proprio piccolo.

Ecco alcuni dati chiave: CPU: 2 socket x 24 core x 2 thread = 96 vCore, 756 G RAM, 2 x 10 Tbyte PCIe SSD in RAID-10 e 7 Tbyte di dati, diverse migliaia di clienti, in forte crescita.

Il throughput attuale: 1 M SELECT/min, 56 k INSERT/min, 44 k UPDATE/min , 7 k DELETE/min in media su 30 giorni. Tendenza in forte aumento. Applicazione e query non ottimizzate in modo coerente. Configurazione del database: “state of the art” non verificata con benchmark. Utilizzo della CPU circa il 50% in media, con picchi superiori. Il sistema I/O ha ancora margini di miglioramento.

Il cliente raccoglie dati di posizione e altri dati relativi ai dispositivi e li memorizza nel database. Si tratta quindi di un classico problema IoT (con serie temporali, tabella clusterizzata per indice, ecc.).

La domanda che ha posto è: qual è il modo più veloce per copiare i dati da una tabella (dati in sospeso, una sorta di coda) a un’altra tabella (dati finali, per cliente)?

Il flusso di dati è più o meno il seguente:

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

Erano disponibili 3 diverse varianti per copiare i dati.

Variante 1: INSERT e DELETE (forma più semplice)

La variante più semplice è INSERT e DELETE. Questa variante è particolarmente problematica perché MariaDB/MySQL e PostgreSQL hanno AUTOCOMMIT attivato di default (qui, qui, qui et qui).

Per chiarire meglio il concetto, ecco un po’ di pseudocodice:

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

Se vogliamo copiare 20 k righe, questa variante causa: 40 k COMMIT (fsync) e 42 k roundtrip di rete!

Variante 2: START TRANSACTION e INSERT e DELETE

Questa variante viene utilizzata dagli sviluppatori di database più esperti. Ecco il codice pseudocodice corrispondente:

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

Se in questo esempio vogliamo copiare 20 k righe, questa variante genera solo 2 k COMMIT (fsync)! Quindi 20 volte meno! Ma in cambio 46 k roundtrip di rete (10% in più).

Variante 3: START TRANSACTION e INSERT e DELETE ottimizzati

Questa variante è leggermente più complessa dal punto di vista della programmazione. Viene utilizzata quando si desidera avvicinarsi il più possibile ai limiti del possibile. Ecco il pseudocodice corrispondente:

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

Anche questa terza variante genera solo 2 k COMMIT (fsync) con 20 k righe, ma evita il ciclo delle istruzioni INSERT e DELETE nel database. Da un lato risparmiamo roundtrip di rete (solo 10 k) e cicli CPU sul database (che sono difficili da scalare) per l’analisi delle query.

Configurazione del test

Per testare il tutto, abbiamo preparato un piccolo script: load_data.php

Preparazione ed esecuzione con MariaDB/MySQL

Con i seguenti comandi è possibile eseguire questi test autonomamente:

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

Ognuno può elaborare i valori misurati con lo script di test corrispondente.

Preparazione ed esecuzione con PostgreSQL

È possibile eseguire questi test autonomamente utilizzando i seguenti comandi:

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

Ognuno può elaborare i valori misurati utilizzando lo script di test corrispondente.

Risultati

Per evitare discussioni inutili, abbiamo elencato qui “solo” le prestazioni relative (tempo di esecuzione), come fa ultimamente anche MarkC. I nostri valori misurati possono essere messi a disposizione bilateralmente. Tuttavia, possono essere facilmente riprodotti con lo script di test.

Meno è meglio:

Attenzione: i valori di MariaDB/MySQL e PostgreSQL NON sono direttamente comparabili!

Ed ecco la valutazione grafica:

Osservazioni

Con dischi più veloci, la differenza tra 1 e 2/3 probabilmente non sarebbe stata così significativa. I test effettuati presso il cliente hanno mostrato “solo” una differenza di circa 5 volte (anziché da 9 a 16 volte).

Ci sono sicuramente altri modi per ottimizzare ulteriormente questo processo di caricamento. Mi vengono in mente:

• INSERT INTO ... SELECT * FROM
• LOAD DATA INFILE/COPY, se possibile
• Prepared Statements
• Server side Stored Language (SQL/PSM, PL/pgSQL, …) :-(
• PDO-Fetch dei risultati?
• etc.

Forse dovrei procurarmi un profiler (Xdebug o xhprof)?

Altri contributi

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

Questa pagina è stata tradotta utilizzando deepl.com.