Quelques nouveautés apprises en exploitant SQLite

 (jvns.ca)
1 points par GN⁺ 9 시간 전 | 1 commentaires | Partager sur WhatsApp
  • SQLite peut être utilisé en production pour un petit site web, mais à mesure que le Django ORM prend en charge davantage de tâches, la complexité opérationnelle de la base de données devient tangible
  • Une recherche FTS5 sur une table de 4 000 lignes prenait 5 secondes, mais est tombée à environ 0,05 seconde après exécution de ANALYZE, confirmant que les statistiques du planificateur de requêtes peuvent avoir un fort impact sur les performances
  • Si un DELETE massif dépasse 5 secondes, les autres opérations d’écriture peuvent aussi expirer, et même les workers ainsi que la VM peuvent être arrêtés ; les tâches de nettoyage sont donc découpées en petits lots
  • Pour les sauvegardes, VACUUM INTO et restic ont été utilisés, mais des arrêts OOM ont été rencontrés ; Litestream est aussi en cours de test pour une sauvegarde incrémentale plus efficace
  • Il est possible de répartir les tables dans plusieurs fichiers SQLite, et Mess with DNS fonctionne avec SQLite depuis 2022, pendant 4 ans, ce qui en fait un choix jugé adapté au projet

Exploiter SQLite sur un site Django

  • SQLite a été adopté comme base de données du site Django en s’appuyant sur plusieurs articles expliquant qu’il peut être utilisé même pour de petits sites en production
  • SQLite reste une base de données complexe qui demande aussi des connaissances d’exploitation, et dans ce projet, davantage de tâches qu’auparavant lui sont confiées via le Django ORM
  • Conformément à plusieurs recommandations, le mode WAL a été activé dès le départ
  • Il s’agit du quatrième site web utilisant SQLite

Les performances de recherche transformées par ANALYZE

  • Une requête de recherche plein texte avec SQLite FTS5 sur une table de 4 000 lignes prenait 5 secondes
  • Après exécution de ANALYZE, la même requête est tombée à environ 0,05 seconde, au point qu’il n’a plus été nécessaire d’enquêter davantage
  • Il n’a pas été possible d’identifier précisément quel problème de plan de requête était en cause, mais il est supposé qu’il s’agissait d’une situation proche d’une complexité temporelle quadratique involontaire
  • ANALYZE génère des informations statistiques, notamment le nombre de lignes des tables, afin d’aider le planificateur de requêtes à faire de meilleurs choix
  • La lecture des plans de requête n’est pas encore maîtrisée

Nettoyage des données et contrainte d’écriture unique

  • Lors de la suppression massive de lignes inutiles, comme les tâches terminées de django-tasks-db, des problèmes en chaîne sont apparus
    • Une commande de nettoyage traitant beaucoup de lignes s’exécutait pendant plus de 5 secondes
    • Pendant ce temps, les écritures en base des autres workers dépassaient la limite configurée de 5 secondes et expiraient
    • Les workers en échec sur l’écriture plantaient, et la VM était aussi arrêtée
  • La raison exacte de la lenteur de DELETE n’a pas été identifiée ; il est aussi envisagé que beaucoup de code Python s’exécute à l’intérieur de la transaction
  • Pour l’instant, les tâches de nettoyage sont découpées en petits lots afin qu’aucune requête en base ne dépasse 5 secondes
  • Cette expérience a permis de mieux comprendre pourquoi une base comme Postgres, capable de gérer plusieurs écritures simultanées, peut être nécessaire
  • À l’avenir, la possibilité de couper le site pendant ce type d’opérations pour effectuer une maintenance planifiée est aussi envisagée, mais aucun workflow n’est encore en place

Requêtes ORM et volume actuel des données

  • Jusqu’ici, les requêtes souhaitées ont été construites avec le Django ORM sans vérification séparée des performances, mais en dehors du problème lié à ANALYZE, tout a globalement bien fonctionné
  • La base de données reste petite, avec environ 10 000 lignes, et devrait le rester à l’avenir

