¿Cuáles son las características de rendimiento de sqlite con archivos de bases de datos muy grandes? [cerrado]
Actualización de 2020 , aproximadamente 11 años después de que se publicara la pregunta y luego se cerrara, lo que impidió respuestas más nuevas.
Casi todo lo escrito aquí está obsoleto. Había una vez que sqlite estaba limitado a la capacidad de memoria o a 2 GB de almacenamiento (32 bits) u otros números populares… bueno, eso fue hace mucho tiempo.
Las limitaciones oficiales se enumeran aquí . En la práctica, es probable que sqlite funcione siempre que haya almacenamiento disponible . Funciona bien con conjuntos de datos más grandes que la memoria, se creó originalmente cuando la memoria era escasa y fue un punto muy importante desde el principio.
No hay absolutamente ningún problema con almacenar 100 GB de datos. Probablemente podría almacenar un TB sin problemas, pero eventualmente ese es el punto en el que debe preguntarse si SQLite es la mejor herramienta para el trabajo y probablemente desee funciones de una base de datos completa (clientes remotos, escrituras simultáneas, réplicas de solo lectura, fragmentación). , etc...).
Original:
Sé que sqlite no funciona bien con archivos de bases de datos extremadamente grandes, incluso cuando son compatibles (solía haber un comentario en el sitio web de sqlite que decía que si necesita tamaños de archivos superiores a 1 GB, es posible que desee considerar el uso de un rdbms empresarial. Ya no lo encuentro, podría estar relacionado con una versión anterior de sqlite).
Sin embargo, para mis propósitos me gustaría tener una idea de qué tan grave es realmente antes de considerar otras soluciones.
Me refiero a archivos de datos sqlite en el rango de varios gigabytes, desde 2 GB en adelante. Alguien tiene alguna experiencia con esto? ¿Algún consejo/idea?
Entonces hice algunas pruebas con sqlite para archivos muy grandes y llegué a algunas conclusiones (al menos para mi aplicación específica).
Las pruebas involucran un único archivo sqlite con una sola tabla o varias tablas. Cada tabla tenía alrededor de 8 columnas, casi todas enteras, y 4 índices.
La idea era insertar suficientes datos hasta que los archivos sqlite tuvieran aproximadamente 50 GB.
Mesa individual
Intenté insertar varias filas en un archivo sqlite con una sola tabla. Cuando el archivo tenía aproximadamente 7 GB (lo siento, no puedo ser específico sobre el número de filas), las inserciones tardaban demasiado. Había estimado que mi prueba para insertar todos mis datos tomaría aproximadamente 24 horas, pero no se completó ni siquiera después de 48 horas.
Esto me lleva a concluir que una única tabla sqlite muy grande tendrá problemas con las inserciones y probablemente también con otras operaciones.
Supongo que esto no es una sorpresa, ya que a medida que la tabla se hace más grande, insertar y actualizar todos los índices lleva más tiempo.
Varias tablas
Luego intenté dividir los datos por tiempo en varias tablas, una tabla por día. Los datos de la tabla 1 original se dividieron en ~700 tablas.
Esta configuración no tuvo problemas con la inserción, no tomó más tiempo conforme avanzaba el tiempo, ya que se creaba una nueva tabla para cada día.
Problemas de vacío
Como señaló i_like_caffeine, el comando VACUUM es un problema cuanto más grande es el archivo sqlite. A medida que se realizan más inserciones/eliminaciones, la fragmentación del archivo en el disco empeorará, por lo que el objetivo es ASPIRAR periódicamente para optimizar el archivo y recuperar espacio.
Sin embargo, como lo señala la documentación , se hace una copia completa de la base de datos en vacío, lo que lleva mucho tiempo completarla. Entonces, cuanto más pequeña sea la base de datos, más rápido finalizará esta operación.
Conclusiones
Para mi aplicación específica, probablemente dividiré los datos en varios archivos db, uno por día, para obtener lo mejor tanto del rendimiento del vacío como de la velocidad de inserción/eliminación.
Esto complica las consultas, pero para mí, vale la pena poder indexar esta cantidad de datos. Una ventaja adicional es que puedo eliminar un archivo db completo para eliminar los datos de un día (una operación común para mi aplicación).
Probablemente también tendría que monitorear el tamaño de la tabla por archivo para ver cuándo la velocidad se convertirá en un problema.
Es una lástima que no parezca existir un método de vacío incremental distinto del vacío automático . No puedo usarlo porque mi objetivo con la aspiradora es desfragmentar el archivo (el espacio en el archivo no es gran cosa), lo cual la aspiradora automática no hace. De hecho, la documentación indica que puede empeorar la fragmentación, por lo que tengo que recurrir periódicamente a realizar una limpieza completa del archivo.
Estamos utilizando DBS de 50 GB+ en nuestra plataforma. No hay quejas, funciona muy bien. ¡Asegúrate de estar haciendo todo bien! ¿Está utilizando declaraciones predefinidas? *SQLITE 3.7.3
- Actas
- Declaraciones hechas previamente
Aplique estas configuraciones (justo después de crear la base de datos)
PRAGMA main.page_size = 4096; PRAGMA main.cache_size=10000; PRAGMA main.locking_mode=EXCLUSIVE; PRAGMA main.synchronous=NORMAL; PRAGMA main.journal_mode=WAL; PRAGMA main.cache_size=5000;
Espero que esto ayude a otros, funciona muy bien aquí.
He creado bases de datos SQLite de hasta 3,5 GB de tamaño sin problemas de rendimiento notables. Si no recuerdo mal, creo que SQLite2 podría haber tenido límites más bajos, pero no creo que SQLite3 tenga esos problemas.
Según la página de límites de SQLite , el tamaño máximo de cada página de base de datos es 32K. Y el máximo de páginas en una base de datos es 1024^3. Entonces, según mis cálculos, el tamaño máximo es 32 terabytes. ¡Creo que alcanzarás los límites de tu sistema de archivos antes de llegar a los de SQLite!
Gran parte de la razón por la que tomó > 48 horas hacer las inserciones se debe a sus índices. Es increíblemente más rápido:
1 - Eliminar todos los índices 2 - Realizar todas las inserciones 3 - Crear índices nuevamente
Además de la recomendación habitual:
- Índice de caída para inserción masiva.
- Inserciones/actualizaciones por lotes en transacciones grandes.
- Ajuste su caché de búfer/deshabilite el diario /w PRAGMA.
- Utilice una máquina de 64 bits (para poder utilizar mucho caché™).
- [agregado en julio de 2014] ¡Utilice expresiones de tabla comunes (CTE) en lugar de ejecutar múltiples consultas SQL! Requiere la versión 3.8.3 de SQLite.
He aprendido lo siguiente de mi experiencia con SQLite3:
- Para obtener la máxima velocidad de inserción, no utilice un esquema con ninguna restricción de columna. (
Modificar la tabla más tarde según sea necesarioNo puede agregar restricciones con ALTER TABLE). - Optimice su esquema para almacenar lo que necesita. A veces, esto significa dividir tablas y/o incluso comprimir/transformar sus datos antes de insertarlos en la base de datos. Un gran ejemplo es almacenar direcciones IP como números enteros (largos).
- Una tabla por archivo de base de datos para minimizar la contención de bloqueos. (Utilice ADJUNTAR BASE DE DATOS si desea tener un único objeto de conexión.
- SQLite puede almacenar diferentes tipos de datos en la misma columna (escritura dinámica), utilícelo a su favor.
Bienvenidos preguntas/comentarios. ;-)
