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

Pregunta originalFondo

Es bien sabido que SQLitenecesita estar bien afinado para lograr velocidades de inserción del orden de 50k inserciones / s. Aquí hay muchas preguntas con respecto a velocidades de inserción lentas y una gran cantidad de consejos y puntos de referencia.

Tambien hayAfirma que SQLite puede manejar grandes cantidades de datos., con informes de más de 50 GB que no causen problemas con la configuración correcta.

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

Problema

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

El rendimiento es excelente para las primeras filas de 10 m, ~ 35k inserciones / s, pero cuando la tabla tiene ~ 20 m filas, el rendimiento comienza a sufrir. Ahora estoy viendo alrededor de 100 inserciones / 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 paramanteniendo ¿Tasas de inserción altas para tablas con filas> 10 m?

Soluciones conocidas que me gustaría evitar si es posibleDescarte 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 debe ser utilizable durante las actualizaciones. No funcionará para que la base de datos sea completamente inaccesible parax minutos / díaRompe la tabla en subtablas / archivos más pequeños: Esto funcionará a corto plazo y ya lo he experimentado. El problema es que necesito poder recuperar datos de todo el historial al consultar, 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 ser un montón de trabajo y gastos generales, pero lo intentaré si no hay otras alternativas.Conjunto SQLITE_FCNTL_CHUNK_SIZE: No sé C (?!), Así que preferiría no aprenderlo solo para hacer esto. No puedo ver ninguna forma de configurar este parámetro utilizando Perl.ACTUALIZAR

SiguiendoLa sugerencia de tim 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 millonestamaño de lote de compromiso50,000 registroscache_size pragma10,000page_size pragma4,096temp_store pragmamemoriajournal_mode pragmaborrarsynchronous pragmaapagado

En mi proyecto, como en los resultados de referencia a continuación, se crea una tabla temporal basada en archivos y se utiliza el soporte integrado de SQLite para importar datos CSV. La tabla temporal se adjunta a la base de datos de recepción y los conjuntos de 50,000 filas se insertan con uninsert-select declaración. Por lo tanto, los tiempos de inserción no reflejanarchivo a la base de datos insertar tiempos, sino más bienmesa con mesa inserte la velocidad Tomar en cuenta el tiempo de importación de CSV reduciría las velocidades en un 25-50% (una estimación muy aproximada, no toma mucho tiempo importar los datos de CSV).

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

De los datos anteriores se desprende claramente que se puede asignar la respuesta correcta aLa respuesta de tim en lugar de las afirmaciones de que SQLite simplemente no puede manejarlo. Claramente espuede manejar grandes conjuntos de datosSi indexar ese conjunto de datos no es parte de su caso de uso. He estado usando SQLite solo para eso, como backend para un sistema de registro, por un tiempo, lo que haceno Necesito ser indexado, así que me sorprendió bastante la ralentización que experimenté.

Conclusión

Si alguien se encuentra con ganas de almacenar una gran cantidad de datos utilizando SQLitey tenerlo indexado,utilizando fragmentos puede ser la respuesta Finalmente decidí usar los primeros tres caracteres de un hash MD5 en una columna únicaz para determinar la asignación a una de las 4,096 bases de datos. Debido a que mi caso de uso es principalmente de archivo, el esquema no cambiará y las consultas nunca requerirán caminar de un fragmento. Hay un límite para el tamaño de la base de datos ya que los datos extremadamente antiguos se reducirán y finalmente se descartarán, por lo que esta combinación de fragmentación, configuración pragma e incluso algunosDelawarela normalización me da un buen balance que, basado en la evaluación comparativa anterior, mantendrá una velocidad de inserción de al menos 10k inserciones / segundo.

Respuestas a la pregunta(5)

Su respuesta a la pregunta