SQLite sur Rails : comment et pourquoi l’optimiser pour de meilleures performances

 (fractaledmind.github.io)
1 points par GN⁺ 2024-09-13 | 1 commentaires | Partager sur WhatsApp
  • SQLite n’est pas encore idéal en production sur Rails avec les réglages par défaut, mais quelques ajustements permettent de construire des applications performantes et résilientes
  • Sous charge d’écriture concurrente, le verrou global d’écriture de SQLite provoque des SQLITE_BUSY, et le flux habituel des transactions d’écriture de Rails s’accorde mal avec le mode deferred par défaut
  • Depuis sqlite3-ruby 1.6.9, on peut changer le mode de transaction par défaut en IMMEDIATE dans database.yml, afin d’attendre et de retenter plus sûrement en cas d’échec d’acquisition du verrou d’écriture
  • busy_timeout conserve la GVL Ruby pendant l’attente et nuit donc au parallélisme de Puma ; utiliser sleep dans un busy_handler pour libérer la GVL et retenter toutes les 1 ms réduit la latence de longue traîne
  • Le mode WAL, les transactions IMMEDIATE, un busy handler qui libère la GVL, la configuration SQLite par défaut de Rails 7.1, et la séparation optionnelle des pools de connexions lecture/écriture sont les principaux leviers pour améliorer les performances de SQLite sur Rails

Limites visibles avec la configuration Rails + SQLite par défaut

  • Pour exploiter une application Rails sur SQLite avec de bonnes performances et de la résilience, la configuration actuelle par défaut ne suffit pas
  • L’objectif est de faire en sorte que l’expérience par défaut avec SQLite dans Rails 8 soit prête pour la production
  • L’application de démonstration est Lorem News
    • Un clone basique de Hacker News avec utilisateurs, publications et commentaires
    • Le contenu est composé de Lorem Ipsum
  • Les tests de charge utilisent oha load testing CLI et des routes de benchmarking intégrées à l’application
  • En envoyant des requêtes séquentielles vers l’endpoint post#create pendant 5 secondes, le RPS reste stable et toutes les requêtes réussissent
  • En envoyant 4 requêtes concurrentes vers le même endpoint pendant 5 secondes, certaines requêtes renvoient des réponses 500

SQLITE_BUSY et les transactions IMMEDIATE

  • Le premier problème visible dans les logs est l’exception SQLITE_BUSY
  • SQLite utilise un verrou d’écriture sur la base de données afin de n’autoriser qu’une seule écriture à la fois
    • Une seule connexion peut détenir le verrou d’écriture à un instant donné
    • Si une nouvelle connexion tente d’obtenir le verrou pendant qu’une autre le détient, un SQLITE_BUSY se produit
  • Plus une application Rails subit de charge concurrente, plus la part des requêtes qui échouent avec SQLITE_BUSY augmente
  • Le mode de transaction par défaut de SQLite est deferred, et il n’essaie pas d’acquérir le verrou avant qu’une vraie écriture n’ait lieu
    • C’est avantageux pour les performances dans un environnement à connexion unique ou avec beaucoup de transactions en lecture seule
    • Une application Rails en production utilise plusieurs connexions sur plusieurs threads, et Rails encapsule les requêtes d’écriture dans des transactions, ce qui entre en conflit avec ce comportement par défaut
  • Si l’acquisition du verrou d’écriture échoue au milieu d’une transaction, SQLite ne peut pas retenter proprement la requête sans casser l’isolation sérialisable et lève donc immédiatement une exception
  • Les transactions IMMEDIATE tentent d’acquérir le verrou d’écriture dès le début de la transaction
    • SQLite peut alors mettre la requête d’écriture en file d’attente et réessayer d’obtenir le verrou plus tard
    • Ce mode convient mieux que deferred au schéma de transactions d’écriture de Rails
  • Le gem sqlite3-ruby prend en charge la configuration du mode de transaction par défaut depuis la version 1.6.9
    • Rails transmet les clés de premier niveau de database.yml à l’initialisation de la base sqlite3-ruby
    • On peut configurer dans database.yml l’exécution des transactions SQLite de Rails en mode IMMEDIATE
  • Après ce changement, de simples tests de charge passent presque sans erreurs 500 sous charge concurrente, mais à 16 requêtes simultanées quelques erreurs réapparaissent

