Évaluation de MySQL 8.0.34 par Jepsen

 (jepsen.io)
3 points par GN⁺ 2023-12-20 | 1 commentaires | Partager sur WhatsApp
  • Repeatable Read, le niveau d’isolation par défaut de MySQL 8.0.34, présente des violations de cohérence transactionnelle incompatibles avec les attentes d’ANSI SQL et du PL-2.99 d’Adya, même sur un seul nœud sain
  • L’évaluation combine le vérificateur list-append d’Elle, des workloads ciblés et LazyFS pour tester MySQL 8.0.34, MariaDB 10.11.3, des clusters à réplication binlog et un cluster AWS RDS MySQL Multi-AZ DB
  • Comme dans les résultats Hermitage de Kleppmann en 2014, G2-item, G-single et des lost updates ont été reproduits ; des violations de cohérence interne, des non-repeatable reads et des violations de Monotonic Atomic View ont aussi été observés
  • En MySQL mononœud, Read Uncommitted, Read Committed et Serializable semblaient respectivement conformes à PL-1, PL-2 et PL-3, mais le cluster AWS RDS MySQL présentait G2-item et G-single même en Serializable
  • Si vous avez besoin d’un Repeatable Read de niveau ANSI ou PL-2.99, il est difficile de se fier au seul Repeatable Read de MySQL : il faut recourir à Serializable ou à des verrous explicites comme SELECT... FOR UPDATE

Périmètre et sujets de l’évaluation

  • MySQL est une base de données relationnelle très utilisée ; dans cette analyse, « MySQL » désigne MySQL avec son moteur de stockage par défaut, InnoDB
  • L’accent porte sur MySQL en serveur unique, mais l’analyse couvre aussi des clusters avec un primary unique en écriture et des secondaries en lecture seule utilisant la réplication binlog
  • Les cibles de test sont les suivantes
    • MySQL 8.0.34
    • MariaDB 10.11.3
    • Debian Bookworm
    • Le profil « Multi-AZ DB Cluster » d’AWS RDS Cluster
  • Le travail a été réalisé indépendamment, sans rémunération, conformément à la politique d’éthique de Jepsen

Niveaux d’isolation SQL et critères de Repeatable Read

  • ANSI SQL définit Read Uncommitted, Read Committed, Repeatable Read et Serializable selon la possibilité des phénomènes P1 dirty read, P2 non-repeatable read et P3 phantom
  • En 1995, Berenson et al. ont critiqué l’ambiguïté et le caractère incomplet de la définition ANSI dans A Critique of ANSI SQL Isolation Levels
    • P1, P2 et P3 laissent place à l’interprétation
    • Des phénomènes importants comme P0 dirty write sont absents
    • P3 interdit seulement les insertions qui affectent un prédicat, mais ne traite pas les updates ni les deletes
  • La thèse de 1999 d’Atul Adya définit des niveaux d’isolation indépendants de l’implémentation à partir d’un graphe de dépendances entre transactions
    • PL-1 interdit les cycles d’écriture G0
    • PL-2 interdit G0 et G1
    • PL-2.99 interdit G0, G1 et G2-item, et correspond à Repeatable Read
    • PL-3 interdit G0, G1 et G2, et correspond à Serializable
  • Jepsen utilise généralement le formalisme d’Adya pour analyser les historiques de transactions et les anomalies

Conflit entre la documentation MySQL et Repeatable Read

  • La documentation MySQL explique qu’InnoDB fournit les quatre niveaux d’isolation de la norme SQL:1992
  • Repeatable Read, le niveau d’isolation par défaut, est décrit comme faisant lire aux consistent reads, au sein d’une même transaction, le snapshot établi lors de la première lecture
  • La documentation sur les consistent reads explique également que la base de données est vue selon le timepoint de la première lecture
  • Mais une note de cette même documentation indique que le snapshot s’applique aux SELECT, sans nécessairement s’appliquer aux instructions DML, et que des lignes commit par d’autres transactions peuvent être touchées par un DELETE ou un UPDATE
  • Cette note entre en conflit avec le fait qu’ANSI SQL et le manuel de référence MySQL considèrent aussi SELECT comme du DML, et crée une confusion : en Repeatable Read, des écritures peuvent affecter des lignes qui n’étaient pas visibles en lecture

