lobste.rs passe de MariaDB à SQLite

 (lobste.rs)
1 points par GN⁺ 3 시간 전 | 1 commentaires | Partager sur WhatsApp
  • Après être passé de MariaDB à SQLite, lobste.rs a géré sans problème le pic de trafic du lundi, avec une baisse de l’utilisation CPU et mémoire et une réactivité perçue plus rapide
  • L’arrêt du VPS MariaDB séparé permet de réduire de moitié les coûts de VPS, et clôt le chantier de migration lancé en 2019
  • Lors du premier déploiement, le trafic en lecture seule a suffi à porter tous les CPU à 100 %, ce qui a entraîné un rollback ; le redéploiement a réussi après la correction de deux scans complets de grandes tables et d’un problème N+1
  • Des fonctions utilisateur SQLite ont été utilisées pour implémenter regexp, if et stddev, tout en devant résoudre des contraintes de compatibilité comme l’absence de prise en charge des unsigned bigint et NOCASE, qui ne traite que l’ASCII
  • Changer la base de données sous-jacente sans accès à la base de production exige des données de test de taille réaliste, une suite de tests, des checklists de déploiement/rollback et une collaboration étroite

Des discussions de 2019 au passage à SQLite

  • Les discussions sur la migration ont commencé en 2019 avec l’issue #539
    • À l’époque, MySQL avait été proposé comme alternative pour tenir compte de la compatibilité avec MariaDB, mais une discussion sur une migration vers PostgreSQL était déjà en cours
    • En 2025, Rahul a mentionné l’acquisition de MariaDB par K1, ce qui a relancé les discussions détaillées sur la migration vers PostgreSQL
    • En février, Rahul a demandé si lobsters pouvait fonctionner sur SQLite, et la participation au projet s’est réellement accélérée à partir de juin 2025
  • La première PR, en août 2025, a été interrompue puis fermée par GitHub comme PR ancienne, sans possibilité de la rouvrir

Échec du premier déploiement et correction des goulets d’étranglement

  • Le premier déploiement, le 21 février, a été effectué avec une checklist de déploiement préparée à l’avance, mais juste après le déploiement, le seul trafic en lecture seule a fait monter tous les CPU à 100 %
    • La cause n’ayant pas été trouvée, un rollback a été effectué
    • Sans accès à la base de production, il était difficile de reproduire à l’avance les vrais problèmes de performance
  • Deux jours après cet échec, une troisième et dernière PR a été ouverte pour corriger les problèmes liés à la recherche et ajouter un script de génération de données en volume permettant de créer en local un jeu de données représentant la moitié des données réelles de lobsters
    • La génération des données a pris une semaine
    • Les goulets d’étranglement du premier déploiement provenaient de deux requêtes provoquant des scans complets sur de grandes tables, ainsi que d’un problème N+1 distinct
    • Les correctifs changement 1, changement 2 et changement 3 ont été intégrés
    • Le matin du déploiement, un journal des requêtes lentes a été ajouté afin de pouvoir suivre d’éventuels problèmes de performance supplémentaires

Résultats du redéploiement et baisse des coûts d’exploitation

  • Lors du deuxième déploiement, le 11 juillet, les checklists de déploiement et de rollback ont été préparées à nouveau ; après la bascule, le site a continué à fonctionner normalement et l’utilisation CPU et mémoire est restée stable
    • En surveillant les métriques du site et IRC, les problèmes signalés ont été traités immédiatement via correctif 1 et correctif 2
    • Le pic de trafic du lundi n’a posé aucun problème, et une baisse de l’utilisation CPU et mémoire ainsi qu’une réactivité perçue plus rapide ont été constatées
    • L’arrêt du VPS MariaDB permettra de réduire de moitié les coûts de VPS, et l’issue de migration est également clôturée

Les contraintes de SQLite par rapport à MariaDB

  • La prise en charge des fonctions définies par l’utilisateur (UDF) dans la gem SQLite a été utilisée pour implémenter dans SQLite les fonctions absentes regexp, if, stddev
    • Cela a évité d’ajouter trop de contournements pour les migrations SQL
  • SQLite ne prenant pas en charge les unsigned bigint utilisés pour certains ID dans MariaDB, ils ont été remplacés par des bigint
  • Les règles de tri sont également plus limitées que dans MariaDB
    • MariaDB utilisait utf8mb4_general_ci, tandis que SQLite applique NOCASE
    • NOCASE ne prend pas en charge le repliement complet de casse UTF et ne traite que les caractères ASCII
  • Pour les tables de recherche plein texte de SQLite, les Contentless-Delete Tables, approche recommandée mais non activée par défaut, conviennent bien
  • Les paramètres PRAGMA par défaut de Rails fonctionnent correctement pour lobsters
    • Les migrations de base de données étant propres à chaque DB, les anciennes migrations ont été déplacées dans le répertoire old_migrations afin que db:migrate continue de fonctionner