busy_timeout, GVL et busy_handler personnalisé

  • Le goulot d’étranglement suivant apparaît quand le nombre de requêtes concurrentes approche ou dépasse le nombre de workers Puma : la latence p99 grimpe brutalement
  • Le temps réel de traitement des requêtes reste stable même sous une charge concurrente égale à trois fois le nombre de workers Puma, mais dès que certaines requêtes commencent à durer environ 5 secondes, des réponses 500 avec SQLITE_BUSY apparaissent aussi
  • Ces 5 secondes correspondent au réglage timeout dans database.yml, qui est mappé sur le paramètre busy_timeout de SQLite
  • busy_timeout essaie de réacquérir le verrou d’écriture pendant le nombre de millisecondes indiqué au lieu de lever immédiatement une exception BUSY
    • SQLite retente l’acquisition du verrou avec une sorte de backoff exponentiel
    • L’exception BUSY n’est levée que si le verrou n’est pas obtenu avant la fin du délai
    • Une application web peut ainsi ouvrir plusieurs connexions et laisser SQLite ordonnancer les écritures au lieu de le faire elle-même
  • Le goulot d’étranglement vient du fait que SQLite est embarqué dans le processus Ruby et que sqlite3-ruby ne libère pas la GVL Ruby lorsqu’il appelle le code C de SQLite
    • Un worker Puma continue de détenir la GVL pendant qu’il attend le retour d’une requête base de données
    • Il devient alors difficile pour d’autres workers Puma d’envoyer en même temps leurs requêtes d’écriture à SQLite
    • Le caractère séquentiel des écritures SQLite rend aussi le traitement des requêtes Rails plus linéaire, ce qui réduit fortement le débit
  • SQLite fournit un hook busy_handler de plus bas niveau que busy_timeout
    • busy_timeout est une implémentation particulière de busy_handler fournie par SQLite
    • sqlite3-ruby expose un binding vers la fonction C sqlite3_busy_handler, ce qui permet de créer un callback Ruby appelé quand une requête entre dans la file d’attente
  • En utilisant Kernel.sleep dans ce callback Ruby, on peut libérer la GVL pendant que la requête attend de retenter l’acquisition du verrou d’écriture
  • Cette approche améliore fortement la latence p99 sous charge concurrente, mais à p99.99 on observe encore que les requêtes les plus lentes deviennent plus lentes à mesure que la concurrence augmente

Longue traîne du backoff exponentiel et retries à 1 ms

  • La logique busy_timeout réimplémentée en Ruby pénalise de plus en plus les requêtes qui attendent depuis longtemps
  • Les premières tentatives attendent peu, mais le temps d’attente augmente avec le nombre d’appels du callback
    • À la première attente, on dort 1 ms
    • Au dixième appel, on dort 50 ms
    • À partir du 12e, on attend 100 ms à chaque fois
    • Une exception est levée quand le temps d’attente cumulé dépasse le timeout de 5000 ms
  • Si de nouvelles requêtes d’écriture continuent d’arriver régulièrement, les nouvelles requêtes peuvent retenter plus souvent l’acquisition du verrou grâce à leurs temps d’attente plus courts
    • Pendant qu’une ancienne requête, déjà passée par trois attentes, dort 10 ms, une nouvelle requête peut retenter trois fois avec des attentes de 1 ms, 2 ms et 5 ms
    • Ce backoff croissant augmente la probabilité que les requêtes plus anciennes n’obtiennent jamais le verrou d’écriture avant le timeout
  • La solution consiste à faire en sorte que toutes les requêtes retentent à la même fréquence, quel que soit leur âge
  • Ce changement est déjà présent sur la branche main de sqlite3-ruby
    • Au moment de la rédaction, cette fonctionnalité n’est pas encore incluse dans une release taguée
    • Le callback Ruby attend avec sleep, ce qui libère la GVL pendant l’attente
    • Il dort toujours 1 ms
  • Après ce changement, la courbe de latence p99.99 s’aplatit dans les benchmarks
    • Un saut subsiste quand la concurrence dépasse la moitié du nombre de workers Puma
    • Au-delà, la latence de longue traîne se stabilise autour de 0,5 seconde