Conception des tests

  • La suite de tests pour MySQL est écrite sur la base de la bibliothèque de test Jepsen 0.3.4
  • Le client utilise l’adaptateur JDBC mysql-connector-j
  • Les tests incluent de l’injection de fautes : process pause, crash, network partition et perte d’écritures disque non fsyncées
  • Cependant, presque toutes les observations de cette analyse apparaissent sur un seul nœud MySQL sain

  • Workload list-append d’Elle

    • Le workload principal utilise le vérificateur list-append d’Elle
    • Elle infère les dépendances write-write, write-read et read-write entre transactions, et prouve des violations de niveaux d’isolation spécifiques via les cycles du graphe de dépendances
    • Le workload list-append exécute des transactions aléatoires composées de reads et d’appends sur plusieurs listes identifiées par clé primaire
    • Les listes sont encodées dans un champ text contenant des valeurs séparées par des virgules, et l’append est traité avec le CONCAT SQL
    • Des améliorations récentes permettent à Elle de mieux détecter :
      • l’inférence de dépendances ww/rw sur des éléments append non lus
      • la détection explicite de P4 lost update
      • la recherche de cycles complexes incluant real-time edges et process edges

  • Workloads ciblés

    • Le workload non-repeatable read cible une ligne de la table people
    • Une famille de transactions ne met à jour que name, tandis qu’une autre lit name, met à jour gender, puis relit name
    • Si name change entre les deux lectures, c’est une violation de Repeatable Read
    • Le workload Monotonic Atomic View utilise la value de deux lignes
    • Le writer incrémente la value de la ligne 0, puis celle de la ligne 1
    • Le reader lit la ligne 0, met à jour le noop de la ligne 1, puis lit la ligne 1 et la ligne 0
    • Si une transaction voit une partie des effets d’une transaction, elle doit voir tous ses effets

  • LazyFS

    • LazyFS est un système de fichiers FUSE qui simule la perte d’écritures non fsyncées
    • Le test consiste à tuer le process MySQL, vider le cache LazyFS, puis redémarrer MySQL
    • Ce rapport est le premier rapport Jepsen public incluant LazyFS