Méthodes de sauvegarde SQLite

  • Deux approches ont été essayées pour les sauvegardes SQLite : restic et Litestream
  • Les tâches de sauvegarde sont généralement surveillées par un dead man’s switch, mais il semble qu’aucun vrai test de restauration n’ait encore été effectué

  • Sauvegarde avec restic

    • Une copie de la base est créée avec VACUUM INTO, compressée avec gzip, puis envoyée sur S3
    • restic sert à créer la sauvegarde, vérifier les snapshots, supprimer les anciennes sauvegardes et faire le nettoyage
    • Les sauvegardes se terminaient parfois par un OOM, laissant un verrou derrière elles, ce qui a conduit à exécuter aussi restic unlock
    sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
gzip /tmp/calendar.sqlite
restic -r s3://s3.amazonaws.com/some_bucket/ unlock
restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
restic -r s3://s3.amazonaws.com/some_bucket/ snapshots
restic -r s3://s3.amazonaws.com/some_bucket/ forget -l 1 -H 6 -d 2 -w 2 -m 2 -y 2
restic -r s3://s3.amazonaws.com/some_bucket/ prune

  • Sauvegarde incrémentale avec Litestream

    • Litestream a commencé à être utilisé pour éviter les arrêts OOM des sauvegardes restic et tester l’efficacité d’une sauvegarde incrémentale
    • Après avoir écrit le fichier de configuration, la réplication est lancée avec la commande suivante
    litestream replicate -config litestream.yml
    • retention: 400h a été défini pour conserver l’historique de la base pendant un certain temps, mais il n’a pas encore été vérifié si cela fonctionne comme prévu
    • Les sauvegardes sont faites sur AWS, mais comme la création d’identifiants dans la console est fastidieuse, un passage futur vers un autre service compatible S3 est envisagé

Utiliser plusieurs fichiers de base de données SQLite

  • Le projet actuel n’utilise qu’une seule base de données, mais dans Mess with DNS, les tables qui n’avaient pas besoin d’être regroupées ont été réparties dans 3 fichiers de base de données
  • Cette séparation est considérée comme ayant aidé à l’exploitation
  • Mess with DNS fonctionne avec SQLite depuis 2022, depuis 4 ans, et pour ce projet, le passage de Postgres à SQLite a été un bon choix

Des fonctionnalités de base découvertes tardivement en production

  • SQLite a été utilisé pour la première fois dans un projet web en 2022, mais l’existence de ANALYZE n’a été découverte que cette fois-ci
  • Même les fonctionnalités de base d’une technologie utilisée continuent à s’apprendre au fil des années en conditions réelles d’exploitation

À lire aussi

