www pagina org oficial mac database sqlite optimization sqlite3 insert

database - pagina - sqlite website



La velocidad de inserción de SQLite disminuye a medida que aumenta el número de registros debido a un índice (5)

Pregunta original

Fondo

Es bien sabido que SQLite necesita ser ajustado para lograr velocidades de inserción del orden de 50k inserts / s. Aquí hay muchas preguntas sobre velocidades de inserción lentas y una gran cantidad de consejos y puntos de referencia.

También se afirma que SQLite puede manejar grandes cantidades de datos , con informes de más de 50 GB que no causan problemas con la configuración correcta.

He seguido los consejos aquí y en otros lugares para lograr estas velocidades y estoy contento con las inserciones de 35k-45k. El problema que tengo es que todos los puntos de referencia solo demuestran rápidas velocidades de inserción con <1m de registros. Lo que estoy viendo es que la velocidad de inserción parece ser inversamente proporcional al tamaño de la mesa .

Problema

Mi caso de uso requiere almacenar tuplas de 500m a 1b ( [x_id, y_id, z_id] ) durante algunos años (1m filas / día) en una tabla de enlaces. Los valores son todos los ID enteros entre 1 y 2,000,000. Hay un solo índice en z_id .

El rendimiento es excelente para las primeras 10m filas, ~ 35k inserts / s, pero cuando la tabla tiene ~ 20m filas, el rendimiento comienza a sufrir. Ahora estoy viendo alrededor de 100 inserts / s.

El tamaño de la mesa no es particularmente grande. Con 20m filas, el tamaño en el disco es de alrededor de 500MB.

El proyecto está escrito en Perl.

Pregunta

¿Es esta la realidad de las tablas grandes en SQLite o hay algún secreto para mantener altas tasas de inserción para tablas con> 10m filas?

Soluciones conocidas que me gustaría evitar si es posible

  • Suelte el índice, agregue los registros y vuelva a indexar : esto está bien como solución alternativa, pero no funciona cuando la base de datos aún necesita ser utilizada durante las actualizaciones. No funcionará hacer que la base de datos sea completamente inaccesible por x minutos / día
  • Divida la tabla en subtablas / archivos más pequeños : esto funcionará a corto plazo y ya he experimentado con él. El problema es que necesito poder recuperar datos de todo el historial al realizar consultas, lo que significa que eventualmente alcanzaré el límite de 62 tablas adjuntas. Adjuntar, recopilar resultados en una tabla temporal y separar cientos de veces por solicitud parece requerir mucho trabajo y mucho trabajo, pero lo intentaré si no hay otras alternativas.
  • Establecer SQLITE_FCNTL_CHUNK_SIZE : No sé C (?!), Por lo que preferiría no aprenderlo solo para hacer esto. Sin embargo, no veo ninguna manera de establecer este parámetro usando Perl.

ACTUALIZAR

Siguiendo la sugerencia de Tim de que un índice estaba causando tiempos de inserción cada vez más lentos a pesar de las afirmaciones de SQLite de que es capaz de manejar grandes conjuntos de datos, realicé una comparación de referencia con la siguiente configuración:

  • filas insertadas: 14 millones
  • confirmar el tamaño del lote: 50,000 registros
  • cache_size pragma: 10,000
  • page_size pragma: 4,096
  • temp_store pragma: memoria
  • journal_mode pragma: borrar
  • pragma synchronous : desactivado

En mi proyecto, como en los resultados de referencia a continuación, se crea una tabla temporal basada en archivos y se usa el soporte integrado de SQLite para importar datos CSV. La tabla temporal se adjunta a la base de datos de recepción y se insertan conjuntos de 50,000 filas con una instrucción de insert-select . Por lo tanto, los tiempos de inserción no reflejan los tiempos de inserción de archivo a base de datos , sino la velocidad de inserción de tabla a tabla . Teniendo en cuenta el tiempo de importación de CSV se reducirían las velocidades en un 25-50% (una estimación muy aproximada, no lleva mucho importar los datos CSV).

Claramente tener un índice causa la desaceleración en la velocidad de inserción a medida que aumenta el tamaño de la tabla.