Mode WAL et séparation des pools de connexions lecture/écriture

  • Quatre conditions sont nécessaires pour les performances de SQLite sur Rails : les transactions IMMEDIATE, un busy handler qui libère la GVL, une configuration SQLite adaptée et le mode WAL
  • Le write-ahead log permet à SQLite de gérer plusieurs lectures simultanées
    • Le mode rollback journal par défaut n’autorise qu’une seule requête à la fois, en lecture comme en écriture
    • Le mode WAL autorise plusieurs readers simultanés, mais un seul writer à la fois
  • Depuis Rails 7.1, Rails applique de meilleurs réglages par défaut aux bases SQLite
    • Ces réglages sont importants pour que SQLite fonctionne bien dans le contexte d’une application web
    • Les détails de ces réglages et leurs raisons sont traités dans un autre article de blog
  • Un cinquième levier de performance, optionnel, consiste à séparer un pool en lecture seule et un pool en écriture seule
    • Le mode WAL de SQLite prend en charge plusieurs connexions de lecture et une seule connexion d’écriture
    • Si le pool de connexions Active Record est saturé par des connexions d’écriture, les lectures peuvent être bloquées inutilement
  • On peut exploiter la prise en charge des bases multiples dans Rails pour faire pointer les configurations reader et writer vers la même base SQLite
    • Il ne s’agit pas de bases distinctes, mais de la même base unique
    • On obtient malgré tout des pools de connexions et des réglages de connexion séparés
  • Le pool reader est composé de connexions en lecture seule, tandis que le pool writer ne contient qu’une seule connexion
  • Les modèles Active Record sont configurés pour se connecter au pool approprié selon leur rôle
  • Grâce à l’automatic role switching de Rails, la connexion par défaut pour toutes les requêtes web peut être le pool reader, avec bascule vers le pool writer uniquement quand une écriture est nécessaire
    • Comme tout se fait sur la même base, il n’y a pas besoin d’un delay pour garantir le read your own writes
    • La méthode transaction de l’adaptateur ActiveRecord est patchée pour que les transactions se connectent à la base writer
  • Cette combinaison de « deferred requests » et de pools de connexions isolés améliore les performances en RPS bruts lors du test de l’endpoint de création de commentaires

Des améliorations empaquetées dans un gem

  • Il n’est pas nécessaire d’implémenter soi-même toutes ces améliorations dans son application Rails
  • L’installation de activerecord-enhancedsqlite3-adapter permet d’appliquer les améliorations concernées
  • La fonctionnalité de pools de connexions isolés est plus récente et expérimentale, et demande donc une configuration supplémentaire en opt-in
  • Cette approche regroupe outils, techniques et réglages par défaut pour utiliser SQLite en production sur Rails de façon rapide et flexible
  • Rails est un bon framework d’application web pour travailler avec SQLite, et l’écosystème d’outils et de gems autour de ce sujet continue de grandir

À lire aussi

