Dans un avenir lointain, la suppression automatique des données inutiles sera l'une des tâches importantes du SGBD [1]. En attendant, nous devons nous-mêmes nous occuper de la suppression ou du déplacement des données inutiles vers des systèmes de stockage moins coûteux. Supposons que vous décidiez de supprimer quelques millions de lignes. Une tâche assez simple, surtout si la condition est connue et qu'il existe un index approprié. "DELETE FROM table1 WHERE col1 = :value" - quoi de plus simple, non ?
Vidéo:
-
Je fais partie du comité du programme Highload depuis la première année, soit depuis 2007.
-
Et je suis chez Postgres depuis 2005. Utilisé dans de nombreux projets.
-
Groupe avec RuPostges également depuis 2007.
-
Nous sommes passés à plus de 2100 participants à Meetup. Elle est la deuxième au monde après New York, longtemps dépassée par San Francisco.
-
Je vis en Californie depuis plusieurs années. Je fais plus affaire avec des entreprises américaines, y compris des grandes. Ce sont des utilisateurs actifs de Postgres. Et il y a toutes sortes de choses intéressantes.
est mon entreprise. Nous sommes dans le domaine de l'automatisation des tâches qui éliminent les ralentissements de développement.
Si vous faites quelque chose, il y a parfois des sortes de bouchons autour de Postgres. Disons que vous devez attendre que l'administrateur configure un banc d'essai pour vous, ou que vous deviez attendre que le DBA vous réponde. Et nous trouvons de tels goulots d'étranglement dans le processus de développement, de test et d'administration et essayons de les éliminer à l'aide de l'automatisation et de nouvelles approches.
J'étais récemment au VLDB à Los Angeles. Il s'agit de la plus grande conférence sur les bases de données. Et il y avait un rapport qui, à l'avenir, le SGBD ne stockera pas seulement, mais supprimera également automatiquement les données. Ceci est un nouveau sujet.
Il y a de plus en plus de données dans le monde des zettaoctets - c'est 1 000 000 pétaoctets. Et maintenant, on estime déjà que nous avons plus de 100 zettaoctets de données stockées dans le monde. Et ils sont de plus en plus nombreux.
Et que faire avec ? Évidemment, il faut l'enlever. Voici un lien vers ce rapport intéressant. Mais jusqu'à présent, cela n'a pas été implémenté dans le SGBD.
Ceux qui savent compter l'argent veulent deux choses. Ils veulent que nous supprimions, donc techniquement, nous devrions pouvoir le faire.
Ce que je vais raconter ensuite est une situation abstraite qui comprend un tas de situations réelles, c'est-à-dire une sorte de compilation de ce qui m'est réellement arrivé et des bases de données environnantes plusieurs fois, de nombreuses années. Les râteaux sont partout et tout le monde marche dessus tout le temps.
Disons que nous avons une base ou plusieurs bases qui grandissent. Et certains disques sont évidemment nuls. Par exemple, l'utilisateur a commencé à faire quelque chose là-bas, mais ne l'a pas terminé. Et au bout d'un certain temps on sait que cet inachevé ne peut plus être stocké. Autrement dit, nous aimerions nettoyer certaines ordures afin d'économiser de l'espace, d'améliorer les performances, etc.
En général, la tâche consiste à automatiser la suppression de choses spécifiques, de lignes spécifiques dans certains tableaux.
Et nous avons une telle demande, dont nous parlerons aujourd'hui, c'est-à-dire l'enlèvement des ordures.
Nous avons demandé à un développeur expérimenté de le faire. Il a pris cette demande, l'a vérifiée par lui-même - tout fonctionne. Testé sur la mise en scène - tout va bien. Déployé - tout fonctionne. Une fois par jour, nous l'exécutons - tout va bien.
La base de données grandit et grandit. Daily DELETE commence à fonctionner un peu plus lentement.
Ensuite, nous comprenons que nous avons maintenant une société de marketing et que le trafic sera plusieurs fois plus important, nous décidons donc de suspendre temporairement les choses inutiles. Et oublier de revenir.
Quelques mois plus tard, ils se sont souvenus. Et ce développeur a quitté ou est occupé par autre chose, a demandé à un autre de le rendre.
Il a vérifié le développement, la mise en scène - tout est OK. Naturellement, vous devez toujours nettoyer ce qui s'est accumulé. Il a vérifié que tout fonctionnait.
Que se passe-t-il ensuite ? Alors tout s'effondre pour nous. Il tombe de sorte qu'à un moment donné, tout tombe. Tout le monde est sous le choc, personne ne comprend ce qui se passe. Et puis il s'avère que la question était dans ce DELETE.
Quelque chose s'est mal passé ? Voici une liste de ce qui aurait pu mal tourner. Lequel de ces éléments est le plus important ?
-
Par exemple, il n'y a pas eu d'examen, c'est-à-dire que l'expert DBA ne l'a pas examiné. Il trouverait tout de suite le problème avec un œil averti, et en plus, il a accès à la prod, où plusieurs millions de lignes se sont accumulées.
-
Peut-être qu'ils ont vérifié quelque chose de mal.
-
Peut-être que le matériel est obsolète et que vous devez mettre à niveau cette base.
-
Ou quelque chose ne va pas avec la base de données elle-même, et nous devons passer de Postgres à MySQL.
-
Ou peut-être qu'il y a quelque chose qui ne va pas avec l'opération.
-
Peut-être y a-t-il des erreurs dans l'organisation du travail et vous devez licencier quelqu'un et embaucher les meilleurs ?
Il n'y a pas eu de contrôle DBA. S'il y avait un DBA, il verrait ces plusieurs millions de lignes et même sans aucune expérience dirait : « Ils ne font pas ça. Supposons que si ce code était dans GitLab, GitHub et qu'il y aurait un processus de revue de code et qu'il n'y avait rien de tel que sans l'approbation du DBA cette opération aurait lieu sur prod, alors évidemment le DBA dirait : « Cela ne peut pas être fait .”
Et il dirait que vous aurez des problèmes avec les E/S de disque et que tous les processus deviendront fous, il peut y avoir des verrous, et vous bloquerez également l'autovacuum pendant quelques minutes, donc ce n'est pas bon.
La deuxième erreur - ils ont vérifié au mauvais endroit. Nous avons vu après coup que beaucoup de données indésirables s'accumulaient sur la prod, mais le développeur n'avait pas accumulé de données dans cette base de données, et personne n'a créé ces données indésirables lors de la mise en scène. En conséquence, il y avait 1 000 lignes qui ont rapidement fonctionné.
Nous comprenons que nos tests sont faibles, c'est-à-dire que le processus qui est construit ne détecte pas les problèmes. Une expérience DB adéquate n'a pas été réalisée.
Une expérience idéale est de préférence réalisée sur le même équipement. Il n'est pas toujours possible de le faire sur le même équipement, mais il est très important qu'il s'agisse d'une copie grandeur nature de la base de données. C'est ce que je prêche depuis plusieurs années maintenant. Et il y a un an, j'en ai parlé, vous pouvez tout regarder sur YouTube.
Peut-être que notre équipement est mauvais ? Si vous regardez, la latence a sauté. Nous avons vu que l'utilisation est de 100 %. Bien sûr, s'il s'agissait de disques NVMe modernes, ce serait probablement beaucoup plus facile pour nous. Et peut-être qu'on ne s'en remettrait pas.
Si vous avez des clouds, la mise à niveau s'y fait facilement. Création de nouvelles répliques sur le nouveau matériel. basculement. Et tout va bien. Plutôt facile.
Est-il possible de toucher en quelque sorte les petits disques? Et ici, juste avec l'aide de DBA, nous plongeons dans un certain sujet appelé réglage des points de contrôle. Il s'avère que nous n'avions pas de réglage de point de contrôle.
Qu'est-ce qu'un point de contrôle ? C'est dans n'importe quel SGBD. Lorsque vous avez des données en mémoire qui changent, elles ne sont pas immédiatement écrites sur le disque. Les informations indiquant que les données ont été modifiées sont d'abord écrites dans le journal à écriture anticipée. Et à un moment donné, le SGBD décide qu'il est temps de vider les vraies pages sur le disque, de sorte que si nous avons un échec, nous pouvons faire moins REDO. C'est comme un jouet. Si nous sommes tués, nous commencerons le jeu depuis le dernier point de contrôle. Et tous les SGBD l'implémentent.
Les paramètres de Postgres sont à la traîne. Ils sont conçus pour des volumes de données et de transactions vieux de 10 à 15 ans. Et le point de contrôle ne fait pas exception.
Voici les informations de notre rapport de vérification Postgres, c'est-à-dire le bilan de santé automatique. Et voici une base de données de plusieurs téraoctets. Et on voit bien que des checkpoints forcés dans près de 90% des cas.
Qu'est-ce que ça veut dire? Il y a là deux paramètres. Le point de contrôle peut arriver par timeout, par exemple, en 10 minutes. Ou cela peut arriver quand beaucoup de données ont été remplies.
Et par défaut, max_wal_saze est défini sur 1 gigaoctet. En fait, cela se produit réellement dans Postgres après 300 à 400 mégaoctets. Vous avez modifié tellement de données et votre point de contrôle se produit.
Et si personne ne l'a réglé et que le service s'est développé et que l'entreprise gagne beaucoup d'argent, elle a beaucoup de transactions, alors le point de contrôle arrive une fois par minute, parfois toutes les 30 secondes, et parfois même se chevauche. C'est assez mauvais.
Et nous devons faire en sorte qu'il vienne moins souvent. Autrement dit, nous pouvons augmenter max_wal_size. Et cela viendra moins fréquemment.
Mais nous avons développé toute une méthodologie pour savoir comment le faire plus correctement, c'est-à-dire comment prendre une décision sur le choix des paramètres, clairement basée sur des données spécifiques.
Ainsi, nous menons deux séries d'expérimentations sur des bases de données.
La première série - nous changeons max_wal_size. Et nous faisons une opération massive. Tout d'abord, nous le faisons sur le paramètre par défaut de 1 gigaoctet. Et nous effectuons une SUPPRESSION massive de plusieurs millions de lignes.
Vous pouvez voir à quel point c'est difficile pour nous. Nous voyons que le disque IO est très mauvais. Nous regardons combien de WAL nous avons générés, car c'est très important. Voyons combien de fois le point de contrôle s'est produit. Et on voit que ce n'est pas bon.
Ensuite, nous augmentons max_wal_size. Nous répétons. On augmente, on répète. Et tant de fois. En principe, 10 points c'est bien, où 1, 2, 4, 8 gigaoctets. Et nous regardons le comportement d'un système particulier. C'est clair qu'ici le matériel doit être comme sur la prod. Vous devez disposer des mêmes disques, de la même quantité de mémoire et des mêmes paramètres Postgres.
Et de cette manière nous échangerons notre système, et nous saurons comment le SGBD se comportera en cas de mauvaise masse DELETE, comment il fera le point de contrôle.
Les points de contrôle en russe sont des points de contrôle.
Exemple : SUPPRIMER plusieurs millions de lignes par index, les lignes sont "éparpillées" sur les pages.
Voici un exemple. C'est une base. Et avec le paramètre par défaut de 1 gigaoctet pour max_wal_size, il est très clair que nos disques vont sur l'étagère pour l'enregistrement. Cette image est un symptôme typique d'un patient très malade, c'est-à-dire qu'il se sentait vraiment mal. Et il y a eu une seule opération, il n'y a eu qu'un DELETE de plusieurs millions de lignes.
Si une telle opération est autorisée en prod, alors nous allons nous allonger, car il est clair qu'un DELETE nous tue dans l'étagère.
Plus loin, où 16 gigaoctets, force est de constater que les dents ont déjà disparu. Les dents sont déjà meilleures, c'est-à-dire que nous frappons au plafond, mais pas si mal. Il y avait là une certaine liberté. A droite, le dossier. Et le nombre d'opérations - le deuxième graphique. Et force est de constater que l'on respire déjà un peu mieux lorsqu'on a 16 gigaoctets.
Et où 64 gigaoctets peuvent être vus, c'est devenu complètement meilleur. Déjà les dents sont prononcées, il y a plus de possibilités de survivre à d'autres opérations et de faire quelque chose avec le disque.
Pourquoi est-ce?
Je vais plonger un peu dans les détails, mais ce sujet, comment effectuer le réglage des points de contrôle, peut donner lieu à un rapport complet, donc je ne chargerai pas grand-chose, mais je décrirai un peu les difficultés rencontrées.
Si le point de contrôle se produit trop souvent, et que nous mettons à jour nos lignes non pas séquentiellement, mais trouvons par index, ce qui est bien, car nous ne supprimons pas toute la table, alors il peut arriver qu'au début nous touchions la première page, puis la millième, puis est revenu au premier. Et si entre ces visites sur la première page, checkpoint l'a déjà sauvegardée sur disque, alors il la sauvegardera à nouveau, car nous l'avons sali une deuxième fois.
Et nous forcerons le point de contrôle à le sauvegarder plusieurs fois. Comment y aurait-il des opérations redondantes pour lui.
Mais ce n'est pas tout. Les pages font 8 kilo-octets sous Postgres et 4 kilo-octets sous Linux. Et il y a un paramètre full_page_writes. Il est activé par défaut. Et c'est correct, car si nous le désactivons, il y a un risque que seule la moitié de la page soit enregistrée en cas de plantage.
Le comportement d'écriture dans le WAL du journal de transfert est tel que lorsque nous avons un point de contrôle et que nous modifions la page pour la première fois, la page entière, c'est-à-dire les 8 kilo-octets, entre dans le journal de transfert, bien que nous n'ayons changé que le ligne, qui pèse 100 octets . Et nous devons écrire toute la page.
Dans les modifications ultérieures, il n'y aura qu'un tuple spécifique, mais pour la première fois, nous écrivons tout.
Et, par conséquent, si le point de contrôle se reproduisait, nous devions tout recommencer à zéro et pousser toute la page. Avec des points de contrôle fréquents, lorsque nous parcourons les mêmes pages, full_page_writes = on sera plus qu'il ne pourrait l'être, c'est-à-dire que nous générons plus de WAL. Plus est envoyé aux répliques, à l'archive, au disque.
Et, en conséquence, nous avons deux licenciements.
Si nous augmentons max_wal_size, il s'avère que nous simplifions à la fois le point de contrôle et l'auteur de wal. Et c'est super.
Mettons un téraoctet et vivons avec. Qu'y a-t-il de mal à ça ? C'est dommage, car en cas d'échec, nous grimperons pendant des heures, car le point de contrôle était il y a longtemps et beaucoup de choses ont déjà changé. Et nous devons faire tout cela REDO. Et donc nous faisons la deuxième série d'expériences.
Nous effectuons une opération et voyons quand le point de contrôle est sur le point de se terminer, nous tuons exprès -9 Postgres.
Et après on le redémarre, et on voit combien de temps ça va monter sur cet équipement, c'est à dire combien ça va refaire dans cette mauvaise situation.
A deux reprises, je constaterai que la situation est mauvaise. Tout d'abord, nous nous sommes écrasés juste avant la fin du point de contrôle, nous avons donc beaucoup à perdre. Et deuxièmement, nous avons eu une opération massive. Et si les points de contrôle étaient sur timeout, alors, très probablement, moins de WAL seraient générés depuis le dernier point de contrôle. Autrement dit, c'est un double perdant.
Nous mesurons une telle situation pour différentes tailles max_wal_size et comprenons que si max_wal_size est de 64 gigaoctets, alors dans le pire des cas, nous grimperons pendant 10 minutes. Et nous réfléchissons si cela nous convient ou non. C'est une question commerciale. Nous devons montrer cette image aux responsables des décisions commerciales et leur demander : « Combien de temps pouvons-nous nous allonger au maximum en cas de problème ? Pouvons-nous nous allonger dans la pire des situations pendant 3 à 5 minutes ? Et vous prenez une décision.
Et voici un point intéressant. Nous avons quelques rapports sur Patroni lors de la conférence. Et peut-être que vous l'utilisez. Il s'agit d'un basculement automatique pour Postgres. GitLab et Data Egret en ont parlé.
Et si vous avez un basculement automatique qui arrive en 30 secondes, alors peut-être que nous pouvons nous allonger pendant 10 minutes ? Parce que nous passerons à la réplique à ce stade, et tout ira bien. C'est un point discutable. Je ne connais pas de réponse claire. J'ai juste l'impression que ce sujet ne concerne pas seulement la récupération après un crash.
Si nous avons une longue récupération après un échec, nous serons mal à l'aise dans de nombreuses autres situations. Par exemple, dans les mêmes expériences, lorsque nous faisons quelque chose et devons parfois attendre 10 minutes.
Je n'irais toujours pas trop loin, même si nous avons un basculement automatique. En règle générale, des valeurs telles que 64, 100 gigaoctets sont de bonnes valeurs. Parfois, cela vaut même la peine d'en choisir moins. En général, c'est une science subtile.
Pour faire des itérations, par exemple, max_wal_size =1, 8, vous devez répéter l'opération de masse plusieurs fois. Tu l'as fait. Et sur la même base vous voulez le refaire, mais vous avez déjà tout supprimé. Ce qu'il faut faire?
Je parlerai plus tard de notre solution, de ce que nous faisons pour itérer dans de telles situations. Et c'est l'approche la plus correcte.
Mais dans ce cas, nous avons eu de la chance. Si, comme il est dit ici "BEGIN, DELETE, ROLLBACK", alors nous pouvons répéter DELETE. Autrement dit, si nous l'avons annulé nous-mêmes, nous pouvons le répéter. Et physiquement chez vous les données se trouveront au même endroit. Vous n'obtenez même pas de ballonnement. Vous pouvez itérer sur ces DELETE.
Ce DELETE avec ROLLBACK est idéal pour le réglage des points de contrôle, même si vous ne disposez pas de laboratoires de base de données correctement déployés.
Nous avons fait une plaque avec une colonne "i". Postgres a des colonnes utilitaires. Ils sont invisibles sauf demande expresse. Ce sont : ctid, xmid, xmax.
Ctid est une adresse physique. Page zéro, le premier tuple de la page.
On peut voir qu'après ROOLBACK, le tuple est resté au même endroit. Autrement dit, nous pouvons réessayer, il se comportera de la même manière. C'est l'essentiel.
Xmax est l'heure de mort du tuple. Il a été tamponné, mais Postgres sait que la transaction a été annulée, donc peu importe si c'est 0 ou s'il s'agit d'une transaction annulée. Cela suggère qu'il est possible d'itérer sur DELETE et de vérifier les opérations en masse du comportement du système. Vous pouvez créer des laboratoires de bases de données pour les pauvres.
Il s'agit de programmeurs. À propos de DBA aussi, ils réprimandent toujours les programmeurs pour cela : "Pourquoi faites-vous des opérations aussi longues et difficiles ?". C'est un sujet perpendiculaire complètement différent. Il y avait l'administration, et maintenant il y aura le développement.
Évidemment, nous ne nous sommes pas brisés en morceaux. Il est clair. Il est impossible de ne pas casser un tel DELETE pour un tas de millions de lignes en plusieurs parties. Cela se fera pendant 20 minutes et tout se couchera. Mais, malheureusement, même les développeurs expérimentés font des erreurs, même dans les très grandes entreprises.
Pourquoi est-ce important de rompre ?
-
Si nous voyons que le disque est dur, ralentissons-le. Et si nous sommes cassés, alors nous pouvons ajouter des pauses, nous pouvons ralentir la limitation.
-
Et nous ne bloquerons pas les autres pendant longtemps. Dans certains cas, cela n'a pas d'importance, si vous supprimez de vrais déchets sur lesquels personne ne travaille, alors vous ne bloquerez probablement personne, sauf le travail d'autovacuum, car il attendra la fin de la transaction. Mais si vous supprimez quelque chose que quelqu'un d'autre peut demander, il sera bloqué, il y aura une sorte de réaction en chaîne. Les transactions longues doivent être évitées sur les sites Web et les applications mobiles.
C'est intéressant. Je vois souvent que les développeurs demandent : "Quelle taille de pack dois-je choisir ?".
Il est clair que plus la taille du bundle est grande, plus la surcharge de transaction est petite, c'est-à-dire la surcharge supplémentaire des transactions. Mais en même temps, le temps augmente pour cette transaction.
J'ai une règle très simple : prenez-en autant que vous le pouvez, mais ne dépassez pas les exécutables par seconde.
Pourquoi une seconde ? L'explication est très simple et compréhensible pour tout le monde, même les non-techniciens. On voit une réaction. Prenons 50 millisecondes. Si quelque chose a changé, notre œil réagira. Si moins, alors plus difficile. Si quelque chose répond après 100 millisecondes, par exemple, vous avez cliqué sur la souris et qu'elle vous a répondu après 100 millisecondes, vous ressentez déjà ce léger retard. Une seconde est déjà perçue comme un frein.
Par conséquent, si nous divisons nos opérations de masse en rafales de 10 secondes, nous risquons de bloquer quelqu'un. Et cela fonctionnera pendant quelques secondes, et les gens le remarqueront déjà. Par conséquent, je préfère ne pas en faire plus d'une seconde. Mais en même temps, ne le décomposez pas très finement, car la surcharge de transaction sera perceptible. La base sera plus dure et d'autres problèmes différents peuvent survenir.
Nous choisissons la taille du pack. Dans chaque cas, nous pouvons le faire différemment. Peut être automatisé. Et nous sommes convaincus de l'efficacité du traitement d'un pack. C'est-à-dire que nous effectuons DELETE d'un pack ou UPDATE.
Au fait, tout ce dont je parle ne concerne pas seulement DELETE. Comme vous l'avez deviné, il s'agit de toutes les opérations en masse sur les données.
Et on voit que le plan est excellent. Vous pouvez voir l'analyse de l'index, l'analyse de l'index uniquement est encore meilleure. Et nous avons une petite quantité de données impliquées. Et moins d'une seconde remplit. Super.
Et nous devons encore nous assurer qu'il n'y a pas de dégradation. Il arrive que les premiers packs fonctionnent rapidement, puis ça empire, de pire en pire. Le processus est tel que vous devez tester beaucoup. C'est exactement à cela que servent les laboratoires de bases de données.
Et nous devons encore préparer quelque chose pour que cela nous permette de suivre cela correctement en production. Par exemple, nous pouvons écrire l'heure dans le journal, nous pouvons écrire où nous sommes maintenant et qui nous avons maintenant supprimé. Et cela nous permettra de comprendre ce qui se passe plus tard. Et en cas de problème, trouvez rapidement le problème.
Si nous devons vérifier l'efficacité des requêtes et que nous devons itérer plusieurs fois, alors il existe un autre bot. Il est déjà prêt. Il est utilisé quotidiennement par des dizaines de développeurs. Et il sait donner une énorme base de données de téraoctets sur demande en 30 secondes, votre propre copie. Et vous pouvez supprimer quelque chose là-bas et dire RESET, et le supprimer à nouveau. Vous pouvez l'expérimenter de cette façon. Je vois un avenir pour cette chose. Et nous le faisons déjà.
Quelles sont les stratégies de partitionnement ? Je vois 3 stratégies de partitionnement différentes que les développeurs du pack utilisent.
Le premier est très simple. Nous avons un identifiant numérique. Et décomposons-le en différents intervalles et travaillons avec cela. L'inconvénient est clair. Dans le premier segment, nous pouvons avoir 100 lignes de vraies ordures, dans les 5 lignes suivantes ou pas du tout, ou les 1 000 lignes se révéleront être des ordures. Travail très inégal, mais il est facile à casser. Ils ont pris la carte d'identité maximale et l'ont détruite. C'est une approche naïve.
La deuxième stratégie est une approche équilibrée. Il est utilisé dans Gitlab. Ils ont pris et scanné la table. Nous avons trouvé les limites des packs d'identification afin que chaque pack contienne exactement 10 000 enregistrements. Et mettez-les dans une file d'attente. Et puis nous traitons. Vous pouvez le faire dans plusieurs threads.
Soit dit en passant, dans la première stratégie, vous pouvez également le faire dans plusieurs threads. Ce n'est pas difficile.
Mais il existe une approche plus cool et meilleure. C'est la troisième stratégie. Et quand c'est possible, mieux vaut le choisir. Nous le faisons sur la base d'un index spécial. Dans ce cas, il s'agira très probablement d'un index en fonction de notre condition de poubelle et de notre ID. Nous inclurons l'ID afin qu'il s'agisse d'un balayage d'index uniquement afin que nous n'allions pas dans le tas.
Généralement, l'analyse d'index uniquement est plus rapide que l'analyse d'index.
Et on retrouve rapidement nos identifiants que l'on souhaite supprimer. BATCH_SIZE que nous sélectionnons à l'avance. Et non seulement nous les obtenons, nous les obtenons d'une manière spéciale et les piratons immédiatement. Mais on verrouille pour que s'ils sont déjà verrouillés, on ne les verrouille pas, mais on passe à autre chose et on prend les suivants. Ceci est pour le saut de mise à jour verrouillé. Cette super fonctionnalité de Postgres nous permet de travailler dans plusieurs threads si nous le voulons. C'est possible en un seul flux. Et ici, il y a un CTE - c'est une demande. Et nous avons une vraie suppression en cours au deuxième étage de ce CTE - returning *. Vous pouvez retourner l'identifiant, mais c'est mieux *si vous n'avez pas beaucoup de données sur chaque ligne.
Pourquoi en avons-nous besoin? C'est ce que nous devons rapporter. Nous avons maintenant supprimé tant de lignes en fait. Et nous avons des bordures par ID ou par created_at comme ceci. Vous pouvez faire min, max. Quelque chose d'autre peut être fait. Vous pouvez bourrer beaucoup ici. Et c'est très pratique pour la surveillance.
Il y a une autre note à propos de l'index. Si nous décidons que nous avons besoin d'un index spécial pour cette tâche, nous devons nous assurer qu'il ne gâche pas les mises à jour du tas uniquement. Autrement dit, Postgres a de telles statistiques. Cela peut être vu dans pg_stat_user_tables pour votre table. Vous pouvez voir si des mises à jour à chaud sont utilisées ou non.
Il y a des situations où votre nouvel index peut simplement les couper. Et vous avez toutes les autres mises à jour qui fonctionnent déjà, ralentissez. Pas seulement parce que l'index est apparu (chaque index ralentit un peu les mises à jour, mais un peu), mais ici il le ruine encore. Et il est impossible de faire une optimisation spéciale pour cette table. Cela arrive parfois. C'est une telle subtilité que peu de gens s'en souviennent. Et ce râteau est facile à marcher. Parfois, il arrive que vous ayez besoin de trouver une approche de l'autre côté et que vous vous débarrassiez toujours de ce nouvel index, ou de créer un autre index, ou d'une autre manière, par exemple, vous pouvez utiliser la deuxième méthode.
Mais c'est la stratégie la plus optimale, comment diviser en lots et tirer sur des lots avec une seule demande, supprimer un peu, etc.
Transactions longues
Autovide bloqué -
problème de blocage -
L'erreur #5 est énorme. Nikolai d'Okmeter a parlé de la surveillance de Postgres. La surveillance idéale de Postgres, malheureusement, n'existe pas. Certains sont plus proches, d'autres plus éloignés. Okmeter est assez proche de la perfection, mais il manque beaucoup de choses et doit être ajouté. Vous devez être prêt pour cela.
Par exemple, les tuples morts sont mieux surveillés. Si vous avez beaucoup de choses mortes dans le tableau, alors quelque chose ne va pas. Il vaut mieux réagir maintenant, sinon il peut y avoir dégradation, et on peut se coucher. Ça arrive.
S'il y a un grand IO, alors il est clair que ce n'est pas bon.
Les longues transactions aussi. Les transactions longues ne doivent pas être autorisées sur OLTP. Et voici un lien vers un extrait qui vous permet de prendre cet extrait et de déjà faire un suivi des transactions longues.
Pourquoi les transactions longues sont-elles mauvaises ? Parce que tous les verrous ne seront libérés qu'à la fin. Et on baise tout le monde. De plus, nous bloquons le vide automatique pour toutes les tables. Ce n'est pas bon du tout. Même si la redondance à chaud est activée sur la réplique, c'est toujours mauvais. En général, nulle part il ne vaut mieux éviter les longues transactions.
Si nous avons de nombreuses tables qui ne sont pas aspirées, nous devons avoir une alerte. Ici, une telle situation est possible. Nous pouvons affecter indirectement le fonctionnement de l'autovacuum. Ceci est un extrait d'Avito, que j'ai légèrement amélioré. Et cela s'est avéré être un outil intéressant pour voir ce que nous avons avec l'autovacuum. Par exemple, certaines tables y attendent et n'attendront pas leur tour. Il faut aussi le mettre en monitoring et avoir une alerte.
Et émet des blocs. Forêt de blocs d'arbres. J'aime prendre quelque chose à quelqu'un et l'améliorer. Ici, j'ai pris un CTE récursif cool de Data Egret qui montre une forêt d'arbres à cadenas. C'est un bon outil de diagnostic. Et sur sa base, vous pouvez également construire une surveillance. Mais cela doit être fait avec soin. Vous devez faire un petit statement_timeout pour vous-même. Et lock_timeout est souhaitable.
Parfois, toutes ces erreurs se produisent en somme.
À mon avis, la principale erreur ici est organisationnelle. C'est organisationnel, car la technique ne tire pas. C'est le numéro 2 - ils ont vérifié au mauvais endroit.
Nous avons vérifié au mauvais endroit, car nous n'avions pas de clone de production, ce qui est facile à vérifier. Un développeur peut ne pas avoir du tout accès à la production.
Et nous avons vérifié pas là. Si nous avions vérifié là-bas, nous l'aurions vu nous-mêmes. Le développeur a tout vu même sans DBA s'il l'a vérifié dans un bon environnement, où il y a la même quantité de données et un emplacement identique. Il aurait vu toute cette dégradation et il aurait honte.
En savoir plus sur l'aspirateur automatique. Après avoir fait un balayage massif de plusieurs millions de lignes, nous devons encore faire REPACK. Ceci est particulièrement important pour les index. Ils se sentiront mal après que nous ayons tout nettoyé là-bas.
Et si vous souhaitez reprendre le travail de nettoyage quotidien, je vous suggérerais de le faire plus souvent, mais plus petit. Cela peut être une fois par minute ou même plus souvent un peu. Et vous devez surveiller deux choses : que cette chose n'a pas d'erreurs et qu'elle n'est pas en retard. L'astuce que j'ai montrée va simplement résoudre ce problème.
Ce que nous faisons est open source. Il est posté sur GitLab. Et nous faisons en sorte que les gens puissent vérifier même sans DBA. Nous faisons un laboratoire de base de données, c'est-à-dire que nous appelons le composant de base sur lequel Joe travaille actuellement. Et vous pouvez récupérer une copie de la production. Maintenant il y a une implémentation de Joe pour slack, vous pouvez y dire : « expliquez telle ou telle requête » et obtenez immédiatement le résultat pour votre copie de la base de données. Vous pouvez même SUPPRIMER là-bas, et personne ne le remarquera.
Disons que vous avez 10 téraoctets, nous faisons également du laboratoire de base de données 10 téraoctets. Et avec des bases de données simultanées de 10 téraoctets, 10 développeurs peuvent travailler simultanément. Chacun peut faire ce qu'il veut. Peut supprimer, déposer, etc. C'est un tel fantasme. Nous en reparlerons demain.
C'est ce qu'on appelle le provisionnement fin. Il s'agit d'un approvisionnement subtil. C'est une sorte de fantaisie qui supprime considérablement les retards de développement, de test et rend le monde meilleur à cet égard. Autrement dit, cela vous permet simplement d'éviter les problèmes liés aux opérations en bloc.
Exemple : base de données de 5 téraoctets, obtention d'une copie en moins de 30 secondes. Et cela ne dépend même pas de la taille, c'est-à-dire que peu importe le nombre de téraoctets.
Aujourd'hui, vous pouvez aller à et explorez nos outils. Vous pouvez vous inscrire pour voir ce qu'il y a. Vous pouvez installer ce bot. C'est gratuit. Écrire.
des questions
Très souvent, dans des situations réelles, il s'avère que les données qui doivent rester dans la table sont bien inférieures à celles qui doivent être supprimées. Autrement dit, dans une telle situation, il est souvent plus facile d'implémenter une telle approche, lorsqu'il est plus facile de créer un nouvel objet, d'y copier uniquement les données nécessaires et de regrouper l'ancienne table. Il est clair qu'une approche programmatique est nécessaire pour ce moment, alors que vous serez en train de changer. Comment est cette approche ?
C'est une très bonne approche et une très bonne tâche. C'est très similaire à ce que fait pg_repack, c'est très similaire à ce que vous devez faire lorsque vous créez des identifiants de 4 octets. De nombreux frameworks l'ont fait il y a quelques années, et seules les plaques ont grandi, et elles doivent être converties en 8 octets.
Cette tâche est assez difficile. Nous l'avons fait. Et il faut être très prudent. Il y a des serrures, etc. Mais cela se fait. Autrement dit, l'approche standard consiste à utiliser pg_repack. Vous déclarez une telle étiquette. Et avant de commencer à y télécharger des données d'instantané, vous déclarez également une plaque qui suit toutes les modifications. Il y a une astuce que vous ne pouvez même pas suivre certains changements. Il y a des subtilités. Et puis vous changez par roulement des changements. Il y aura une courte pause lorsque nous fermerons tout le monde, mais en général, cela se fait.
Si vous regardez pg_repack sur GitHub, alors là, quand il y avait une tâche pour convertir un ID de int 4 en int 8, alors il y avait une idée d'utiliser pg_repack lui-même. C'est également possible, mais c'est un peu un hack, mais cela fonctionnera aussi pour cela. Vous pouvez intervenir dans le déclencheur utilisé par pg_repack et y dire : "Nous n'avons pas besoin de ces données", c'est-à-dire que nous ne transférons que ce dont nous avons besoin. Et puis il change juste et c'est tout.
Avec cette approche, nous obtenons toujours une deuxième copie de la table, dans laquelle les données sont déjà indexées et empilées de manière très uniforme avec de beaux index.
Le ballonnement n'est pas présent, c'est une bonne approche. Mais je sais qu'il y a des tentatives pour développer une automatisation pour cela, c'est-à-dire pour créer une solution universelle. Je peux vous mettre en contact avec cette automatisation. Il est écrit en Python, ce qui est une bonne chose.
Je suis juste un peu du monde de MySQL, alors je suis venu écouter. Et nous utilisons cette approche.
Mais ce n'est que si nous avons 90 %. Si nous avons 5%, alors ce n'est pas très bon de l'utiliser.
Merci pour le rapport ! S'il n'y a pas de ressources pour faire une copie complète de prod, existe-t-il un algorithme ou une formule pour calculer la charge ou la taille ?
Bonne question. Jusqu'à présent, nous sommes en mesure de trouver des bases de données de plusieurs téraoctets. Même si le matériel n'y est pas le même, par exemple, moins de mémoire, moins de processeur et les disques ne sont pas exactement les mêmes, mais nous le faisons quand même. S'il n'y a absolument nulle part, alors vous devez réfléchir. Laissez-moi réfléchir jusqu'à demain, vous êtes venu, nous allons parler, c'est une bonne question.
Merci pour le rapport ! Au début, vous avez commencé à parler du fait qu'il existe un Postgres cool, qui a telles ou telles limitations, mais il se développe. Et tout cela n'est en gros qu'une béquille. Tout cela n'est-il pas en conflit avec le développement de Postgres lui-même, dans lequel certains DELETE déférents apparaîtront ou quelque chose d'autre qui devrait maintenir à un niveau bas ce que nous essayons de salir avec certains de nos étranges moyens ici ?
Si nous disions en SQL de supprimer ou de mettre à jour de nombreux enregistrements en une seule transaction, alors comment Postgres peut-il le distribuer là-bas ? Nous sommes physiquement limités dans nos opérations. Nous le ferons encore longtemps. Et nous verrouillerons à ce moment, etc.
Fait avec des index.
Je peux supposer que le même réglage de point de contrôle pourrait être automatisé. Un jour ça pourrait l'être. Mais alors je ne comprends pas vraiment la question.
La question est, y a-t-il un tel vecteur de développement qui va ici et là, et ici le vôtre va parallèlement ? Ceux. N'y ont-ils pas encore pensé ?
J'ai parlé des principes qui peuvent être utilisés maintenant. Il y a un autre robot , avec cela, vous pouvez effectuer un réglage automatisé des points de contrôle. Sera-t-il un jour dans Postgres ? Je ne sais pas, ça n'a même pas encore été discuté. Nous en sommes encore loin. Mais il y a des scientifiques qui créent de nouveaux systèmes. Et ils nous poussent dans des index automatiques. Il y a des évolutions. Par exemple, vous pouvez regarder le réglage automatique. Il sélectionne automatiquement les paramètres. Mais il ne fera pas encore le réglage des points de contrôle pour vous. C'est-à-dire qu'il reprendra pour les performances, la mémoire tampon du shell, etc.
Et pour le réglage des points de contrôle, vous pouvez le faire : si vous avez un millier de clusters et différents matériels, différentes machines virtuelles dans le cloud, vous pouvez utiliser notre bot faire de l'automatisation. Et max_wal_size sera automatiquement sélectionné en fonction de vos paramètres cibles. Mais jusqu'à présent, ce n'est même pas proche dans le noyau, malheureusement.
Bon après-midi Vous avez parlé des dangers des longues transactions. Vous avez dit que l'autovacuum est bloqué en cas de suppressions. Sinon, comment cela nous fait-il du mal ? Parce qu'on parle plus de libérer de l'espace et de pouvoir l'utiliser. Que nous manque-t-il d'autre ?
Le vide automatique n'est peut-être pas le plus gros problème ici. Et le fait qu'une transaction longue puisse verrouiller d'autres transactions, cette possibilité est plus dangereuse. Elle peut ou non se rencontrer. Si elle se rencontre, alors ça peut être très mauvais. Et avec l'autovacuum - c'est aussi un problème. Il y a deux problèmes avec les transactions longues dans OLTP : les verrous et le vide automatique. Et si vous avez activé la rétroaction de secours à chaud sur la réplique, vous recevrez toujours un verrou de vide automatique sur le maître, il arrivera de la réplique. Mais au moins, il n'y aura pas de serrures. Et il y aura des loks. Nous parlons de modifications de données, les verrous sont donc un point important ici. Et si tout cela dure très longtemps, de plus en plus de transactions sont bloquées. Ils peuvent voler les autres. Et les arbres lok apparaissent. J'ai fourni un lien vers l'extrait. Et ce problème devient plus perceptible plus rapidement que le problème de l'autovacuum, qui ne peut que s'accumuler.
Merci pour le rapport ! Vous avez commencé votre rapport en disant que vous n'avez pas fait le bon test. Nous avons continué notre idée qu'il fallait prendre le même équipement, avec la base de la même manière. Disons que nous avons donné une base au développeur. Et il a accédé à la demande. Et il semble aller bien. Mais il ne vérifie pas pour le direct, mais pour le direct, par exemple, nous avons une charge de 60 à 70 %. Et même si on utilise ce réglage, ça ne marche pas très bien.
Avoir un expert dans l'équipe et utiliser des experts DBA qui peuvent prédire ce qui se passera avec une charge de fond réelle est important. Lorsque nous venons de conduire nos changements propres, nous voyons l'image. Mais une approche plus poussée, quand on a refait la même chose, mais avec une charge simulée avec la production. C'est plutôt cool. Jusque-là, il faut grandir. C'est comme un adulte. Nous avons juste regardé ce que nous avons et aussi si nous avons suffisamment de ressources. C'est une bonne question.
Lorsque nous effectuons déjà une sélection de déchets et que nous avons, par exemple, un indicateur supprimé
C'est ce que fait automatiquement autovacuum dans Postgres.
Oh, est-ce qu'il le fait ?
Autovacuum est le ramasse-miettes.
Je vous remercie!
Merci pour le rapport ! Existe-t-il une option pour concevoir immédiatement une base de données avec un partitionnement de telle sorte que toutes les ordures se salissent de la table principale quelque part sur le côté ?
Bien sûr il y a.
Est-il alors possible de se protéger si on a verrouillé une table qui ne doit pas être utilisée ?
Bien sûr avoir. Mais c'est comme une question de poule et d'œuf. Si nous savons tous ce qui se passera dans le futur, alors, bien sûr, nous ferons tout cool. Mais le métier évolue, il y a de nouvelles chroniques, de nouvelles demandes. Et puis – oups, nous voulons le supprimer. Mais cette situation idéale, dans la vie, elle se produit, mais pas toujours. Mais dans l'ensemble c'est une bonne idée. Il suffit de tronquer et c'est tout.
Source: habr.com