Es bastante claro a partir de los datos anteriores que la respuesta correcta se puede asignar a la respuesta de Tim en lugar de las afirmaciones de que SQLite simplemente no puede manejarlo. Claramente puede manejar grandes conjuntos de datos si la indexación de ese dataset no es parte de su caso de uso. He estado usando SQLite para eso, como un backend para un sistema de registro, desde hace un tiempo que no necesita ser indexado, así que estaba bastante sorprendido por la desaceleración que experimenté.

Conclusión

Si alguien se encuentra con el deseo de almacenar una gran cantidad de datos usando SQLite y hacer que se indexe, el uso de fragmentos puede ser la respuesta. Finalmente decidí usar los primeros tres caracteres de un hash MD5 en una columna única en z para determinar la asignación a una de las 4,096 bases de datos. Dado que mi caso de uso es principalmente de archivo por naturaleza, el esquema no cambiará y las consultas nunca requerirán caminar en fragmentos. Hay un límite en el tamaño de la base de datos ya que los datos extremadamente antiguos se reducirán y eventualmente se descartarán, así que esta combinación de fragmentación, configuración de pragma e incluso cierta normalización me da un buen equilibrio que, de acuerdo con la evaluación comparativa anterior, mantendrá una velocidad de inserción de al menos 10k inserciones / segundo.

¡Gran pregunta y seguimiento muy interesante!

Simplemente me gustaría hacer una observación rápida: mencionó que dividir la tabla en subtablas / archivos más pequeños y adjuntarlos más tarde no es una opción porque alcanzará rápidamente el límite estricto de 62 bases de datos adjuntas. Si bien esto es completamente cierto, no creo que haya considerado una opción de mitad de camino: fragmentar los datos en varias tablas, pero seguir usando la misma base de datos única (archivo).

Hice un punto de referencia muy crudo solo para asegurarme de que mi sugerencia realmente tenga un impacto en el rendimiento.

Esquema:

CREATE TABLE IF NOT EXISTS "test_$i" ( "i" integer NOT NULL, "md5" text(32) NOT NULL );

Datos - 2 millones de filas:

  • i = 1..2,000,000
  • md5 = md5 hex digest de i

Cada transacción = 50,000 INSERT s.

Bases de datos: 1; Tablas: 1; Índices: 0

0..50000 records inserted in 1.87 seconds 50000..100000 records inserted in 1.92 seconds 100000..150000 records inserted in 1.97 seconds 150000..200000 records inserted in 1.99 seconds 200000..250000 records inserted in 2.19 seconds 250000..300000 records inserted in 1.94 seconds 300000..350000 records inserted in 1.94 seconds 350000..400000 records inserted in 1.94 seconds 400000..450000 records inserted in 1.94 seconds 450000..500000 records inserted in 2.50 seconds 500000..550000 records inserted in 1.94 seconds 550000..600000 records inserted in 1.94 seconds 600000..650000 records inserted in 1.93 seconds 650000..700000 records inserted in 1.94 seconds 700000..750000 records inserted in 1.94 seconds 750000..800000 records inserted in 1.94 seconds 800000..850000 records inserted in 1.93 seconds 850000..900000 records inserted in 1.95 seconds 900000..950000 records inserted in 1.94 seconds 950000..1000000 records inserted in 1.94 seconds 1000000..1050000 records inserted in 1.95 seconds 1050000..1100000 records inserted in 1.95 seconds 1100000..1150000 records inserted in 1.95 seconds 1150000..1200000 records inserted in 1.95 seconds 1200000..1250000 records inserted in 1.96 seconds 1250000..1300000 records inserted in 1.98 seconds 1300000..1350000 records inserted in 1.95 seconds 1350000..1400000 records inserted in 1.95 seconds 1400000..1450000 records inserted in 1.95 seconds 1450000..1500000 records inserted in 1.95 seconds 1500000..1550000 records inserted in 1.95 seconds 1550000..1600000 records inserted in 1.95 seconds 1600000..1650000 records inserted in 1.95 seconds 1650000..1700000 records inserted in 1.96 seconds 1700000..1750000 records inserted in 1.95 seconds 1750000..1800000 records inserted in 1.95 seconds 1800000..1850000 records inserted in 1.94 seconds 1850000..1900000 records inserted in 1.95 seconds 1900000..1950000 records inserted in 1.95 seconds 1950000..2000000 records inserted in 1.95 seconds