Anomalies découvertes dans MySQL Repeatable Read

  • G2-item

    • Le Repeatable Read PL-2.99 d’Adya interdit G2-item, un cycle de dépendances write-write, write-read et read-write n’impliquant pas de prédicat
    • MySQL Repeatable Read autorise G2-item de façon répétée, même sur un seul nœud sain
    • Le comportement signalé par Kleppmann en 2014 dans Hermitage se produit toujours avec MySQL 8.0.34
    • Un test d’exemple a montré 214 cycles en 40 secondes
    • Ce comportement est interdit en Repeatable Read PL-2.99, mais la définition ANSI de P2 ne couvrant que le cas où la même ligne est lue deux fois, il reste une marge d’interprétation dans la définition ANSI

  • G-single et read skew

    • MySQL Repeatable Read présente aussi G-single
    • G-single est un cycle composé d’arêtes write-write, write-read et read-write, mais dans lequel les arêtes read-write ne sont pas adjacentes
    • Le read skew rapporté par Kleppmann en 2014 est confirmé dans MySQL 8.0.34
    • Dans un test append de 60 secondes, à environ 140 transactions par seconde, 244 G-single et 305 G2-item sont apparus
    • Le test append n’utilise pas d’opérations sur prédicats ; tous ces cas sont donc classés comme violations de Repeatable Read

  • Lost update

    • P4 lost update est un cas particulier de G-single où deux transactions lisent la même version de la même clé et la mettent toutes deux à jour
    • Snapshot Isolation et Repeatable Read PL-2.99 interdisent les lost updates
    • MySQL Repeatable Read autorise de façon répétée les lost updates, même sur un seul nœud sain
    • Dans un test, sur 9 048 transactions réussies, le nouveau checker a trouvé 446 transactions impliquées dans 198 cas de lost update
    • Parmi celles-ci, seulement 47 cas apparaissaient sous forme de cycles
    • Le schéma consistant à lire une valeur puis à l’écrire n’est pas sûr avec MySQL Repeatable Read
    • Dans le pattern ORM standard consistant à lire un objet, le modifier en mémoire puis le réenregistrer, des modifications commit peuvent disparaître silencieusement
    • Les utilisateurs doivent employer eux-mêmes des verrous explicites

  • Non-repeatable read et violations de cohérence interne

    • MySQL Repeatable Read présente des violations de cohérence interne même sur un seul nœud sain
    • Dans le même run de test, 126 transactions commit sur 9 048 ont montré des erreurs de cohérence interne
    • Dans un exemple, une transaction lit une clé comme nil, ajoute une valeur, puis relit la même clé et observe que trois autres valeurs ont été ajoutées
    • Dans un autre exemple, après avoir lu la clé 1096 comme [1 2 3] et append 7, une seconde lecture observe [1 2 3 4 5 6 7]
    • Dans le workload ciblé, au sein d’une transaction Repeatable Read, name est lu comme "pebble", gender est mis à jour en "femme", puis une nouvelle lecture du même name renvoie "moss"
    • Ce comportement contredit la définition ANSI des non-repeatable reads et la description de la documentation MySQL selon laquelle le snapshot est « établi lors de la première lecture »

  • Violations de Monotonic Atomic View

    • Monotonic Atomic View est la propriété selon laquelle une transaction qui observe un effet d’une autre transaction doit en observer tous les effets
    • MySQL Repeatable Read la viole de façon répétée, même sur un seul nœud sain
    • Dans le workload, le writer incrémente la ligne 0, puis la ligne 1
    • Le reader voit d’abord l’ancienne valeur 0 sur la ligne 0, puis l’incrément 1 du writer sur la ligne 1, puis à nouveau 0 sur la ligne 0
    • Il s’agit d’une lecture non monotone qui voit l’effet sur la ligne 1, mais pas celui sur la ligne 0, ce qui ne correspond pas au comportement attendu d’un snapshot classique

Anomalies de Serializable sur AWS RDS MySQL

  • Les clusters AWS RDS MySQL violent de façon répétée la serializability même au niveau d’isolation « Serializable »
  • Sur un cluster RDS MySQL avec le profil de production recommandé par défaut, le test append montre des anomalies G2-item et G-single
  • Les anomalies observées prenaient la forme d’une transaction manquant des dépendances antérieures d’une autre transaction dont elle voyait les effets
  • Cette anomalie est classée à la fois comme G-single et G2-item, et viole Snapshot Isolation, Repeatable Read et Serializability
  • Les réglages liés à replica_preserve_commit_order restent un facteur suspect
    • Depuis MySQL 8.0.27, replica_preserve_commit_order=ON est la valeur par défaut
    • Les paramètres RDS par défaut choisissent toujours un réglage équivalent à replica_preserve_commit_order=OFF
    • Dans les parameter groups RDS, l’ancien nom de ce réglage, slave_preserve_commit_order, est utilisé
    • Lorsque ce réglage est appliqué à un cluster de test local, des G-single et G2-item similaires sont observés

Points semblant fonctionner correctement et résultats LazyFS

  • Read Uncommitted, Read Committed et Serializable dans MySQL 8.0.34 semblent satisfaire respectivement PL-1, PL-2 et PL-3
  • Ce résultat a été observé à la fois sur un nœud unique et sur un petit cluster avec réplicas read-only utilisant la réplication binlog
  • Ces résultats se maintiennent aussi lors de process pauses, crashes et network partitions
  • L’injection de fautes LazyFS n’a pas mis en évidence de problème avec la configuration par défaut de MySQL
  • Avec la valeur par défaut innodb_flush_log_at_trx_commit=1, aucune perte de transaction commit n’est apparue après un crash de process et une perte de données non fsyncées
  • En passant à innodb_flush_log_at_trx_commit=0, MySQL ne faisait un fsync qu’une fois toutes les quelques secondes, et une perte de données a été observée