Enseignements sur les tests et la collaboration

  • Dans la codebase de lobsters, on trouve le parseur de recherche et heinous_inline_partials, un hack destiné à accélérer le rendu
    • La suite de tests a permis de vérifier la justesse du passage à SQLite sans grands tests manuels
  • Une communication fluide entre les participants a été déterminante pour la réussite de la migration
    • Remplacer la base de données sous-jacente sans accès à la base de production a été très difficile
    • Si le même travail devait être refait, il faudrait préparer à l’avance un volume de données réaliste
  • Si les tests avaient pu être configurés pour échouer en cas de scan complet de table, le problème de performance du premier déploiement aurait pu être détecté à l’avance
  • Il faut des outils permettant de créer facilement un jeu de données proche de la production, sans devoir l’écrire soi-même ni attendre une semaine

À lire aussi

1 commentaires

 
GN⁺ 3 시간 전
Réactions sur Lobste.rs

  • @thomas0 a pris en charge avec soin la migration de grande ampleur, @355E3B a aidé à la planification et à l’exploitation, et grâce aux contributeurs liés à #539 ainsi qu’à #lobsters, même la période en lecture seule s’est passée dans la bonne humeur
    À l’origine, PostgreSQL était prévu dans #539, mais comme le volontaire qui a réellement assuré le travail utilisait SQLite et qu’on voulait éviter une solution plus grosse et plus complexe que les besoins réels, le choix s’est porté sur SQLite. PostgreSQL reste le choix par défaut habituel, mais il implique la charge d’exploiter, d’ajuster et de maintenir un service séparé
    Fait surprenant, après la migration, l’usage CPU et RAM a diminué, et des graphiques détaillés figurent à la fin de #539

  • Chaque fois que je vois la configuration par défaut de SQLite, je suis surpris. Ce serait bien d’avoir un mode pour les nouveaux projets qui active par défaut WAL, les clés étrangères, synchronous=NORMAL, etc., et il y a probablement d’autres valeurs par défaut à améliorer

    • Il me semble me souvenir vaguement que certains aspects de la planification ou du format des écritures disque étaient avantageux pour les HDD mais assez inadaptés aux NVMe

  • Je suis curieux de connaître la stratégie de sauvegarde de la base SQLite. J’aimerais savoir s’ils utilisent un outil comme litestream ou une autre méthode

  • Je me demande comment lobste.rs gère les opérations de mise à jour

    • Ils utilisent l’instruction update de SQLite. Si tu parlais d’un autre type de mise à jour, merci de préciser

  • SQLite a déjà une fonction iif, mais le fait même d’exploiter des fonctions définies par l’utilisateur dans ce domaine est aussi intéressant

  • Je pensais que SQLite ne prenait pas en charge le même niveau de concurrence que PostgreSQL ou MySQL, mais à lire le document d’ensemble, faut-il comprendre qu’il peut désormais être utilisé par plusieurs processus, à condition que lobste.rs tourne sur un seul serveur ?
    Je voudrais aussi savoir si les processus se synchronisent à l’intérieur de SQLite via de la mémoire partagée ou mmap, avec une seule écriture simultanée mais plusieurs lectures autorisées, et si les écritures en attente patientent à leur tour dans une file

    • La FAQ SQLite peut répondre à une partie de ces questions. On peut faire plusieurs lectures à travers plusieurs processus, mais une seule écriture est possible à la fois, et SQLite le garantit via des verrous du système de fichiers
      Pour l’attente des écritures, il semble qu’il n’y ait pas de file explicite en mémoire partagée, mais plutôt une courte attente suivie d’une nouvelle tentative. Il n’est pas certain que l’usage de SQLite sur plusieurs machines soit totalement impossible, mais comme l’approche simple consistant à partager le fichier via NFS est explicitement déconseillée, considérer qu’il s’agit d’un serveur unique est une approximation raisonnable, d’autant qu’une seule machine peut aujourd’hui déjà absorber une charge importante
    • Les écritures peuvent être mises en attente dans une certaine mesure, mais la configuration par défaut est mauvaise et doit être ajustée. La configuration actuellement utilisée est la suivante, et il faut aussi activer le mode de transaction immédiate pour que busy_timeout fonctionne 
      "PRAGMA foreign_keys=ON;"  
"PRAGMA journal_mode = WAL;"  
"PRAGMA synchronous = NORMAL;"  
"PRAGMA busy_timeout = 5000;"  
"PRAGMA temp_store = MEMORY;"  
"PRAGMA mmap_size = 134217728;"  
"PRAGMA journal_size_limit = 67108864;"  
"PRAGMA cache_size = 2000;"
      Références : https://fractaledmind.github.io/2024/04/… , https://kerkour.com/sqlite-for-servers et https://www.sqlite.org/lang_transaction.html#immediate ; voir notamment la partie SQLITE_BUSY du deuxième lien et le troisième lien pour plus de détails

  • Upgrade impressionnante, avec des détails très bien documentés dans la PR, l’issue et le Gist. Je me demande si ce changement a entraîné des différences fonctionnelles, par exemple pour la recherche du site, et quelle est actuellement la taille du fichier SQLite sur disque

    • Pour les résultats de recherche, SQLite utilise sa fonction de classement, donc l’ordre peut différer de celui de MariaDB. Il existe des tests de recherche, mais ils vérifient peu le classement ; il est donc possible qu’il y ait des changements fonctionnels, sans qu’on ait encore d’élément solide pour l’affirmer
      Il me semble avoir vu quelque part que le fichier SQLite faisait 3,8 Go