Tamaño del archivo de base de datos: 89.2 MiB.

Bases de datos: 1; Tablas: 1; Índices: 1 ( md5 )

0..50000 records inserted in 2.90 seconds 50000..100000 records inserted in 11.64 seconds 100000..150000 records inserted in 10.85 seconds 150000..200000 records inserted in 10.62 seconds 200000..250000 records inserted in 11.28 seconds 250000..300000 records inserted in 12.09 seconds 300000..350000 records inserted in 10.60 seconds 350000..400000 records inserted in 12.25 seconds 400000..450000 records inserted in 13.83 seconds 450000..500000 records inserted in 14.48 seconds 500000..550000 records inserted in 11.08 seconds 550000..600000 records inserted in 10.72 seconds 600000..650000 records inserted in 14.99 seconds 650000..700000 records inserted in 10.85 seconds 700000..750000 records inserted in 11.25 seconds 750000..800000 records inserted in 17.68 seconds 800000..850000 records inserted in 14.44 seconds 850000..900000 records inserted in 19.46 seconds 900000..950000 records inserted in 16.41 seconds 950000..1000000 records inserted in 22.41 seconds 1000000..1050000 records inserted in 24.68 seconds 1050000..1100000 records inserted in 28.12 seconds 1100000..1150000 records inserted in 26.85 seconds 1150000..1200000 records inserted in 28.57 seconds 1200000..1250000 records inserted in 29.17 seconds 1250000..1300000 records inserted in 36.99 seconds 1300000..1350000 records inserted in 30.66 seconds 1350000..1400000 records inserted in 32.06 seconds 1400000..1450000 records inserted in 33.14 seconds 1450000..1500000 records inserted in 47.74 seconds 1500000..1550000 records inserted in 34.51 seconds 1550000..1600000 records inserted in 39.16 seconds 1600000..1650000 records inserted in 37.69 seconds 1650000..1700000 records inserted in 37.82 seconds 1700000..1750000 records inserted in 41.43 seconds 1750000..1800000 records inserted in 49.58 seconds 1800000..1850000 records inserted in 44.08 seconds 1850000..1900000 records inserted in 57.17 seconds 1900000..1950000 records inserted in 50.04 seconds 1950000..2000000 records inserted in 42.15 seconds

Tamaño del archivo de base de datos: 181.1 MiB.

Bases de datos: 1; Tablas: 20 (una por 100,000 registros); Índices: 1 ( md5 )

0..50000 records inserted in 2.91 seconds 50000..100000 records inserted in 10.30 seconds 100000..150000 records inserted in 10.85 seconds 150000..200000 records inserted in 10.45 seconds 200000..250000 records inserted in 10.11 seconds 250000..300000 records inserted in 11.04 seconds 300000..350000 records inserted in 10.25 seconds 350000..400000 records inserted in 10.36 seconds 400000..450000 records inserted in 11.48 seconds 450000..500000 records inserted in 10.97 seconds 500000..550000 records inserted in 10.86 seconds 550000..600000 records inserted in 10.35 seconds 600000..650000 records inserted in 10.77 seconds 650000..700000 records inserted in 10.62 seconds 700000..750000 records inserted in 10.57 seconds 750000..800000 records inserted in 11.13 seconds 800000..850000 records inserted in 10.44 seconds 850000..900000 records inserted in 10.40 seconds 900000..950000 records inserted in 10.70 seconds 950000..1000000 records inserted in 10.53 seconds 1000000..1050000 records inserted in 10.98 seconds 1050000..1100000 records inserted in 11.56 seconds 1100000..1150000 records inserted in 10.66 seconds 1150000..1200000 records inserted in 10.38 seconds 1200000..1250000 records inserted in 10.24 seconds 1250000..1300000 records inserted in 10.80 seconds 1300000..1350000 records inserted in 10.85 seconds 1350000..1400000 records inserted in 10.46 seconds 1400000..1450000 records inserted in 10.25 seconds 1450000..1500000 records inserted in 10.98 seconds 1500000..1550000 records inserted in 10.15 seconds 1550000..1600000 records inserted in 11.81 seconds 1600000..1650000 records inserted in 10.80 seconds 1650000..1700000 records inserted in 11.06 seconds 1700000..1750000 records inserted in 10.24 seconds 1750000..1800000 records inserted in 10.57 seconds 1800000..1850000 records inserted in 11.54 seconds 1850000..1900000 records inserted in 10.80 seconds 1900000..1950000 records inserted in 11.07 seconds 1950000..2000000 records inserted in 13.27 seconds