Nature réelle de MySQL Repeatable Read

  • MySQL Repeatable Read ne satisfait pas Repeatable Read PL-2.99
    • Il présente G2-item et write skew
  • Il ne satisfait pas non plus Snapshot Isolation
    • Il présente G-single, read skew et lost update
  • Il ne satisfait pas non plus cursor stability
    • Des lost updates se produisent
  • Read Atomic, Causal Consistency, Consistent View, Prefix Consistency et Parallel Snapshot Isolation sont également exclus
    • Des violations de cohérence interne ont été observées
  • MySQL Repeatable Read semble toutefois un peu plus fort que Read Committed
    • G0 dirty write, G1a aborted read, G1b intermediate read et G1c cyclic information flow n’ont pas été observés
    • La repeatability de certaines lectures fournit des propriétés plus fortes que Read Committed
  • Cependant, il n’est pas clair quel consistency model décrit exactement MySQL Repeatable Read, et aucune propriété formelle n’est définie officiellement

Décalage entre documentation et compréhension de la communauté

  • Le comportement de Repeatable Read reste insuffisamment compris dans la communauté MySQL
  • Plusieurs articles pensent que MySQL Repeatable Read empêche les lost updates, tandis que d’autres disent qu’il ne les empêche pas et recommandent des verrous explicites
  • De nombreuses ressources en ligne affirment que MySQL Repeatable Read est effectivement repeatable, mais les tests de Jepsen montrent des contre-exemples
  • La documentation de MySQL et de MariaDB explique aussi que Repeatable Read lit le même snapshot au sein d’une même transaction
  • Une phrase de la documentation MySQL sur les consistent reads laisse entendre un comportement contradictoire avec cette description, mais cette information est noyée dans la documentation

Recommandations

  • Si MySQL conserve son comportement actuel, il devrait documenter clairement le consistency model réellement fourni par « Repeatable Read »
  • Une autre option consiste à traiter le comportement actuel comme un bug et à le corriger
  • Jepsen indique qu’il accueillerait favorablement l’engagement de MySQL et d’autres fournisseurs à fournir Repeatable Read PL-2.99
  • Les utilisateurs qui ont besoin de PL-2.99 ou d’ANSI Repeatable Read doivent faire preuve de prudence avec MySQL Repeatable Read
  • Les alternatives pratiques sont les suivantes
    • Utiliser le niveau d’isolation Serializable de MySQL
    • Renforcer les lectures en READ COMMITTED avec des techniques de verrouillage comme SELECT ... FOR UPDATE

Recommandations pour les utilisateurs RDS

  • Les clusters AWS RDS MySQL présentent du read skew et G2-item en « Serializable »
  • Les utilisateurs qui dépendent de la serializability doivent définir slave_preserve_commit_order sur ON dans le parameter group RDS
  • Il est suggéré qu’AWS modifie la valeur par défaut, ou documente clairement les violations de Serializability autorisées dans les known limitations de RDS MySQL

Travaux futurs et appel à la standardisation

  • La réplication binlog de MySQL semblait fragile
    • Des tests Jepsen locaux ont observé plusieurs situations où la réplication s’arrêtait
    • La réplication AWS RDS MySQL pouvait être complètement cassée après seulement quelques minutes de test, et une situation où un CREATE DATABASE réussi sur le primary n’apparaissait pas sur le secondary ne s’est pas rétablie pendant une heure
  • La promotion d’un secondary en primary, ainsi que les topologies de réplication de type ring ou star, n’ont pas été explorées
  • Des travaux sont en cours sur des tests de prédicats plus généraux afin d’évaluer la predicate safety
  • Les définitions ANSI des niveaux d’isolation SQL n’ont pas changé, malgré les 28 ans écoulés depuis que Berenson et al. en ont souligné l’ambiguïté et l’incomplétude, et malgré sept révisions ANSI·ISO
  • Des définitions de niveaux d’isolation plus formelles et portables sont nécessaires pour qu’ISO/IEC 9075-2 puisse traiter clairement des phénomènes comme les anomalies internes, les lost updates et les dirty writes

À lire aussi

