Quelles sont les caractéristiques de performance de sqlite avec des fichiers de base de données très volumineux ?

Question

Mise à jour 2020 environ 11 ans après que la question ait été postée, puis fermée, ce qui empêche les réponses plus récentes.

Presque tout ce qui est écrit ici est obsolète. Il fut un temps où sqlite était limité à la capacité de la mémoire ou à 2 Go de stockage (32 bits) ou d'autres chiffres populaires... enfin, c'était il y a longtemps.

Les limitations officielles sont énumérées ici . En pratique, sqlite est susceptible de fonctionner tant qu'il y a du stockage disponible. . Il fonctionne bien avec des ensembles de données plus grands que la mémoire, il a été créé à l'origine lorsque la mémoire était mince et c'était un point très important dès le départ.

Il n'y a absolument aucun problème à stocker 100 Go de données. Il pourrait probablement stocker 1 To sans problème, mais c'est à ce moment-là que vous devez vous demander si SQLite est le meilleur outil pour ce travail et vous voulez probablement les fonctionnalités d'une base de données à part entière (clients distants, écritures simultanées, répliques en lecture seule, sharding, etc...).

---

Original :

Je sais que sqlite ne fonctionne pas bien avec des fichiers de base de données extrêmement volumineux, même s'ils sont pris en charge (il y avait un commentaire sur le site web de sqlite indiquant que si vous avez besoin de fichiers de plus de 1 Go, vous devriez envisager d'utiliser un rdbms d'entreprise. Je ne le trouve plus, cela peut être lié à une ancienne version de sqlite).

Cependant, pour mes besoins, j'aimerais me faire une idée de la gravité de la situation avant d'envisager d'autres solutions.

Je parle de fichiers de données sqlite de l'ordre de plusieurs gigaoctets, à partir de 2 Go. Quelqu'un a-t-il de l'expérience dans ce domaine ? Des conseils/idées ?

Answer 1

J'ai donc fait quelques tests avec sqlite pour de très gros fichiers, et je suis arrivé à quelques conclusions (du moins pour mon application spécifique).

Les tests impliquent un seul fichier sqlite avec soit une seule table, soit plusieurs tables. Chaque table a environ 8 colonnes, presque toutes des entiers, et 4 indices.

L'idée était d'insérer suffisamment de données jusqu'à ce que les fichiers sqlite fassent environ 50 Go.

Table unique

J'ai essayé d'insérer plusieurs lignes dans un fichier sqlite avec une seule table. Lorsque le fichier était d'environ 7 Go (désolé, je ne peux pas être précis sur le nombre de lignes), les insertions prenaient beaucoup trop de temps. J'avais estimé que mon test pour insérer toutes mes données prendrait environ 24 heures, mais il ne s'est pas terminé même après 48 heures.

Cela m'amène à conclure qu'une seule et très grande table sqlite aura des problèmes avec les insertions, et probablement aussi avec d'autres opérations.

Je suppose que ce n'est pas une surprise, car plus la table est grande, plus l'insertion et la mise à jour de tous les indices prennent du temps.

Tables multiples

J'ai ensuite essayé de répartir les données par heure sur plusieurs tableaux, un tableau par jour. Les données de la table originale ont été réparties sur ~700 tables.

Cette configuration n'a posé aucun problème d'insertion, elle n'a pas pris plus de temps au fur et à mesure que le temps passait, puisqu'une nouvelle table était créée pour chaque jour.

Problèmes de vide

Comme l'a souligné i_like_caffeine, la commande VACUUM est un problème plus le fichier sqlite est volumineux. Plus les insertions/suppressions sont nombreuses, plus la fragmentation du fichier sur le disque s'aggrave. Le but est donc de VACUUMer périodiquement pour optimiser le fichier et récupérer de l'espace.

Cependant, comme l'a souligné documentation Une copie complète de la base de données est effectuée pour faire le vide, ce qui prend beaucoup de temps. Donc, plus la base de données est petite, plus cette opération sera rapide.