Tamaño del archivo de base de datos: 180.1 MiB.

Como puede ver, la velocidad de inserción permanece bastante constante si fragmenta los datos en varias tablas.

En mi proyecto, no pude copiar la base de datos, ya que está indexada en diferentes columnas. Para acelerar las inserciones, puse la base de datos durante la creación en / dev / shm (= linux ramdisk) y luego la copié al disco local. Obviamente, esto solo funciona bien para una base de datos write-once, read-many.

Lamentablemente, diría que esto es una limitación de tablas grandes en SQLite. No está diseñado para operar en conjuntos de datos de gran escala o gran volumen. Si bien entiendo que puede aumentar drásticamente la complejidad del proyecto, probablemente sea mejor que busque soluciones de bases de datos más sofisticadas que se adapten a sus necesidades.

De todo lo que vinculó, parece que el tamaño de la tabla para acceder a la velocidad es una compensación directa. No puedo tener ambos.

Si su requisito es encontrar un z_id en particular y los x_ids y y_ids vinculados a él (a diferencia de seleccionar rápidamente un rango de z_ids), podría buscar en un hash-relational db de tabla hash no indexada que le permita encontrar al instante su camino a un z_id en particular para obtener sus y_ids y x_ids - sin la sobrecarga de indexación y el rendimiento concomitante degradado durante las inserciones a medida que crece el índice. Para evitar aglutinar colisiones conocidas de cubos, elija un algoritmo de hashing de clave que ponga mayor peso en los dígitos de z_id con la mayor variación (ponderado a la derecha).

PD: Una base de datos que utiliza un b-tree puede parecer más rápida que un db que usa hashing lineal, por ejemplo, pero el rendimiento de inserción permanecerá nivelado con el hash lineal a medida que el rendimiento en el b-tree comience a degradarse.

PPS Para responder la pregunta de kawing-chiu: la característica principal relevante aquí es que dicha base de datos se basa en las llamadas tablas "dispersas" en las que la ubicación física de un registro está determinada por un algoritmo hash que toma la clave de registro como entrada. Este enfoque permite una búsqueda directa a la ubicación del registro en la tabla sin la intermediación de un índice . Como no hay necesidad de recorrer índices o reequilibrar índices, los tiempos de inserción permanecen constantes a medida que la tabla se vuelve más densamente poblada. Con un b-tree, por el contrario, los tiempos de inserción se degradan a medida que el árbol de índice crece. Las aplicaciones OLTP con un gran número de inserciones concurrentes pueden beneficiarse de un enfoque de tabla dispersa. Los registros están dispersos por toda la tabla. La desventaja de los registros que se esparcen por la "tundra" de la tabla dispersa es que reunir grandes conjuntos de registros que tienen un valor en común, como un código postal, puede ser más lento. El enfoque hash de tabla dispersa está optimizado para insertar y recuperar registros individuales, y para recuperar redes de registros relacionados, no grandes conjuntos de registros que tienen algún valor de campo en común.

Una base de datos relacional anidada es aquella que permite tuplas dentro de una columna de una fila.

Sospecho que la colisión del valor hash del índice hace que la velocidad de inserción sea lenta.

Cuando tenemos muchas filas en una tabla, la colisión del valor hash de la columna indexada será más frecuente. Significa que el motor Sqlite necesita calcular el valor hash dos o tres veces, o incluso cuatro veces, para obtener un valor hash diferente.

Así que supongo que esta es la causa raíz de la lentitud de inserción de SQLite cuando la tabla tiene muchas filas.

Este punto podría explicar por qué el uso de fragmentos podría evitar este problema. ¿Quién es un verdadero experto en el dominio SQLite para confirmar o negar mi punto aquí?