¿Por qué SQLite es tan lento (~ 2 q / s) en una máquina específica?

En uno de mis servidores (i7 Core Ivy, 32 GB RAM, Debian 6 @ 64 bits, PHP 5.4.10) experimento insertos extremadamente lentos con SQLite. El siguiente progtwig de prueba informa solo 2.2 insertos / segundo (14 segundos para insertar 30 filas).

unlink("test.db"); $db = new PDO('sqlite:test.db'); $db->exec("CREATE TABLE test (dummy INT)"); $count = 30; $t = microtime(true); for ($i=0; $iexec("INSERT INTO test VALUES ($i)") or die("SQLite error: ".$db->errorInfo()[2]."\n"); } $elapsed = microtime(true)-$t; echo sprintf("%d inserts in %.3f secs (%.1f q/s)\n", $count, $elapsed, $count/$elapsed); 

Salida:

 $ time php test.php 30 inserts in 13.911 secs (2.2 q/s) real 0m14.634s user 0m0.004s sys 0m0.016s 

Sé que puedo acelerar esto envolviendo BEGIN / END alrededor de las instrucciones de inserción (que me da 200000 q / s), pero incluso sin una transacción, esto debería ser mucho más rápido. En otras máquinas (más antiguas) (misma versión de PHP) alcanzo más de 300 consultas / seg sin transacciones explícitas.

¿Cuál podría ser la causa de esto? ¿Debo sintonizar Sqlite o el O / S?

Hice una prueba similar en una máquina Linux de 64 bits usando strace -C -tt para tener una idea de dónde está tomando SQLite3.

 % time seconds usecs/call calls errors syscall ------ ----------- ----------- --------- --------- ---------------- 99.03 0.004000 32 124 fsync 0.64 0.000026 0 222 mprotect 0.32 0.000013 0 216 munmap 

El retraso obvio está en la función fsync , que es:

  • configurable
  • depende de la E / S de disco general (consulte iotop , iostat )
  • depende en gran medida de IOSS (por lo tanto, el sistema de archivos y la asignación de disco: puede obtener un valor en ext3, uno diferente en xfs y un tercero en btrfs)
  • depende, por supuesto, indirectamente, del hardware subyacente y sus peculiaridades o afinaciones.

Al desactivar la sincronización, mi rendimiento de SQLite3 aumenta en un factor de alrededor de tres mil:

 $db = new PDO('sqlite:test.db'); $db->exec('pragma synchronous = off;'); 

También tengo dos valores diferentes en dos máquinas muy similares (una tiene ext4, la otra XFS, pero no estoy seguro de que esta sea la razón principal, sus perfiles de carga también son diferentes).

Por cierto, usar declaraciones preparadas casi duplica la velocidad de ejecución al nivel más rápido (de 45k a 110k INSERTs, en lotes de 3000 ya que a esa velocidad 30 INSERTs están destinados a dar tiempos espurios), y aumenta la velocidad más baja desde aproximadamente 6 a alrededor de 150.

Por lo tanto, esto (usando declaraciones preparadas) podría ser una buena solución para mejorar las operaciones repetidas sin tocar la sincronización de archivos, es decir, sin dejar de ser demostrablemente seguro de que el nivel de riesgo de los datos sigue siendo el mismo. Después de eso probaría transacciones o fsync (tal vez incluso el diario de memoria) dependiendo del riesgo y el valor de un corte de datos.

Cuando se diseña un sistema desde cero, seguramente se recomiendan algunas pruebas en diferentes FS.

Pruebas en diferentes sistemas de archivos (misma máquina)

 ext4 (acl,user_xattr,data=order) 5.5 queries/s using transactions 170 queries/s disabling fsync 16000 queries/s using transactions and disabling fsync 47200 queries/s 

En un sistema de archivos temporal , fsync es barato, por lo que apagarlo rinde poco beneficio. La mayor parte del tiempo se dedica a la vigilancia, por lo que las transacciones son clave.

 tmpfs 13700 queries/s disabling fsync 15350 queries/s enabling transactions 47900 queries/s using transactions and disabling fsync 48200 queries/s 

Por supuesto, debe tenerse en cuenta una organización e indexación de datos adecuada y, para grandes conjuntos de datos, podría resultar ser más importante.


ACTUALIZACIÓN : para exprimir un poco más de rendimiento, también se puede poner el diario SQLite en la memoria con pragma journal_mode=MEMORY;