1 commentaires

 
GN⁺ 2024-09-13
Avis sur Hacker News

  • Toute personne qui envisage d’utiliser SQLite + Rails devrait absolument jeter un œil au projet Litestack d’Oldmoe (X/GitHub)
    Litestack est une gem Ruby qui exploite le caractère embarqué de SQLite dans les apps Ruby et Ruby on Rails pour fournir, dans un seul package, une base de données SQL, un cache rapide, une file de jobs, un broker de messages, un moteur de recherche plein texte et une plateforme de métriques
    Je l’utilise dans un projet en ce moment et j’en suis très satisfait ; le lien est https://github.com/oldmoe/litestack

  • Je n’imagine même pas combien de jours il a fallu pour écrire un article aussi détaillé
    C’est utile pour quiconque réfléchit à la montée en charge d’applications web avec SQLite, au-delà de Rails

  • Toute personne qui travaille avec SQLite devrait lire cet article, quels que soient le langage ou le framework utilisés
    Ayant dû découvrir la plupart de ces choses par moi-même il y a quelques années, merci de les avoir rassemblées

  • Je construis un système d’analytics FOSS qui doit être facile à installer, et j’aimerais envoyer les données d’événements vers une base SQLite distincte, séparée des données principales de l’application
    Même un site web modérément fréquenté peut générer plus de 1 000 événements par seconde, donc la scalabilité m’inquiète
    Je me demande si accumuler les événements en mémoire côté serveur puis effectuer une écriture groupée une fois par seconde est une manière raisonnable de contourner la limite de SQLite sur les écritures multiples, ou s’il existe de meilleures idées

    • Je trouve ça assez raisonnable et c’est une bonne idée. J’ai implémenté des approches similaires dans plusieurs systèmes, et le traitement par lots réduit généralement l’overhead par élément
      On peut aussi le démontrer facilement par benchmark, et mettre l’ensemble des éléments du lot dans une seule transaction
      Avec le traitement par lots, on se retrouve en pratique avec un seul thread qui prend un lot et effectue l’écriture réelle, ce qui s’accorde bien avec la limite d’une seule écriture concurrente de SQLite
      Cela dit, la complexité augmente un peu. Il faut décider quoi faire si une écriture groupée ne se termine pas en moins d’une seconde, si la taille de la file stockée en mémoire doit être illimitée, si, dans ce cas, on peut être sûr que le serveur ne mourra pas par OOM en cas de surcharge, et si l’on fixe une limite, s’il faut accepter de perdre des éléments, quels éléments jeter le cas échéant, et quelle limite donner à la file
      Ces questions se posent inévitablement dans presque tous les systèmes qui ont besoin d’une file, et elles seront utiles aussi bien maintenant que plus tard
    • Ce n’est pas que SQLite soit incapable d’écrire. C’est simplement qu’il ne prend en charge qu’une transaction d’écriture à la fois
      Si vous n’avez pas confiance dans les performances de concurrence des transactions de SQLite, vous pouvez sérialiser toutes les écritures dans un thread ou un processus dédié
    • Ici, cela a bien fonctionné et a changé ma vision de SQLite. Hipp a aussi travaillé avec nous, et SQLite peut offrir des performances énormes : https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...
    • Les données d’analytics étant généralement centrées sur l’écriture, je recommande ClickHouse
      Avec la fonctionnalité async-insert[0] de ClickHouse, vous n’avez pas à vous soucier du traitement par lots des événements côté application
      Si vous cherchez une solution embarquée, vous pouvez utiliser chDB, basé sur ClickHouse
      [0] https://clickhouse.com/blog/asynchronous-data-inserts-in-cli...
    • Les écritures groupées sont probablement une bonne idée, mais la meilleure façon de faire ce genre de chose avec SQLite est sans doute d’utiliser WAL et d’avoir un unique rédacteur désigné
      Vous pouvez avoir autant de lecteurs que vous voulez, et le rédacteur peut recevoir ses entrées depuis quelque chose comme une file
      Rien qu’avec ça, les performances sont souvent vraiment surprenantes

  • Je ne comprends toujours pas très bien cette tendance à vouloir utiliser SQLite comme base de données backend en production
    SQLite est excellent comme petite base de données applicative côté client, embarquable, par exemple pour le carnet d’adresses d’un téléphone, mais ses propres développeurs ont constamment refusé de l’étendre au-delà de ce périmètre
    Par exemple, ils n’ajoutent pas de types natifs utiles comme date/heure ou UUID. Parce que cela ferait grossir le code et la taille des objets embarqués, avec pour résultat qu’on reste coincé dans un monde où « tout est une chaîne »
    L’intégrité référentielle peut être activée, mais les options de contraintes restent très limitées
    Je ne comprends pas pourquoi on continue à vouloir le faire entrer de force dans un rôle qui ne lui convient pas et qui n’est pas vraiment pris en charge

    • D’abord, je ne sais pas si Richard Hipp serait d’accord avec cette idée du rôle auquel SQLite était « initialement destiné »
      La raison est simple. Pour les schémas d’accès majoritairement en lecture, SQLite est extrêmement rapide, suffisamment pour simplifier le code d’accès à la base de données, et par exemple les requêtes N+1 ne sont souvent pas un vrai problème
      SQLite supprime aussi une couche dans une architecture N-tiers, et donc autant d’éléments susceptibles de tomber en panne. Si vous avez déjà exploité vous-même Postgres ou MySQL, vous savez que beaucoup de choses peuvent réellement mal tourner
      Ce n’est pas le choix parfait pour toutes les applis, ni même pour une majorité d’entre elles, mais la tendance actuelle ressemble plutôt à un rééquilibrage face à l’idée manifestement fausse selon laquelle SQLite ne conviendrait qu’aux « petites bases de données embarquées pour applis clientes »
    • On finit par réaliser qu’un « serveur d’API » était en fait un SGBD depuis le début
      Une fois qu’on le voit ainsi, avoir un autre SGBD à côté d’un SGBD qui manipule les mêmes données devient assez absurde
      Du coup, soit on laisse le client se connecter directement à Postgres, soit on retire Postgres et on se concentre davantage sur son propre SGBD
      Si l’on choisit la deuxième option, SQLite est un moteur pratique sur lequel construire. Il n’est pas parfait, mais c’est l’outil dont on dispose aujourd’hui
      Cette prise de conscience ne s’est diffusée à grande échelle que relativement récemment, donc beaucoup d’expérimentations sont en cours pour voir ce qui fonctionne ou non
      C’est le cycle naturel de l’informatique : l’ancien redevient nouveau
      On peut remplacer Postgres par MySQL, MSSQL, Oracle ou tout autre SGBD dans cette lecture
    • Les UUID peuvent bien être stockés sous forme de chaînes ou de BLOB, et les dates sous forme de chaînes ou de timestamps entiers/flottants, non ?
      Ce genre de simplification profite à plusieurs égards non seulement aux développeurs de SQLite et au matériel peu puissant, mais aussi aux développeurs d’applications
      La documentation est plus simple, la courbe d’apprentissage plus courte, la surface de bugs et la taille du binaire diminuent aussi
      Les logiciels actuels ont tendance à ajouter de l’embonpoint et de la complexité partout ; c’est très bien que quelques projets comme SQLite y résistent

  • Excellent article, et je me demande s’il existe quelque chose de similaire pour Django
    ArchiveBox utilise SQLite via Django, et nous avons assez souvent rencontré exactement les mêmes problèmes que ceux décrits dans l’article pour Rails
    Ce serait bien d’avoir une solution au niveau de SQLite qui n’oblige pas à sérialiser toutes les écritures par un autre chemin dans l’application

  • Le gem sqlite3-ruby, par conception, ne libère pas le GVL pendant les appels SQLite ; d’après le commentaire de l’issue liée https://github.com/sparklemotion/sqlite3-ruby/issues/287#iss..., il semble qu’on soupçonnait un coût important pour reprendre le verrou, mais que cela n’avait pas été vérifié
    Avec tous ces contournements, cela paraît un peu douteux
    Dans la culture des extensions Python, j’ai l’impression que cela aurait été conçu à l’inverse, et je me demande comment ils font réellement de ce côté-là
    En plus, dans l’issue liée, un commentaire indique que « le gem extralite est un client SQLite alternatif qui libère le GVL pendant les opérations bloquantes, et les détails liés à la concurrence sont ici : https://github.com/digital-fabric/extralite?tab=readme-ov-fi.... En général, il est beaucoup plus rapide que ce gem et n’a pas de problèmes de concurrence »

    • Une discussion plus détaillée est disponible dans https://github.com/sparklemotion/sqlite3-ruby/pull/528 et https://github.com/digital-fabric/extralite/pull/46
      Il a été vérifié que libérer simplement le GVL à chaque step de la machine virtuelle SQLite dégrade fortement les performances monothread
      Trouver un compromis entre performances monothread et multithread est délicat
      Avec Rails, on sait que l’exécution est multithread à cause du pool de connexions, mais le gem de bas niveau est aussi largement utilisé par d’autres bibliothèques et outils dans des environnements monothread

  • Voici quelques réglages que je garde pour mon petit service web perso
    PRAGMA journal_mode = WAL;
    PRAGMA busy_timeout = 5000;
    PRAGMA synchronous = NORMAL;
    PRAGMA cache_size = 1000000000;
    PRAGMA foreign_keys = true;
    PRAGMA temp_store = memory;
    Et j’utilise des transactions BEGIN IMMEDIATE
    https://kerkour.com/sqlite-for-servers

    • Que pensez-vous de cache_size et mmap_size ?

  • J’aime SQLite comme Rails, mais là, cela ressemble à utiliser MS Access en production

    • Ce n’est pas si différent, mais dans les scénarios surtout en lecture, SQLite est bien plus performant que Jet (MS Access)
      En plus, les ordinateurs et les disques sont aujourd’hui beaucoup plus rapides qu’il y a des décennies, quand Access était davantage utilisé
      Pour des charges surtout en lecture, SQLite, et même Jet, peuvent probablement atteindre assez facilement des dizaines de milliers de requêtes par seconde
      La plupart des applications n’ont pas des centaines de milliers d’utilisateurs simultanés, donc SQLite peut très bien convenir
      Autre point fort de SQLite : il existe des clients pour presque toutes les plateformes et tous les langages
      L’archivage, les sauvegardes et la portabilité sont aussi des cas d’usage qui conviennent bien à SQLite. Sur un projet avec des saisies de données limitées à une certaine période, j’ai fortement poussé l’usage d’un SQLite par boîte, et je pense encore aujourd’hui que cela aurait été mieux
      Au lieu de créer un schéma complexe et des fonctions d’export/archivage personnalisées, on aurait pu simplement copier un seul fichier pour en faire une archive ou une sauvegarde, sans avoir à se soucier autant des évolutions de schéma au fil du temps
      Cela dépend du contexte, mais c’est une solution plutôt correcte pour beaucoup de problèmes. C’est un peu comme le fait que, pour la plupart des applications, PostgreSQL ou un autre SGBDR est souvent un meilleur choix que des options NoSQL plus scalables
      On avait tendance à surconcevoir, et les performances actuelles du calcul et des entrées/sorties approchent désormais un niveau où ces efforts se justifient de moins en moins
    • J’aimerais qu’il existe quelque chose comme MS Access pour les apps web. J’ai essayé plusieurs produits de création de sites web, mais aucun n’était aussi intuitif qu’Access
    • Je me demande pourquoi. Surtout pour une application orientée lecture, quelles limites de scalabilité faut-il craindre ?
    • Pour Pieter Levels, cette approche fonctionne. Bien sûr, des problèmes apparaîtront si le nombre d’utilisateurs dépasse un certain seuil

  • Article très instructif et bien écrit
    Je me demande pourquoi la méthode busy_timeout par défaut utilise un délai exponentiel qui pénalise les anciennes requêtes
    En quoi est-ce un choix raisonnable par défaut ?