1 commentaires

 
GN⁺ 9 시간 전
Commentaires sur Hacker News

  • Le mode .expert de SQLite permet de repousser un peu le jour où il faut apprendre à lire les plans de requête : https://www.sqlite.org/cli.html#index_recommendations_sqlite...
    En analysant SELECT * FROM x1 WHERE a=? AND b>?;, il recommande un index comme CREATE INDEX x1_idx_000123a7 ON x1(a, b);, puis, après sa création, une nouvelle analyse indique qu’aucun nouvel index n’est nécessaire
    Même avec de “vraies” bases de données comme Postgres, il est généralement recommandé de découper les opérations de nettoyage en petits lots. C’est simplement que, à petite échelle, le caractère inefficace de l’opération se voit moins, donc l’approche de l’article était plus juste qu’elle n’en avait l’air

    • Dans certaines bases de données, supprimer 10 millions de lignes enregistre aussi autant de données d’annulation. Avec Oracle, si elles ne sont pas sauvegardées et purgées à temps, l’espace disque des journaux d’archive peut être saturé
      Faire des commits fréquents aide, mais pour nettoyer régulièrement de très grosses bases de données, le partitionnement s’est révélé le plus efficace. Supprimer la partition la plus ancienne se termine presque instantanément
      En revanche, dans « le worker n’arrive pas à écrire dans la base de données, plante et la VM s’arrête », on ne comprend pas bien pourquoi la VM s’arrête. Ici, VM semble désigner la machine virtuelle qui exécute le système d’exploitation
    • Sur de grosses installations MySQL utilisant la réplication basée sur les lignes, il fallait exécuter par lots les UPDATE ou DELETE modifiant des millions de lignes. Sinon, une seule requête oblige à envoyer d’un coup les millions de lignes mises à jour à tous les réplicas
    • Cela ressemble à EXPLAIN QUERY PLAN : https://sqlite.org/eqp.html
      Le EXPLAIN classique affiche généralement un bytecode bien trop verbeux, tandis que EXPLAIN QUERY PLAN montre un plan résumé
    • Je me demande si le fait que « le caractère inefficace de l’opération se voit moins » est vu ici comme un avantage

  • En tant que personne qui travaille avec des bases de données, cette lecture était frustrante, et j’avais envie d’identifier moi-même la cause pour la corriger. Si c’est une table de seulement 10 000 lignes, un scan complet de table devrait être très rapide, et encore plus avec SQLite exécuté dans le processus sur le même serveur physique
    La solution évidente qui me venait à l’esprit était bien sûr « créez un index ». La lenteur de la suppression ressemble fortement au classique problème N+1 qu’on voit souvent chez les utilisateurs d’ORM avant qu’ils comprennent les interactions internes avec la base de données, et j’espère que Julia publiera un billet de suivi

  • Comme créer des identifiants dans la console AWS était trop pénible, j’ai créé il y a quelques années un outil qui ne résout que ce problème
    uvx s3-credentials create my-existing-s3-bucket
    Cette commande affiche des identifiants en lecture/écriture limités à ce seul bucket. On peut restreindre davantage avec --read-only ou --write-only, ou n’autoriser la lecture et l’écriture que sur les clés commençant par un préfixe donné avec --prefix foo/bar
    Je l’ai aussi utilisé avec Restic et Cloudflare R2, et cela a très bien fonctionné

    • Une manière plus générale de gérer des services AWS complexes consiste à apprendre un minimum de Terraform, puis à laisser le reste au LLM. Plus besoin de se souvenir des ressources créées, et il devient aussi beaucoup plus simple de les supprimer plus tard
    • Il existe aussi un outil similaire : https://litestream.io/
    • Excellent outil, au point qu’on pourrait imaginer son intégration dans l’AWS CLI. En revanche, je me demande dans quels cas des identifiants en écriture seule sont utiles

  • À l’ère des LLM, j’apprécie encore davantage le texte de Julia. Ce processus d’exploration sincère est un antidote aux textes générés automatiquement qui prétendent tout savoir avec assurance

  • J’exécute mes sauvegardes comme ceci :
    OUT="${i}.sql.zst"
    PART="${OUT}.part"
    sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
    mv "${PART}" "${OUT}"
    Cela ne bloque pas les writers s’ils utilisent le WAL, et produit des dumps faciles à synchroniser avec un bon taux de compression. La base de données Home Assistant fait 1,8 Go, mais le dump compressé ne pèse que 286 Mo, et j’estime qu’environ 90 % du contenu généré chaque jour est identique

    • Je me demande ce que vous sauvegardez dans Home Assistant. La sauvegarde par défaut était trop volumineuse, donc j’ai gardé uniquement la configuration en excluant les vidéos, le cache et les dépôts téléchargés par HACS, mais je ne sais pas si j’oublie quelque chose d’important
      Je me demande aussi si la base de données est devenue aussi grosse à cause de l’historique de séries temporelles conservé sur une longue période
    • VACUUM INTO, .backup, sqlite3_rsync et Litestream ne bloquent pas non plus les writers. .backup utilise l’API de sauvegarde
    • Sur une base de données en production, j’ai subi un blocage à cause de .dump, puis je suis passé à .backup. Cela dit, la méthode consistant à écrire dans .part puis faire mv est élégante

  • Je trouve que ce texte manque de substance réelle, avec seulement des formulations comme « je n’ai pas creusé davantage », « c’est ma meilleure hypothèse », « peut-être d’autres choses aussi ? », « il se peut qu’il y ait beaucoup de code Python exécuté dans la transaction ». Il avance parfois même des suppositions erronées sans avoir vraiment vérifié ou enquêté
    En tant qu’utilisateur de Debian, si un résultat de recherche Linux mène vers un forum Ubuntu, je ne l’ouvre généralement pas, justement parce qu’on y trouve trop de mauvaises suppositions. À l’inverse, même si Arch est assez différent de Debian, je consulte en général l’Arch Wiki, rédigé par des gens compétents

    • Julia Evans a une connaissance technique extrêmement solide et excelle particulièrement à enlever le caractère mystérieux de la technique pour faire comprendre à quoi ressemble réellement le processus de résolution de problème
      Cet article ne prétend pas être l’explication du plus grand expert mondial de SQLite, et le titre lui-même fixe clairement l’attente avec « quelques apprentissages sur l’exploitation de SQLite »
      Dans l’ensemble des textes de Julia, on retrouve l’idée qu’il n’est pas nécessaire de tout savoir ni de faire semblant, et qu’on peut construire sa compréhension en identifiant les problèmes avec des méthodes simples. Partager aussi clairement que possible ce qu’on a découvert jusqu’ici est une bonne attitude
    • Je trouve ce texte excellent parce qu’il montre bien la situation d’un utilisateur averti mais généraliste qui utilise cette technologie. L’autrice se concentre sur l’exploitation d’un site web, et ce type de problème est précisément le genre de piège dans lequel tombent souvent les utilisateurs ordinaires qui ne passent pas leurs journées sur un seul outil
      Hier encore, en développant une fonctionnalité, j’ai utilisé 2 langages de programmation, 2 systèmes de build, un fournisseur cloud, un outil de gestion des secrets, un framework complexe de communication client-serveur couvrant deux langages, un système de gestion de versions, un éditeur et un outil de CI. Et on pourrait encore creuser du côté du système d’exploitation, des versions de runtime, de la base de données, du proxy inverse, du cache ou de la logique métier
      Si l’on approfondit tous les sujets rencontrés, on ne termine jamais rien ; il faut donc, comme l’autrice, choisir le problème sur lequel se concentrer
    • L’Arch Wiki est l’une des meilleures ressources Linux. Quand j’utilisais Mint, j’y allais tout le temps, mais depuis que je suis passé à Arch, je le consulte paradoxalement moins qu’à l’époque de Mint

  • Le problème de DELETE peut être facilement atténué par des suppressions par lots, des pauses entre les lots, et une approche consistant à précharger les rowid via SELECT. SELECT ne provoque pas de blocage
    Si les données ont été ajoutées séquentiellement dans la même table, il est probable qu’elles soient aussi stockées dans un ordre similaire dans le fichier ; les supprimer dans cet ordre, ou en ordre inverse, peut donc être plus rapide. Le résultat varie selon le support de stockage et d’autres conditions

    • Le préchargement des ID de lignes a été très efficace non seulement avec SQLite, mais aussi sur de gros clusters Aurora MySQL ou Postgres. On pouvait envoyer le SELECT vers un réplica, et la pression mémoire des index liée au filtrage des lignes pesait fortement sur le CPU et le buffer cache de la base de données
      C’est une stratégie très puissante quand on ne peut pas facilement écarter les données inutiles du chemin principal de traitement, par exemple via l’élagage de partitions

  • Creuser un peu plus les bases de données que ne l’exigent votre niveau actuel ou les besoins du travail reste une bonne manière de progresser. Beaucoup de développeurs web se retrouvent bloqués face aux outils de base de données, et il m’arrive pareil avec certaines technologies d’exploitation comme K8s
    Comprendre comment SQL se traduit en opérations de lecture et d’écriture de données sur disque aide énormément à juger intuitivement quelles approches sont raisonnables. Il faut aussi comprendre le mécanisme de verrouillage de la base
    Avec ces connaissances, on est moins susceptible d’être déconcerté quand un simple COUNT met du temps à se terminer sur Postgres

  • sqlite_stat1 et sqlite_stat4 stockent diverses informations statistiques sur la distribution des valeurs d’index, et le planificateur de requêtes s’en sert pour estimer la sélectivité et l’utilité des index
    sqlite_stat1 ne fournit que le nombre d’enregistrements de l’index et le nombre moyen d’enregistrements par valeur ; si activé, sqlite_stat4 stocke aussi des données d’histogramme

  • Litestream est très intéressant, et j’ai réussi à le faire fonctionner avec S3 comme backend. Cela permet à de très nombreuses applications utilisant SQLite de dépendre beaucoup moins de l’état du système de fichiers
    L’état de S3 est bien plus facile à gérer, et le fournisseur prend aussi en charge les sauvegardes et la synchronisation