Conclusions

Pour mon application spécifique, je vais probablement répartir les données sur plusieurs fichiers de base de données, un par jour, afin d'obtenir le meilleur des performances de vide et de la vitesse d'insertion/suppression.

Cela complique les requêtes, mais pour moi, c'est un compromis intéressant de pouvoir indexer autant de données. Un autre avantage est que je peux simplement supprimer un fichier db entier pour abandonner une journée de données (une opération courante pour mon application).

Je devrais probablement surveiller la taille des tables par fichier pour savoir quand la vitesse deviendra un problème.

C'est dommage qu'il ne semble pas y avoir de méthode de vide incrémentiel autre que aspirateur automatique . Je ne peux pas l'utiliser parce que mon objectif pour le vide est de défragmenter le fichier (l'espace du fichier n'est pas un problème important), ce que le vide automatique ne fait pas. En fait, la documentation indique qu'il peut aggraver la fragmentation, ce qui m'oblige à faire périodiquement un vide complet sur le fichier.

Answer 2

Nous utilisons des DBS de plus de 50 Go sur notre plateforme. Aucun problème. Assurez-vous que vous faites tout ce qu'il faut ! Utilisez-vous des instructions prédéfinies ? *SQLITE 3.7.3

1. Transactions
2. Déclarations préalables

3. Appliquez ces paramètres (juste après avoir créé la BD)

   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;

J'espère que cela aidera d'autres personnes, cela fonctionne très bien ici

Answer 3

J'ai créé des bases de données SQLite d'une taille allant jusqu'à 3,5 Go sans problème de performances notable. Si je me souviens bien, je pense que SQLite2 pouvait avoir des limites plus basses, mais je ne pense pas que SQLite3 ait de tels problèmes.

Selon le Limites de SQLite la taille maximale de chaque page de la base de données est de 32K. Et le nombre maximum de pages dans une base de données est de 1024^3. Donc, d'après mes calculs, la taille maximale est de 32 téraoctets. Je pense que vous atteindrez les limites de votre système de fichiers avant d'atteindre celles de SQLite !

Answer 4

Une grande partie de la raison pour laquelle il a fallu > 48 heures pour faire vos insertions est due à vos index. C'est incroyablement plus rapide de :

1 - Abandonner tous les index 2 - Faites toutes les insertions 3 - Créez à nouveau des index

Answer 5

En plus de la recommandation habituelle :

1. Laissez tomber l'index pour l'insertion en vrac.
2. Insertions/mises à jour par lots dans les transactions importantes.
3. Réglez votre cache tampon/désactivez les PRAGMAs de journal /w.
4. Utilisez une machine 64 bits (pour pouvoir utiliser beaucoup de cache™).
5. [ajouté en juillet 2014] Utiliser expression de table commune (CTE) au lieu d'exécuter plusieurs requêtes SQL ! Requiert la version 3.8.3 de SQLite.

Mon expérience avec SQLite3 m'a appris ce qui suit :

1. Pour une vitesse d'insertion maximale, n'utilisez pas de schéma avec une contrainte de colonne. ( Modifiez le tableau ultérieurement si nécessaire Vous ne pouvez pas ajouter de contraintes avec ALTER TABLE).
2. Optimisez votre schéma pour stocker ce dont vous avez besoin. Cela signifie parfois qu'il faut décomposer les tables et/ou même compresser/transformer vos données avant de les insérer dans la base de données. Un bon exemple est le stockage des adresses IP sous forme d'entiers (longs).
3. Une table par fichier db - pour minimiser la contention des verrous. (Utilisez JOINDRE LA BASE DE DONNÉES si vous voulez avoir un seul objet de connexion.
4. SQLite peut stocker différents types de données dans la même colonne (typage dynamique), utilisez cela à votre avantage.

Questions/commentaires bienvenus ;-)