Les développeurs de SQLite ont publié une série de mesures montrant que la base de données est 35 % plus rapide que le système de fichiers et qu'elle utilise environ 20 % d'espace disque en moins que le stockage des blobs dans des fichiers individuels. SQLite est une bibliothèque en langage C qui implémente un moteur de base de données SQL léger, rapide, autonome, fiable et complet. SQLite est le moteur de base de données le plus utilisé au monde, il est intégré à tous les téléphones portables et à la plupart des ordinateurs, et est fourni avec d'innombrables autres applications que les gens utilisent tous les jours. Le format de fichier SQLite est stable, multiplateforme et rétrocompatible, et les développeurs s'engagent à ce qu'il le reste jusqu'en 2050. Les fichiers de base de données SQLite sont couramment utilisés comme conteneurs pour transférer un contenu riche entre systèmes et comme format d'archivage à long terme pour les données. Il existe plus de mille milliards de bases de données SQLite en cours d'utilisation. Le code source de SQLite fait partie du domaine public et est libre d'utilisation pour tous.
Résumé
SQLite lit et écrit de petits blobs (par exemple, des images miniatures) 35 % plus rapidement que les mêmes blobs peuvent être lus ou écrits dans des fichiers individuels sur le disque en utilisant fread() ou fwrite(). En outre, une seule base de données SQLite contenant des blobs de 10 kilo-octets utilise environ 20 % d'espace disque en moins que le stockage des blobs dans des fichiers individuels.
La différence de performance s'explique par le fait que lorsqu'on travaille à partir d'une base de données SQLite, les appels système open() et close() ne sont invoqués qu'une seule fois, alors que open() et close() sont invoqués une fois pour chaque blob lorsqu'on utilise des blobs stockés dans des fichiers individuels. Il semble que le surcoût lié à l'appel de open() et close() soit plus important que le surcoût lié à l'utilisation de la base de données. La réduction de la taille provient du fait que les fichiers individuels sont remplis au multiple suivant de la taille du bloc du système de fichiers, alors que les blobs sont rangés plus étroitement dans une base de données SQLite.
Les mesures présentées ici ont été effectuées durant la semaine du 2017-06-05 en utilisant une version de SQLite comprise entre 3.19.2 et 3.20.0. Vous pouvez vous attendre à ce que les futures versions de SQLite soient encore plus performantes.
Mise en garde
Le chiffre de 35 % ci-dessus est approximatif. Les temps réels varient en fonction du matériel, du système d'exploitation et des détails de l'expérience, ainsi qu'en raison des fluctuations aléatoires des performances sur le matériel réel.
Le chiffre de 35 % est basé sur des tests effectués sur toutes les machines que l'auteur a sous la main. Certains utilisateurs ont signalé que SQLite avait une latence plus élevée que les E/S directes sur leurs systèmes. Cette différence n'est pas encore comprise. Il semble également que SQLite ne soit pas aussi performant que l'E/S directe lorsque les expériences sont réalisées en utilisant un cache de système de fichiers froid.
Il faut donc retenir ceci : la latence read/write de SQLite est compétitive par rapport à la latence read/write de fichiers individuels sur le disque. SQLite est souvent plus rapide. Parfois, SQLite est presque aussi rapide. Quoi qu'il en soit, l'hypothèse commune selon laquelle une base de données relationnelle doit être plus lente qu'une E/S directe sur le système de fichiers est ici réfutée.
Études connexes
Une étude réalisée en 2022 a montré que SQLite est environ deux fois plus rapide que Btrfs et Ext4 sur Linux pour les charges de travail réelles.
Jim Gray et d'autres chercheurs ont étudié les performances de lecture des blobs par rapport aux E/S de fichiers pour Microsoft SQL Server et ont constaté que la lecture des blobs à partir de la base de données était plus rapide pour les tailles de blobs inférieures à 250 Ko et 1 Mo. Dans leur étude, la base de données stocke toujours le nom de fichier du contenu, même si le contenu se trouve dans un fichier séparé. La base de données est donc consultée pour chaque blob, même si ce n'est que pour extraire le nom de fichier.
Dans la présente étude, la clé du blob est le nom de fichier, de sorte qu'aucun accès préalable à la base de données n'est nécessaire. Étant donné que la base de données n'est jamais utilisée pour lire le contenu de fichiers individuels, le seuil à partir duquel les E/S directes de fichiers deviennent plus rapides est plus bas que dans l'article de Gray.
L'article Internal Versus External BLOBs est une étude antérieure (circa 2011) qui utilise la même approche que l'article de Jim Gray - stocker les noms de fichiers blob en tant qu'entrées dans la base de données - mais pour SQLite au lieu de SQL Server.
Comment ces mesures sont-elles effectuées ?
Les performances d'E/S sont mesurées à l'aide du programme kvtest.c issu de l'arborescence des sources de SQLite. Pour compiler ce programme de test, il faut d'abord rassembler le fichier source kvtest.c dans un répertoire contenant les fichiers sources de l'amalgame SQLite « sqlite3.c » et « sqlite3.h ». Ensuite, sous Unix, exécuter une commande comme la suivante :
| Code : | Sélectionner tout |
1 2 | gcc -Os -I. -DSQLITE_DIRECT_OVERFLOW_READ \ kvtest.c sqlite3.c -o kvtest -ldl -lpthread |
Ou sous Windows avec MSVC :
| Code : | Sélectionner tout |
cl -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c
Les instructions pour la compilation pour Android sont indiquées ci-dessous.
Utilisez le programme « kvtest » résultant pour générer une base de données de test avec 100 000 blobs aléatoires incompressibles, chacun ayant une taille aléatoire comprise entre 8 000 et 12 000 octets en utilisant une commande comme celle-ci :
| Code : | Sélectionner tout |
./kvtest init test1.db --count 100k --size 10k --variance 2k
Si vous le souhaitez, vous pouvez vérifier la nouvelle base de données en exécutant cette commande :
| Code : | Sélectionner tout |
./kvtest stat test1.db
Ensuite, faites des copies de tous les blobs dans des fichiers individuels dans un répertoire en utilisant une commande comme celle-ci :
| Code : | Sélectionner tout |
./kvtest export test1.db test1.dir
À ce stade, vous pouvez mesurer l'espace disque utilisé par la base de données test1.db et l'espace utilisé par le répertoire test1.dir et tout son contenu. Sur un ordinateur de bureau Ubuntu Linux standard, le fichier de base de données aura une taille de 1 024 512 000 octets et le répertoire test1.dir utilisera 1 228 800 000 octets d'espace (d'après « du -k »), soit environ 20 % de plus que la base de données.
Le répertoire « test1.dir » créé ci-dessus regroupe tous les blobs dans un seul dossier. Il a été supposé que certains systèmes d'exploitation auraient de mauvaises performances lorsqu'un seul répertoire contient 100 000 objets. Pour tester cette hypothèse, le programme kvtest peut également stocker les blobs dans une hiérarchie de dossiers ne contenant pas plus de 100 fichiers et/ou sous-répertoires par dossier. La représentation alternative des blobs sur le disque peut être créée en utilisant l'option --tree de la ligne de commande de la commande « export », comme suit :
| Code : | Sélectionner tout |
./kvtest export test1.db test1.tree --tree
Le répertoire test1.dir contiendra 100 000 fichiers portant des noms tels que « 000000 », « 000001 », « 000002 », etc., mais le répertoire test1.tree contiendra les mêmes fichiers dans des sous-répertoires tels que « 00/00/00 », « 00/00/01 », etc. Les répertoires test1.dir et test1.test occupent approximativement le même espace, bien que test1.test soit légèrement plus grand en raison des entrées de répertoire supplémentaires.
Toutes les expériences qui suivent fonctionnent de la même manière avec « test1.dir » ou « test1.tree ». La différence de performance mesurée est très faible dans les deux cas, quel que soit le système d'exploitation.
Mesurez les performances de lecture des blobs depuis la base de données et depuis des fichiers individuels à l'aide de ces commandes :
| Code : | Sélectionner tout |
1 2 3 | ./kvtest run test1.db --count 100k --blob-api ./kvtest run test1.dir --count 100k --blob-api ./kvtest run test1.tree --count 100k --blob-api |
En fonction de votre matériel et de votre système d'exploitation, vous devriez constater que les lectures à partir du fichier de base de données test1.db sont environ 35 % plus rapides que les lectures à partir de fichiers individuels dans les dossiers test1.dir ou test1.tree. Les résultats peuvent varier considérablement d'une exécution à l'autre en raison de la mise en cache, il est donc conseillé d'exécuter les tests plusieurs fois et de prendre une moyenne ou un pire ou un meilleur cas, en fonction de vos besoins.
L'option --blob-api sur le test de lecture de la base de données fait que kvtest utilise la fonctionnalité sqlite3_blob_read() de SQLite pour charger le contenu des blobs, plutôt que d'exécuter des instructions SQL pures. Cela permet à SQLite de fonctionner un peu plus rapidement lors des tests de lecture. Vous pouvez omettre cette option pour comparer les performances de SQLite lors de l'exécution d'instructions SQL. Dans ce cas, SQLite reste plus performant que les lectures directes, mais pas autant que lors de l'utilisation de sqlite3_blob_read(). L'option --blob-api est ignorée pour les tests qui lisent à partir de fichiers disques individuels.
Mesurez les performances en écriture en ajoutant l'option --update. Cela signifie que les blobs sont écrasés à la place par un autre blob aléatoire ayant exactement la même taille.
| Code : | Sélectionner tout |
1 2 3 | ./kvtest run test1.db --count 100k --update ./kvtest run test1.dir --count 100k --update ./kvtest run test1.tree --count 100k --update |
Le test d'écriture ci-dessus n'est pas tout à fait juste, puisque SQLite effectue des transactions sécurisées alors que l'écriture directe sur disque ne l'est pas. Pour mettre les tests sur un pied d'égalité, ajoutez l'option --nosync aux écritures SQLite pour désactiver l'appel à fsync() ou FlushFileBuffers() pour forcer le contenu sur le disque, ou utilisez l'option --fsync pour les tests directs sur disque pour les forcer à invoquer fsync() ou FlushFileBuffers() lors de la mise à jour des fichiers sur disque.
Par défaut, kvtest exécute les mesures d'E/S de la base de données au sein d'une seule transaction. Utilisez l'option --multitrans pour exécuter chaque lecture ou écriture de blob dans une transaction séparée. L'option --multitrans rend SQLite beaucoup plus lent et non compétitif par rapport aux entrées/sorties directes sur disque. Cette option prouve, une fois de plus, que pour obtenir les meilleures performances de SQLite, vous devez regrouper autant d'interactions avec la base de données que possible au sein d'une seule transaction.
Il existe de nombreuses autres options de test, qui peuvent être visualisées en exécutant la commande :
| Code : | Sélectionner tout |
./kvtest help
Mesures de la performance de lecture
Le graphique ci-dessous montre les données recueillies à l'aide de kvtest.c sur cinq systèmes différents :
- Win7 : Un ordinateur portable Dell Inspiron circa-2009, Pentium dual-core à 2.30GHz, 4GiB RAM, Windows7.
- Win10 : Un Lenovo YOGA 910 de 2016, Intel i7-7500 à 2.70GHz, 16GiB RAM, Windows10.
- Mac : Un MacBook Pro 2015, Intel Core i7 à 3,1 GHz, 16 Go de RAM, MacOS 10.12.5.
- Ubuntu : Ordinateur de bureau construit à partir d'un Intel i7-4770K à 3.50GHz, 32GiB RAM, Ubuntu 16.04.2 LTS
- Android : Galaxy S3, ARMv7, 2GiB RAM.
Toutes les machines utilisent des disques SSD, à l'exception de Win7 qui a un disque dur. La base de données de test est constituée de 100 000 blobs dont la taille est uniformément répartie entre 8 000 et 12 000, pour un total d'environ 1 gigaoctet de contenu. La taille des pages de la base de données est de 4KiB. L'option de compilation -DSQLITE_DIRECT_OVERFLOW_READ a été utilisée pour tous ces tests. Les tests ont été exécutés plusieurs fois. La première exécution a été utilisée pour chauffer le cache et ses temps ont été écartés.
Le graphique ci-dessous montre le temps moyen de lecture d'un blob directement depuis le système de fichiers par rapport au temps nécessaire pour lire le même blob depuis la base de données SQLite. Les temps réels varient considérablement d'un système à l'autre (le bureau Ubuntu est beaucoup plus rapide que le téléphone Galaxy S3, par exemple). Ce graphique montre le rapport entre le temps nécessaire pour lire des blobs à partir d'un fichier et le temps nécessaire pour les lire à partir de la base de données. La colonne la plus à gauche du graphique est le temps normalisé de lecture à partir de la base de données, pour référence.
Dans ce graphique, une instruction SQL (« SELECT v FROM kv WHERE k=?1 ») est préparée une fois. Ensuite, pour chaque blob, la valeur de la clé du blob est liée au paramètre ?1 et l'instruction est évaluée pour extraire le contenu du blob.
Le graphique montre que sous Windows 10, le contenu peut être lu à partir de la base de données SQLite environ 5 fois plus vite qu'il ne peut être lu directement à partir du disque. Sur Android, SQLite n'est qu'environ 35 % plus rapide que la lecture à partir du disque.
Les performances peuvent être légèrement améliorées en contournant la couche SQL et en lisant le contenu du blob directement à l'aide de l'interface sqlite3_blob_read(), comme le montre le graphique suivant :
Il est possible d'améliorer encore les performances en utilisant la fonction d'E/S en mémoire de SQLite. Dans le graphique suivant, l'ensemble du fichier de base de données de 1 Go est mis en mémoire et les blobs sont lus (dans un ordre aléatoire) à l'aide de l'interface sqlite3_blob_read(). Grâce à ces optimisations, SQLite est deux fois plus rapide qu'Android ou MacOS-X et plus de 10 fois plus rapide que Windows.
Le troisième graphique montre que la lecture du contenu d'un blob à partir de SQLite peut être deux fois plus rapide que la lecture de fichiers individuels sur le disque pour Mac et Android, et dix fois plus rapide pour Windows.
Mesures des performances en écriture
Les écritures sont plus lentes. Sur tous les systèmes, en utilisant à la fois les E/S directes et SQLite, les performances en écriture sont entre 5 et 15 fois plus lentes que les lectures.
Les mesures des performances en écriture ont été effectuées en remplaçant (par écrasement) un blob entier par un blob différent. Tous les blobs de cette expérience sont aléatoires et incompressibles. Les écritures étant beaucoup plus lentes que les lectures, seuls 10 000 des 100 000 blobs de la base de données sont remplacés. Les blobs à remplacer sont choisis au hasard et ne sont pas classés dans un ordre particulier.
Les écritures directes sur le disque sont effectuées à l'aide de fopen()/fwrite()/fclose(). Par défaut, et dans tous les résultats présentés ci-dessous, les tampons du système de fichiers du système d'exploitation ne sont jamais vidés vers le stockage persistant à l'aide de fsync() ou de FlushFileBuffers(). En d'autres termes, aucune tentative n'est faite pour rendre les écritures directes sur disque transactionnelles ou sûres sur le plan énergétique. Il a été constaté que l'invocation de fsync() ou de FlushFileBuffers() pour chaque fichier écrit rend le stockage direct sur disque environ 10 fois plus lent que les écritures sur SQLite.
Le graphique suivant compare les mises à jour de la base de données SQLite en mode WAL avec les écrasements bruts de fichiers distincts sur le disque. Le paramètre synchrone de PRAGMA est NORMAL. Toutes les écritures de la base de données se font en une seule transaction. Le minuteur pour les écritures de la base de données est arrêté après la validation de la transaction, mais avant l'exécution d'un point de contrôle. Notez que les écritures SQLite, contrairement aux écritures directes sur disque, sont transactionnelles et sans danger pour l'alimentation, bien que les transactions ne soient pas durables car le paramètre synchrone est NORMAL au lieu de FULL.
Les chiffres des performances android pour les expériences d'écriture sont omis car les tests de performance sur le Galaxy S3 sont très aléatoires. Deux exécutions consécutives de la même expérience donneraient des temps très différents. Et, pour être juste, les performances de SQLite sur android sont légèrement plus lentes que l'écriture directe sur le disque.
Le graphique suivant montre les performances de SQLite par rapport à l'écriture directe sur disque lorsque les transactions sont désactivées (PRAGMA journal_mode=OFF) et que PRAGMA synchronous est réglé sur OFF. Ces paramètres placent SQLite sur un pied d'égalité avec les écritures directes sur disque, ce qui signifie qu'ils rendent les données susceptibles d'être corrompues en cas de panne du système ou de coupure de courant.
Pour tous les tests d'écriture, il est important de désactiver les logiciels antivirus avant d'effectuer les tests de performance de l'écriture directe sur disque. Il a été constaté que les logiciels antivirus ralentissent les écritures directes sur disque d'un ordre de grandeur, alors qu'ils n'ont qu'un impact très limité sur les écritures SQLite. Ceci est probablement dû au fait que le direct-to-disk modifie des milliers de fichiers distincts qui doivent tous être contrôlés par l'antivirus, alors que les écritures SQLite ne modifient que le seul fichier de la base de données.
Variations
L'option de compilation -DSQLITE_DIRECT_OVERFLOW_READ permet à SQLite de contourner son cache de pages lors de la lecture du contenu des pages de débordement. Cela permet à la base de données de lire des blobs de 10K un peu plus rapidement, mais pas tant que ça. SQLite conserve un avantage de vitesse par rapport aux lectures directes du système de fichiers sans l'option de compilation SQLITE_DIRECT_OVERFLOW_READ.
D'autres options de compilation telles que l'utilisation de -O3 au lieu de -Os ou l'utilisation de -DSQLITE_THREADSAFE=0 et/ou d'autres options de compilation recommandées peuvent aider SQLite à fonctionner encore plus rapidement par rapport aux lectures directes du système de fichiers.
La taille des blobs dans les données de test affecte les performances. Le système de fichiers sera généralement plus rapide pour les blobs de grande taille, puisque les frais généraux des fonctions open() et close() sont amortis sur un plus grand nombre d'octets d'E/S, tandis que la base de données sera plus efficace en termes de vitesse et d'espace à mesure que la taille moyenne des blobs diminuera.
Constats généraux
- SQLite est compétitif et généralement plus rapide que les blobs stockés dans des fichiers séparés sur le disque, tant en lecture qu'en écriture.
- SQLite est beaucoup plus rapide que les écritures directes sur disque sous Windows lorsque la protection antivirus est activée. Étant donné que les logiciels antivirus sont et devraient être activés par défaut dans Windows, cela signifie que SQLite est généralement beaucoup plus rapide que les écritures directes sur disque sous Windows.
- La lecture est environ un ordre de grandeur plus rapide que l'écriture, pour tous les systèmes et à la fois pour SQLite et les E/S directes sur disque.
- Les performances en matière d'E/S varient considérablement en fonction du système d'exploitation et du matériel. Effectuez vos propres mesures avant de tirer des conclusions.
- Certains autres moteurs de base de données SQL conseillent aux développeurs de stocker les blobs dans des fichiers séparés, puis de stocker le nom du fichier dans la base de données. Dans ce cas, où la base de données doit d'abord être consultée pour trouver le nom du fichier avant d'ouvrir et de lire le fichier, le simple fait de stocker l'ensemble du blob dans la base de données permet d'obtenir des performances de lecture et d'écriture beaucoup plus rapides avec SQLite.
Notes supplémentaires (compilation et test sur Android)
Le programme kvtest est compilé et exécuté sur Android comme suit. Installez d'abord le SDK et le NDK Android. Ensuite, préparez un script nommé « android-gcc » qui ressemble à peu près à ceci :
| Code : | Sélectionner tout |
1 2 3 4 5 6 7 | #!/bin/sh # NDK=/home/drh/Android/Sdk/ndk-bundle SYSROOT=$NDK/platforms/android-16/arch-arm ABIN=$NDK/toolchains/arm-linux-androideabi-4.9/prebuilt/linux-x86_64/bin GCC=$ABIN/arm-linux-androideabi-gcc $GCC --sysroot=$SYSROOT -fPIC -pie $* |
Rendez ce script exécutable et placez-le dans votre $PATH. Compilez ensuite le programme kvtest comme suit :
| Code : | Sélectionner tout |
android-gcc -Os -I. kvtest.c sqlite3.c -o kvtest-android
Ensuite, déplacez l'exécutable kvtest-android résultant sur l'appareil Android :
| Code : | Sélectionner tout |
adb push kvtest-android /data/local/tmp
Enfin, utilisez « adb shell » pour obtenir une invite de commande sur l'appareil Android, accédez au répertoire /data/local/tmp et commencez à exécuter les tests comme avec n'importe quel autre hôte Unix.
Source : SQLite
Et vous ?
Quel est votre avis sur le sujet ? Selon vous, SQLite est il vraiment le SGBD le plus utilisé du monde ? Trouvez-vous ces tests crédibles ou pertinents ? ?Voir aussi :
SQLite 3.40 est disponible, le moteur de base de données léger apporte le support officiel de Wasm, une API de récupération qui pourrait récupérer une partie du contenu d'un fichier de BD corrompu SQLite n'est pas assez ouvert et a besoin d'être modernisé, se plaint son fondateur, « SQLite est explicitement et sans équivoque "Open Source, pas Open Contribution" » 
Envoyé par Anselme45