1 commentaires

 
GN⁺ 2023-12-20
Avis sur Hacker News

  • J’estime depuis longtemps que repeatable read est une mauvaise idée, même si l’implémentation est parfaite
    Même si cela fonctionne correctement dans la base de données, le raisonnement devient trop difficile avec des requêtes complexes
    À mes yeux, les seuls niveaux d’isolation qui aient du sens sont read committed et serializable
    Il faut soit aller jusqu’au bout avec serializable pour éviter toute surprise, soit choisir un read committed où il est clair qu’il faut verrouiller les lignes avant de lire si l’on a besoin d’une vue cohérente dans la transaction
    read committed se rapproche du code multithread classique et de la gestion mémoire, donc les ingénieurs peuvent plus facilement se forger une intuition, et serializable est si strict qu’il laisse peu de place aux erreurs inattendues
    Entre les deux, c’est un no man’s land, et tout ce qui est moins cohérent que read committed ne mérite plus vraiment le nom de base de données

    • Je ne pense pas non plus que les gens raisonnent bien avec read committed
      Plus l’application grossit, plus il devient très difficile de comprendre tous les cas où des verrous sont pris et où les données sont accédées
      Pour les transactions lecture/écriture, serializable est à mes yeux le seul modèle d’isolation sensé, et pour les transactions en lecture seule, snapshot isolation, qui manipule un instantané de la base à un moment donné, est un bon modèle
      D’ailleurs, ce sont pratiquement les deux seuls modes fournis par Spanner : https://cloud.google.com/spanner/docs/transactions
    • read uncommitted peut convenir pour des statistiques agrégées, mais dans ce cas il vaut mieux envoyer les données vers ClickHouse
    • Les requêtes snapshot en lecture seule sont très utiles dans les systèmes réels
    • Si repeatable read fonctionnait réellement correctement, il ne serait pas nécessaire de verrouiller les lignes

  • Il y a une présentation à FOSSDEM 2024 qui compare les niveaux d’isolation et MVCC des bases SQL
    Elle couvre Oracle, MySQL, SQL Server, PostgreSQL et YugabyteDB
    https://fosdem.org/2024/schedule/event/fosdem-2024-3600-isol...

    • Le présentateur est developer advocate chez YugabyteDB, et je me demande quel lien cela a avec le travail de Kyle

  • Je me demande comment append(a) se mappe exactement sur une opération SQL réelle pour la table donnée
    Est-ce qu’un champ TEXT est utilisé comme une liste ?
    Il m’est aussi arrivé de voir un simple SELECT sur une seule ligne renvoyer un résultat impossible en mode repeatable read de MySQL
    C’était de la forme SELECT min(value), max(value) FROM table WHERE id = 1;, avec id comme clé primaire, et min et max renvoyaient des valeurs différentes

  • J’ai apprécié l’article et le fait qu’il traite d’AWS RDS, mais je me demande s’il s’intéressait aussi à AWS Aurora MySQL
    Pour ceux qui ne le savent pas, AWS a créé une plateforme de base de données compatible protocole qui se fait passer pour MySQL ou PostgreSQL
    Il serait intéressant de voir si Aurora MySQL présente les mêmes « caractéristiques » que RDS ou MariaDB

    • Aurora utilise un moteur de base de données complètement différent, donc les problèmes de concurrence y sont aussi différents, ce qui explique sans doute pourquoi ce n’est pas traité ici
      Cela reste néanmoins une cible très intéressante, et comme Aurora est une base bien plus récente, j’ai l’intuition qu’elle peut encore cacher des problèmes subtils qui n’ont pas été découverts, là où l’ancien MySQL est déjà mieux connu
    • Nous utilisons assez largement MySQL Aurora, et dans notre cas, même si le volume d’usage est très élevé, les schémas de requêtes sont simples, donc on ne voit pas énormément de différences
      Il y a quand même un gros point irritant
      Des ingénieurs de Plaid ont écrit un bon article qui résume bien les différences : https://plaid.com/blog/exploring-performance-differences-bet...
      Pour moi, la plus grande différence est que le modèle d’isolation est légèrement différent parce que le cluster Aurora utilise un stockage partagé
      read committed n’est possible qu’en configurant un paramètre au niveau de tout le cluster, et read uncommitted me semble impossible

  • Article très intéressant
    Il montre bien combien de « systèmes qui fonctionnent en pratique » peuvent être construits sur une base qui présente autant d’anomalies de cohérence

    • La plupart des systèmes sont, en pratique, cassés, et continuent de tourner grâce à des corrections humaines qui contournent le problème

  • Le passage indiquant qu’en seulement 5 minutes de manipulation, la réplication RDS s’est arrêtée, sans même une alerte de health check en échec, est assez inquiétant

    • Les détails comptent, et il est presque impossible de diagnostiquer le problème à partir d’un simple screencast, mais d’après mon expérience AWS fournit en général des CloudWatch Metrics assez généreuses
      En revanche, cela reporte sur l’utilisateur la charge de fouiller parmi plus de 150 métriques et de lire la documentation pour trouver celles qui comptent
      De plus, <https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...> mentionne une cellule de tableau dans la console censée afficher l’état de la réplication, mais dans la console il faut souvent activer soi-même l’affichage de cette colonne, ce qui n’est pas idéal
      AWS s’appuie énormément sur ce qu’il appelle le « modèle de responsabilité partagée »
    • Je peux garantir qu’aucun health check AWS ne doit être considéré comme l’alerte primaire en cas de panne
      Il faut tout faire soi-même depuis l’intérieur de l’hôte ou du conteneur
      Le support AWS/Rackspace se contente de dire que ce qui s’exécute à l’intérieur d’un service AWS n’est pas géré par eux, donc c’est un problème client

  • J’aime le passage disant qu’en 2022 Jepsen a demandé à INESC TEC de l’université de Porto de développer LazyFS
    Un système de fichiers FUSE qui simule la perte d’écritures non fsync, c’est un excellent exemple de technologie qui fait avancer l’état de l’art

  • SELECT ... FOR UPDATE donne l’impression d’être la réponse à ce genre de problèmes
    Si on verrouille la ligne à mettre à jour, est-ce que soudain tout ne se met pas à fonctionner comme annoncé ?

    • En général, les opérations qui verrouillent des lignes ont tendance à « figer » l’existence des valeurs, indépendamment de repeatable read
      Si vous voulez mettre à jour un enregistrement à partir des données d’un autre, il faut faire une lecture avec verrouillage sur cet autre enregistrement, et probablement aussi sur celui à mettre à jour
      Si vous mettez à jour un enregistrement à partir d’un autre avec une seule requête SQL, MySQL verrouillera de toute façon les deux
      Si vous devez mettre à jour quelque chose à partir de plusieurs cibles, d’après mon expérience les interblocages arrivent très facilement
      Il vaut mieux à la place verrouiller quelque chose comme un enregistrement de verrouillage, puis faire une repeatable read sur les données voulues et effectuer la mise à jour
      Le point de référence temporel de repeatable read n’est pas fixé tant qu’on n’a pas effectué de lecture cohérente
      SELECT ... FOR UPDATE n’est pas une lecture cohérente, donc cela fonctionne bien dans des situations concurrentes sans verrouiller des dizaines ou des centaines de lignes via une mise à jour SQL classique
    • Oui, si cela ne vous dérange pas de détruire complètement les performances

  • D’après mon expérience, la plupart des développeurs ne réfléchissent même pas au niveau d’isolation et gardent simplement la valeur par défaut
    Quand une condition de concurrence se produit, ils se disent juste « tiens, c’est bizarre » et passent à autre chose

    • J’aimerais contester, mais les premières années de succès de MongoDB en sont une très bonne démonstration
    • C’est pour cela que j’ai dit que le niveau d’isolation par défaut devrait être serializable
      [1] https://news.ycombinator.com/item?id=38696421
    • Les problèmes d’isolation sont beaucoup trop difficiles à raisonner, donc la plupart de ce qui est en dessous de la cohérence serializable finit par vous piéger d’une manière ou d’une autre
      C’est pourquoi, à mon avis, il vaut mieux que la plupart des développeurs n’aient pas à se soucier eux-mêmes du niveau d’isolation, et que MySQL ainsi que certaines autres bases de données offrent trop peu de garanties au développeur moyen
    • D’après mon expérience, presque aucun développeur ne réfléchit à la cohérence elle-même