¿Cuáles son las características de rendimiento de sqlite con archivos de base de datos muy grandes?

Sé que sqlite no funciona bien con archivos de bases de datos muy grandes, incluso cuando son compatibles (solía haber un comentario en el sitio web sqlite que indica que si necesita tamaños de archivo superiores a 1 GB, puede considerar el uso de una empresa rdbms. 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 lo malo que es realmente antes de considerar otras soluciones.

Estoy hablando de archivos de datos sqlite en el rango de varios gigabytes, desde 2 GB en adelante. Alguien tiene alguna experiencia con esto? ¿Alguna sugerencia / idea?

Así que 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 aproximadamente 8 columnas, casi todos los enteros y 4 índices.

La idea era insertar suficientes datos hasta que los archivos sqlite tuvieran cerca de 50 GB.

Mesa individual

Intenté insertar varias filas en un archivo sqlite con solo una tabla. Cuando el archivo tenía aproximadamente 7 GB (lo siento, no puedo especificar el recuento de filas), las inserciones tardaban demasiado. Había estimado que mi prueba para insertar todos mis datos tardaría aproximadamente 24 horas, pero no se completó incluso después de 48 horas.

Esto me lleva a concluir que una tabla sqlite única y muy grande tendrá problemas con las inserciones, y probablemente también con otras operaciones.

Supongo que esto no es sorprendente, ya que la tabla se hace más grande, la inserción y actualización de todos los índices lleva más tiempo.

Múltiples tablas

Luego traté de dividir los datos por tiempo en varias tablas, una tabla por día. Los datos para la tabla original 1 se dividieron en ~ 700 tablas.

Esta configuración no tuvo problemas con la inserción, no pasó más tiempo con el paso del tiempo, ya que se creó una nueva tabla para cada día.

Problemas de vacío

Como lo señala 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 objective es VACUUM periódicamente para optimizar el archivo y recuperar el espacio de archivos.

Sin embargo, como se señala en la documentación , se hace una copia completa de la base de datos para hacer un vacío, tardando mucho tiempo en completarse. Por lo tanto, 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 el mejor rendimiento de vacío y la velocidad de inserción / eliminación.

Esto complica las consultas, pero para mí, es una compensación que vale la pena poder indexar esta cantidad de datos. Una ventaja adicional es que puedo simplemente eliminar un archivo db completo para eliminar datos de un día (una operación común para mi aplicación).

Probablemente también deba controlar 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 haber un método de vacío incremental que no sea el vacío automático . No puedo usarlo porque mi objective para el vacío es desfragmentar el archivo (el espacio de archivo no es un gran problema), que el vacío automático no funciona. De hecho, la documentación indica que puede empeorar la fragmentación, así que tengo que recurrir periódicamente a un vacío total en el archivo.

Estamos utilizando DBS de 50 GB + en nuestra plataforma. ninguna queja funciona muy bien. ¡Asegúrate de estar haciendo todo bien! ¿Estás usando declaraciones predefinidas? * SQLITE 3.7.3

  1. Actas
  2. Declaraciones pre hechas
  3. Aplicar estas configuraciones (justo después de crear el DB)

    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í

Creé bases de datos SQLite de hasta 3.5GB de tamaño sin problemas de rendimiento. Si recuerdo correctamente, creo que SQLite2 podría haber tenido algunos límites más bajos, pero no creo que SQLite3 tenga tales problemas.

De acuerdo con la página 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, llega a 32 terabytes como tamaño máximo. ¡Creo que alcanzará los límites de su sistema de archivos antes de llegar a SQLite!

Gran parte de la razón por la que tardó más de 48 horas en hacer sus inserciones es debido a sus índices. Es increíblemente más rápido para:

1 – Soltar todos los índices 2 – Hacer todas las inserciones 3 – Crear índices de nuevo

Además de la recomendación habitual:

  1. Drop index for bulk insert.
  2. Inserciones / actualizaciones por lotes en transacciones grandes.
  3. Ajuste su memoria caché de buffer / deshabilite journal / w PRAGMAs.
  4. Use una máquina de 64 bits (para poder usar mucho caché ™).
  5. [agregado en julio de 2014] ¡Use la expresión de tabla común (CTE) en lugar de ejecutar múltiples consultas SQL! Requiere la versión de SQLite 3.8.3.

He aprendido lo siguiente de mi experiencia con SQLite3:

  1. Para obtener la máxima velocidad de inserción, no use el esquema con ninguna restricción de columna. ( Altere la mesa más tarde según sea necesario No puede agregar restricciones con ALTER TABLE).
  2. Optimice su esquema para almacenar lo que necesita. A veces esto significa descomponer tablas y / o incluso comprimir / transformar sus datos antes de insertarlos en la base de datos. Un buen ejemplo es almacenar direcciones IP como enteros (largos).
  3. Una tabla por archivo db: para minimizar la contención del locking. (Utilice ATTACH DATABASE si desea tener un único objeto de conexión.
  4. SQLite puede almacenar diferentes tipos de datos en la misma columna (tipado dynamic), use eso para su ventaja.

Pregunta / comentario bienvenido. 😉

Creo que las principales quejas sobre el escalado de sqlite son:

  1. Proceso único de escritura.
  2. Sin reflection.
  3. Sin réplica

Tengo una base de datos SQLite de 7 GB. Para realizar una consulta en particular con una combinación interna se necesita 2.6s. Para acelerar esto intenté agregar índices. Dependiendo de qué índice (s) he agregado, a veces la consulta bajó a 0.1s y algunas veces subió hasta 7s. Creo que el problema en mi caso fue que si una columna está muy duplicada, agregar un índice degrada el rendimiento 🙁

He experimentado problemas con archivos sqlite grandes cuando uso el comando de vacío.

Todavía no he probado la función auto_vacuum. Si espera estar actualizando y eliminando datos a menudo, entonces vale la pena mirar.

Solía ​​haber una statement en la documentación de SQLite de que el límite de tamaño práctico de un archivo de base de datos era de unas pocas docenas de GB: s. Eso se debió principalmente a la necesidad de que SQLite “asigne un bitmap de páginas sucias” cada vez que inicie una transacción. Por lo tanto, se necesitaron 256 bytes de RAM para cada MB en la base de datos. Insertar en un archivo DB de 50 GB requeriría una gran cantidad (2 ^ 8) * (2 ^ 10) = 2 ^ 18 = 256 MB de RAM.

Pero a partir de versiones recientes de SQLite, esto ya no es necesario. Lea más aquí .