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

sqlite portable

Question

Sé que sqlite no funciona bien con archivos de base de datos extremadamente grandes, incluso cuando son compatibles (solía haber un comentario en el sitio web de sqlite que indica que si necesita tamaños de archivo superiores a 1 GB, es posible que desee considerar el uso de un rdbms empresarial. No lo encuentre más, 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.

Estoy hablando de archivos de datos de sqlite en el rango de varios gigabytes, a partir de 2 GB. Alguien tiene alguna experiencia con esto? ¿Algún consejo / ideas?

Answer 1

Además de la recomendación habitual:

1. Índice de caída para inserto a granel.
2. Lotes de inserciones / actualizaciones en grandes transacciones.
3. Ajuste su caché de búfer / deshabilite journal / w PRAGMAs.
4. Use una máquina de 64 bits (para poder usar una gran cantidad de caché ™).
5. [agregado en julio de 2014] ¡Use la expresión de tabla común (CTE) en lugar de ejecutar varias consultas SQL! Requiere la versión 3.8.3 de SQLite.

He aprendido lo siguiente de mi experiencia con SQLite3:

1. Para obtener la velocidad máxima de inserción, no utilice el esquema con ninguna restricción de columna. ( Alterar 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 gran ejemplo es almacenar las direcciones IP como enteros (largos).
3. Una tabla por archivo db - para minimizar la contención de bloqueo. (Utilice ATTACH DATABASE si desea tener un solo objeto de conexión.
4. SQLite puede almacenar diferentes tipos de datos en la misma columna (escritura dinámica), úselo en su beneficio.

Pregunta / comentario de bienvenida. ;-)

Answer 2

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 solo archivo sqlite con una sola tabla o varias tablas. Cada tabla tenía alrededor de 8 columnas, casi todos los números enteros y 4 índices.

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

Mesa única

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 los recuentos de filas) las inserciones se estaban demorando. Calculé 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 la conclusión de 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 una sorpresa, ya que la tabla se hace más grande, la inserción y actualización de todos los índices lleva más tiempo.

Tablas multiples

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 tardó más tiempo en progresar, ya que se creó una nueva tabla para cada día.

Problemas de vacío

Como lo señaló i_like_caffeine, el comando VACUUM es un problema, cuanto más grande es el archivo sqlite. A medida que se realicen más inserciones / eliminaciones, la fragmentación del archivo en el disco empeorará, por lo que el objetivo es realizar VACUUM periódicamente para optimizar el archivo y recuperar el espacio del archivo.

Sin embargo, como se señala en la documentation , se hace una copia completa de la base de datos para hacer un vacío, lo que lleva mucho tiempo en completarse. Entonces, cuanto más pequeña sea la base de datos, más rápido terminará 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 del rendimiento de vacío y la velocidad de inserción / eliminación.

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

Probablemente tendría que 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 parece haber un método de vacío incremental que no sea el vacío automático . No puedo usarlo porque mi objetivo para la aspiradora es desfragmentar el archivo (el espacio de archivos no es un gran problema), que no hace la aspiración automática. De hecho, la documentación indica que puede empeorar la fragmentación, por lo que tengo que recurrir periódicamente a hacer un vacío completo en el archivo.

Answer 3

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

1. Proceso único de escritura.
2. Sin reflejo.
3. No hay replicación.

Answer 4

En la documentación de SQLite solía haber una declaración 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. Esto se debió principalmente a la necesidad de que SQLite "asigne un mapa de bits de páginas sucias" cada vez que inició una transacción. Por lo tanto, se requerían 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 las versiones recientes de SQLite, esto ya no es necesario. Lea más here .

Answer 5

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

1. Actas
2. Declaraciones hechas previamente

3. Aplicar esta configuración (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í.

Answer 6

Gran parte de la razón por la que tardaron más de 48 horas en realizar las inserciones se debe a sus índices. Es increíblemente más rápido para:

1 - Eliminar todos los índices 2 - Hacer todas las inserciones 3 - Crear índices nuevamente

Answer 7

He creado bases de datos SQLite de hasta 3,5 GB de tamaño sin problemas de rendimiento notables. Si recuerdo correctamente, creo que SQLite2 podría haber tenido algunos límites inferiores, pero no creo que SQLite3 tenga ningún problema de este tipo.

De acuerdo con la página de Límites de SQLite , el tamaño máximo de cada página de base de datos es de 32K. Y el máximo de páginas en una base de datos es 1024 ^ 3. Así que, según mis cálculos, el tamaño máximo es de 32 terabytes. ¡Creo que alcanzará los límites de su sistema de archivos antes de llegar a SQLite!

Answer 8

He experimentado problemas con archivos grandes de sqlite al usar el comando de vacío.

No he probado la característica auto_vacuum todavía. Si espera actualizar y eliminar datos a menudo, entonces vale la pena mirar esto.

Answer 9

Tengo una base de datos SQLite de 7GB. Para realizar una consulta en particular con una unión interna se necesitan 2.6 s Para acelerar esto, intenté agregar índices. Dependiendo de los índices que agregué, a veces la consulta bajaba a 0.1s y a veces subía hasta 7s. Creo que el problema en mi caso fue que si una columna está altamente duplicada, al agregar un índice se degrada el rendimiento :(