Bases de la surveillance PostgreSQL. Alexeï Lessovski

Je vous suggère de vous familiariser avec la transcription du rapport d'Alexey Lesovsky de Data Egret "Basics of PostgreSQL Monitoring"

Dans ce rapport, Alexey Lesovsky parlera des points clés des statistiques postgres, de leur signification et des raisons pour lesquelles elles devraient être incluses dans le suivi ; sur les graphiques qui doivent être surveillés, comment les ajouter et comment les interpréter. Le rapport sera utile aux administrateurs de bases de données, aux administrateurs système et aux développeurs intéressés par le dépannage de Postgres.

Je m'appelle Alexey Lesovsky, je représente Data Egret.

Quelques mots sur moi. J'ai commencé il y a longtemps en tant qu'administrateur système.

J'ai administré toutes sortes de Linux différents, j'ai fait diverses choses liées à Linux, c'est-à-dire la virtualisation, la surveillance, j'ai travaillé avec des proxys, etc. Mais à un moment donné, je me suis davantage impliqué dans les bases de données, PostgreSQL. Je l'aimais vraiment. Et à un moment donné, j'ai commencé à utiliser PostgreSQL la majeure partie de mon temps de travail. Et c’est ainsi que progressivement je suis devenu un administrateur de base de données PostgreSQL.

Et tout au long de mon parcours, j'ai toujours été intéressé par les sujets de statistiques, de surveillance, de télémétrie. Et quand j'étais administrateur système, je travaillais très dur sur Zabbix. Et j'ai écrit un petit ensemble de scripts comme extensions zabbix. Il était très populaire à son époque. Et là, il était possible de surveiller des choses très différentes et importantes, non seulement Linux, mais aussi divers composants.

Maintenant, je fais déjà PostgreSQL. J'écris déjà autre chose qui vous permet de travailler avec les statistiques PostgreSQL. On l'appelle pgCentre (article sur Habré - Postgres stat sans nerfs ni tension).

Une petite introduction. Quelles sont les situations avec nos clients, avec nos clients ? Il y a une sorte d'accident associé à la base de données. Et lorsque la base de données a déjà été restaurée, le chef du département ou le responsable du développement vient et dit : « Mes amis, nous devrions surveiller la base de données, car quelque chose de grave s'est produit et il est nécessaire que cela ne se reproduise pas à l'avenir. Et ici commence le processus intéressant de choix d'un système de surveillance ou d'adaptation d'un système de surveillance existant afin que vous puissiez surveiller votre base de données - PostgreSQL, MySQL ou quelques autres. Et des collègues commencent à proposer : « J'ai entendu dire qu'il existe telle ou telle base de données. Utilisons-le." Les collègues commencent à se disputer. Et au final, il s'avère que nous choisissons une sorte de base de données, mais la surveillance PostgreSQL y est plutôt mal représentée et nous devons toujours finir quelque chose. Prenez quelques référentiels de GitHub, clonez-les, adaptez les scripts, ajustez-les d'une manière ou d'une autre. Et à la fin, cela se résume à une sorte de travail manuel.

Par conséquent, dans ce rapport, je vais essayer de vous donner quelques connaissances sur la façon de choisir la surveillance non seulement pour PostgreSQL, mais également pour la base de données. Et de donner les connaissances qui vous permettront de terminer votre surveillance afin d'en tirer des bénéfices, afin que vous puissiez surveiller votre base de données avec profit afin de prévenir toute situation d'urgence à venir qui pourrait survenir à temps.

Et ces idées qui seront dans ce rapport peuvent être directement adaptées à n'importe quelle base de données, qu'il s'agisse d'un SGBD ou de noSQL. Par conséquent, non seulement PostgreSQL ici, mais il y aura de nombreuses recettes sur la façon de procéder dans PostgreSQL. Il y aura des exemples de requêtes, des exemples d'entités dont PostgreSQL dispose pour la surveillance. Et si votre SGBD a les mêmes choses qui vous permettent de les mettre en surveillance, vous pouvez aussi les adapter, les ajouter et tout ira bien.

je ne signalerai pas
parler de la façon de fournir et de stocker les métriques. Je ne dirai rien sur le post-traitement des données et leur fourniture à l'utilisateur. Et je ne dirai rien sur l'alerte.
Mais au cours de l'histoire, je montrerai différentes captures d'écran de surveillances existantes, d'une manière ou d'une autre je les critiquerai. Néanmoins, j'essaierai de ne pas citer de marques afin de ne pas créer de publicité ou d'anti-publicité pour ces produits. Par conséquent, toutes les coïncidences sont aléatoires et restent le fruit de votre imagination.

Tout d’abord, comprenons ce qu’est la surveillance. La surveillance est une chose très importante à avoir. Tout le monde comprend cela. Mais en même temps, la surveillance n'est pas liée à un produit commercial et n'affecte pas directement les bénéfices de l'entreprise, c'est pourquoi la surveillance est toujours accordée sur une base résiduelle. Si nous avons le temps, alors nous sommes engagés dans la surveillance, s'il n'y a pas de temps, alors d'accord, nous le mettrons dans l'arriéré et un jour nous reviendrons à ces tâches.

Par conséquent, d'après notre pratique, lorsque nous arrivons chez des clients, le suivi est souvent sous-développé et n'apporte rien d'intéressant qui nous aiderait à faire un meilleur travail avec la base de données. Et donc le suivi doit toujours être terminé.

Les bases de données sont des éléments tellement complexes que vous devez également les surveiller, car elles constituent un référentiel d'informations. Et l'information est très importante pour l'entreprise, elle ne peut en aucun cas être perdue. Mais en même temps, les bases de données sont des logiciels très complexes. Ils sont constitués de nombreux composants. Et bon nombre de ces composants doivent être surveillés.

Si nous parlons spécifiquement de PostgreSQL, il peut alors être représenté comme un tel schéma, composé d'un grand nombre de composants. Ces composants interagissent les uns avec les autres. Et en même temps, PostgreSQL dispose du sous-système dit Stats Collector, qui vous permet de collecter des statistiques sur le fonctionnement de ces sous-systèmes et de fournir une interface à l'administrateur ou à l'utilisateur afin qu'il puisse visualiser ces statistiques.

Ces statistiques sont présentées sous la forme d'un ensemble de fonctions et de vues (vue). Ils peuvent également être appelés tables. Autrement dit, en utilisant un client psql standard, vous pouvez vous connecter à la base de données, sélectionner ces fonctions et vues et obtenir des chiffres spécifiques sur le fonctionnement des sous-systèmes PostgreSQL.

Vous pouvez ajouter ces chiffres à votre système de surveillance préféré, dessiner des graphiques, ajouter des fonctionnalités et obtenir des analyses à long terme.

Mais dans ce rapport, je ne couvrirai pas toutes ces fonctions sans exception, car cela peut prendre une journée entière. Je mentionnerai littéralement deux, trois ou quatre éléments et je vous dirai comment ils contribuent à améliorer le suivi.

Et si nous parlons de surveillance de la base de données, que faut-il surveiller ? Tout d'abord, nous devons surveiller la disponibilité, car la base de données est un service qui donne accès aux données aux clients et nous devons surveiller la disponibilité, ainsi que fournir certaines de ses caractéristiques qualitatives et quantitatives.

Nous devons également surveiller les clients qui se connectent à notre base de données, car il peut s'agir à la fois de clients normaux et de clients nuisibles susceptibles d'endommager la base de données. Ils doivent également être surveillés et suivis.

Lorsque les clients se connectent à la base de données, il est évident qu'ils commencent à travailler avec nos données, nous devons donc surveiller comment les clients travaillent avec les données : avec quelles tables, dans une moindre mesure avec quels index. Autrement dit, nous devons évaluer la charge de travail créée par nos clients.

Mais la charge de travail consiste bien entendu aussi en demandes. Les applications se connectent à la base de données, accèdent aux données à l'aide de requêtes, il est donc important d'évaluer quelles requêtes nous avons dans la base de données, de surveiller leur adéquation, qu'elles ne soient pas tordues, que certaines options doivent être réécrites et créées pour qu'elles fonctionnent plus rapidement et avec de meilleures performances.

Et puisque nous parlons de base de données, la base de données est toujours constituée de processus en arrière-plan. Les processus en arrière-plan maintiennent les performances de la base de données à un bon niveau, ils nécessitent donc une certaine quantité de ressources pour s'exécuter. Et en même temps, ils peuvent chevaucher les ressources des demandes des clients, de sorte que le travail gourmand des processus en arrière-plan peut affecter directement les performances des demandes des clients. Par conséquent, ils doivent également être surveillés et suivis afin qu’il n’y ait pas de distorsions en termes de processus de fond.

Et tout cela en termes de surveillance de la base de données reste dans la métrique du système. Mais étant donné que la majeure partie de notre infrastructure est hébergée dans le cloud, les métriques système d'un hôte individuel passent toujours au second plan. Mais dans les bases de données, ils restent pertinents et, bien entendu, il est également nécessaire de surveiller les métriques du système.

Avec les métriques du système, tout va plus ou moins bien, tous les systèmes de surveillance modernes prennent déjà en charge ces métriques, mais en général, certains composants ne suffisent toujours pas et certains éléments doivent être ajoutés. Je les aborderai également, plusieurs diapositives y seront consacrées.

Le premier point du plan est l'accessibilité. Qu’est-ce que l’accessibilité ? Selon moi, la disponibilité est la capacité de la base à servir les connexions, c'est-à-dire que la base est élevée et qu'elle accepte, en tant que service, les connexions des clients. Et cette accessibilité peut être évaluée par certaines caractéristiques. Ces caractéristiques sont très pratiques à afficher sur les tableaux de bord.

Tout le monde sait ce que sont les tableaux de bord. C’est à ce moment-là que vous avez jeté un coup d’œil à l’écran, qui résumait les informations nécessaires. Et vous pouvez déjà déterminer immédiatement s'il y a un problème dans la base de données ou non.
Par conséquent, la disponibilité de la base de données et d’autres caractéristiques clés doivent toujours être indiquées sur les tableaux de bord afin que ces informations soient à portée de main, toujours avec vous. Certains détails supplémentaires qui aident déjà à enquêter sur des incidents, dans l'enquête sur certaines situations d'urgence, doivent déjà être placés sur des tableaux de bord secondaires, ou cachés dans des liens de recherche menant à des systèmes de surveillance tiers.

Un exemple d'un système de surveillance connu. C'est un système de surveillance très cool. Il collecte beaucoup de données, mais de mon point de vue, il a un étrange concept de tableaux de bord. Il y a un lien "Créer un tableau de bord". Mais lorsque vous créez un tableau de bord, vous créez une liste à deux colonnes, une liste de graphiques. Et lorsque vous avez besoin de regarder quelque chose, vous commencez à cliquer, à faire défiler, à rechercher le graphique souhaité avec la souris. Et cela prend du temps, c’est à dire qu’il n’existe pas de tableaux de bord en tant que tels. Il n'y a que des listes de graphiques.

Que faut-il ajouter à ces tableaux de bord ? Vous pouvez commencer par une caractéristique telle que le temps de réponse. PostgreSQL a la vue pg_stat_statements. Elle est désactivée par défaut, mais c'est l'une des vues système importantes qui doit toujours être activée et utilisée. Il stocke des informations sur toutes les requêtes en cours d'exécution qui ont été exécutées dans la base de données.

En conséquence, nous pouvons partir du fait que nous pouvons prendre le temps d'exécution total de toutes les requêtes et le diviser par le nombre de requêtes en utilisant les champs ci-dessus. Mais c'est une température tellement moyenne à l'hôpital. Nous pouvons nous appuyer sur d'autres champs - le temps minimum d'exécution de la requête, le maximum et la médiane. Et nous pouvons même construire des centiles, PostgreSQL a les fonctions correspondantes pour cela. Et nous pouvons obtenir des chiffres qui caractérisent le temps de réponse de notre base de données pour les requêtes déjà terminées, c'est-à-dire que nous n'exécutons pas la fausse requête 'select 1' et surveillons le temps de réponse, mais nous analysons le temps de réponse pour les requêtes déjà terminées et dessinons soit une figure distincte, ou nous construisons un graphique basé sur celle-ci.

Il est également important de suivre le nombre d’erreurs générées actuellement par le système. Et pour cela, vous pouvez utiliser la vue pg_stat_database. Nous ciblons le champ xact_rollback. Ce champ affiche non seulement le nombre d'annulations qui se produisent dans la base de données, mais prend également en compte le nombre d'erreurs. Relativement parlant, nous pouvons afficher ce chiffre dans notre tableau de bord et voir combien d'erreurs nous avons actuellement. S'il y a beaucoup d'erreurs, c'est déjà une bonne raison d'examiner les journaux et de voir de quel type d'erreurs il s'agit et pourquoi elles se produisent, puis d'investir et de les résoudre.

Vous pouvez ajouter un tachymètre. Il s'agit du nombre de transactions par seconde et du nombre de requêtes par seconde. Relativement parlant, vous pouvez utiliser ces chiffres comme performances actuelles de votre base de données et observer s'il y a des pics de requêtes, des pics de transactions ou, à l'inverse, si la base de données est sous-chargée en raison d'une sorte de backend tombé en panne. Il est important de toujours regarder ce chiffre et de se rappeler que pour notre projet, une telle performance est normale, et que les valeurs ci-dessus et ci-dessous sont déjà en quelque sorte problématiques et incompréhensibles, ce qui signifie que nous devons examiner pourquoi de tels chiffres .

Afin d'estimer le nombre de transactions, on peut à nouveau se référer à la vue pg_stat_database. Nous pouvons ajouter le nombre de commits et le nombre de rollbacks pour obtenir le nombre de transactions par seconde.

Tout le monde comprend que plusieurs demandes peuvent tenir dans une seule transaction ? Par conséquent, TPS et QPS sont légèrement différents.

Le nombre de requêtes par seconde peut être obtenu à partir de pg_stat_statements et calculer simplement la somme de toutes les requêtes exécutées. Il est clair que nous comparons la valeur actuelle avec la précédente, soustrayons, obtenons le delta, obtenons le montant.

Vous pouvez ajouter des mesures supplémentaires si vous le souhaitez, qui aident également à évaluer la disponibilité de notre base de données et à suivre s'il y a eu des temps d'arrêt.

L'une de ces mesures est la disponibilité. Mais la disponibilité dans PostgreSQL est un peu délicate. Je vais vous dire pourquoi. Lorsque PostgreSQL démarre, il commence à signaler la disponibilité. Mais si à un moment donné, par exemple, une tâche était en cours d'exécution la nuit, un tueur de MOO est venu et a mis fin de force au processus enfant de PostgreSQL, alors dans ce cas, PostgreSQL met fin à la connexion de tous les clients, réinitialise la zone de mémoire fragmentée et démarre la récupération à partir de le dernier point de contrôle. Et tant que dure cette récupération à partir du point de contrôle, la base de données n'accepte pas les connexions, c'est-à-dire que cette situation peut être évaluée comme un temps d'arrêt. Mais cela ne réinitialisera pas le compteur de disponibilité, car il prend en compte l'heure à laquelle le postmaster a été démarré dès le premier instant. Par conséquent, de telles situations peuvent être évitées.

Vous devez également surveiller le nombre de travailleurs du vide. Tout le monde sait ce qu’est l’autovacuum dans PostgreSQL ? Il s'agit d'un sous-système intéressant dans PostgreSQL. De nombreux articles ont été écrits à ce sujet, de nombreux rapports ont été réalisés. Beaucoup de discussions sur le vide, comment il devrait fonctionner. Beaucoup le considèrent comme un mal nécessaire. Mais il est. Il s'agit d'une sorte de garbage collector qui nettoie les versions obsolètes des lignes qui ne sont nécessaires à aucune des transactions et libère de l'espace dans les tables et les index pour les nouvelles lignes.

Pourquoi faut-il le surveiller ? Parce que le vide fait parfois très mal. Cela consomme une grande quantité de ressources et les demandes des clients commencent à en souffrir.

Et cela doit être surveillé via la vue pg_stat_activity, dont je parlerai dans la section suivante. Cette vue montre l'activité actuelle dans la base de données. Et grâce à cette activité, nous pouvons suivre le nombre d’aspirateurs qui fonctionnent actuellement. Nous pouvons surveiller les vides et voir que si nous avons dépassé la limite, c'est alors l'occasion d'examiner les paramètres de PostgreSQL et d'optimiser d'une manière ou d'une autre le fonctionnement du vide.

Une autre caractéristique de PostgreSQL est que PostgreSQL en a vraiment marre des transactions longues. Surtout des transactions qui traînent pendant longtemps et ne font rien. Il s'agit de ce que l'on appelle les statistiques d'inactivité dans la transaction. Une telle transaction tient des verrous, elle empêche le vide de fonctionner. Et en conséquence, les tables gonflent, elles grossissent. Et les requêtes qui fonctionnent avec ces tables commencent à fonctionner plus lentement, car vous devez déplacer toutes les anciennes versions des lignes de la mémoire vers le disque et inversement. Par conséquent, l’heure, la durée des transactions les plus longues, les demandes de vide les plus longues doivent également être surveillées. Et si nous voyons certains processus qui s'exécutent depuis très longtemps, depuis plus de 10-20-30 minutes pour un chargement OLTP, alors nous devons y prêter attention et les forcer à se terminer, ou optimiser l'application pour que ils ne sont pas appelés et ne restent pas aussi longtemps en attente. Pour une charge analytique, 10-20-30 minutes sont normales, il y en a aussi des plus longues.

Nous avons ensuite l'option avec les clients connectés. Lorsque nous avons déjà créé un tableau de bord et y avons publié des mesures d'accessibilité clés, nous pouvons également y ajouter des informations supplémentaires sur les clients connectés.

Les informations sur les clients connectés sont importantes car, du point de vue de PostgreSQL, il existe différents types de clients. Il y a des bons clients et il y a des mauvais clients.

Un exemple simple. Par client, j'entends l'application. L'application s'est connectée à la base de données et commence immédiatement à y envoyer ses requêtes, la base de données les traite et les exécute, et renvoie les résultats au client. Ce sont de bons et bons clients.

Il y a des situations où le client est connecté, il maintient la connexion, mais ne fait rien. Il est à l'état de repos.

Mais il y a de mauvais clients. Par exemple, le même client s'est connecté, a ouvert une transaction, a fait quelque chose dans la base de données, puis est entré dans le code, par exemple pour accéder à une source externe ou pour y traiter les données reçues. Mais en même temps, il n’a pas conclu la transaction. Et la transaction se bloque dans la base de données et maintient le verrou sur la ligne. C'est un mauvais état. Et si soudainement l'application quelque part à l'intérieur tombe par une exception (Exception), alors la transaction peut rester ouverte pendant très longtemps. Et cela affecte directement les performances de PostgreSQL. PostgreSQL fonctionnera plus lentement. Par conséquent, il est important de suivre ces clients à temps et de mettre fin à leur travail de force. Et vous devez optimiser votre application pour éviter de telles situations.

D'autres mauvais clients attendent des clients. Mais ils deviennent mauvais à cause des circonstances. Par exemple, une simple transaction inactive : il peut ouvrir une transaction, prendre des verrous sur certaines lignes, puis elle tombera quelque part dans le code, laissant une transaction suspendue. Un autre client viendra demander les mêmes données, mais il rencontrera un verrou, car cette transaction suspendue détient déjà des verrous sur certaines lignes nécessaires. Et la deuxième transaction se bloquera par anticipation lorsque la première transaction sera terminée ou que son administrateur la fermera de force. Ainsi, les transactions en attente peuvent s'accumuler et dépasser la limite de connexion à la base de données. Et lorsque la limite est atteinte, l'application ne peut plus fonctionner avec la base de données. Il s'agit déjà d'une situation d'urgence pour le projet. Par conséquent, les mauvais clients doivent être suivis et traités en temps opportun.

Un autre exemple de surveillance. Et voici un tableau de bord décent. Il y a des informations sur les connexions d'en haut. Connexion DB - 8 pièces. Et c'est tout. Nous n'avons aucune information sur les clients actifs, ceux qui sont simplement inactifs et ne font rien. Il n'y a aucune information sur les transactions suspendues et les connexions en attente, c'est-à-dire c'est un tel chiffre qui montre le nombre de connexions et c'est tout. Et puis devinez par vous-même.

Par conséquent, pour ajouter ces informations à la surveillance, vous devez vous référer à la vue système pg_stat_activity. Si vous passez beaucoup de temps dans PostgreSQL, alors c'est une très bonne vue qui devrait devenir votre amie, car elle montre l'activité actuelle dans PostgreSQL, c'est-à-dire ce qui s'y passe. Il existe une ligne distincte pour chaque processus qui affiche des informations sur ce processus : à partir de quel hôte la connexion a été établie, sous quel utilisateur, sous quel nom, quand la transaction a été lancée, quelle demande est en cours d'exécution, quelle demande a été exécutée en dernier. Et, en conséquence, nous pouvons évaluer l'état du client par le champ statistique. Relativement parlant, nous pouvons regrouper par ce champ et obtenir les statistiques qui sont maintenant dans la base de données et le nombre de connexions avec cette statistique dans la base de données. Et nous pouvons envoyer les chiffres déjà reçus à notre surveillance et dessiner des graphiques dessus.
Il est également important d'évaluer la durée de la transaction. J'ai déjà dit qu'il est important d'évaluer la durée des vides, mais les transactions sont également évaluées de la même manière. Il existe des champs xact_start et query_start. Ils affichent, relativement parlant, l'heure de début de la transaction et l'heure de début de la demande. Nous prenons la fonction now(), qui affiche l'horodatage actuel, et soustrayons les horodatages de la transaction et de la demande. Et on obtient la durée de la transaction, la durée de la demande.

Si nous voyons des transactions longues, nous devrions déjà les terminer. Pour un chargement OLTP, les transactions longues durent déjà plus de 1-2-3 minutes. Pour une charge OLAP, les transactions longues sont normales, mais si elles durent plus de deux heures, c'est aussi le signe que nous avons un biais quelque part.

Une fois que les clients se sont connectés à la base de données, ils commencent à travailler avec nos données. Ils accèdent aux tables, ils accèdent aux index pour obtenir les données d'une table. Et il est important d’évaluer la manière dont les clients utilisent ces données.

Cela est nécessaire pour évaluer notre charge de travail et comprendre approximativement quelles tables nous avons les « plus chaudes ». Par exemple, cela est nécessaire dans les situations où nous souhaitons placer des tables « chaudes » sur une sorte de stockage SSD rapide. Par exemple, certaines tables d'archives que nous n'avons pas utilisées depuis longtemps peuvent être transférées vers une sorte d'archive « froide », sur des disques SATA et laissées là-bas, elles seront accessibles selon les besoins.

Il est également utile pour détecter les anomalies après toute version et déploiement. Disons que le projet a déployé une nouvelle fonctionnalité. Par exemple, nous avons ajouté une nouvelle fonctionnalité pour travailler avec la base de données. Et si nous construisons des graphiques à l’aide de tableaux, nous pouvons facilement détecter ces anomalies sur ces graphiques. Par exemple, mettez à jour les rafales ou supprimez des rafales. Ce sera très visible.

Il est également possible de détecter des anomalies de statistiques « flottantes ». Qu'est-ce que ça veut dire? PostgreSQL dispose d'un planificateur de requêtes très puissant et très performant. Et les développeurs consacrent beaucoup de temps à son développement. Comment travaille-t-il ? Afin de construire de bons plans, PostgreSQL collecte des statistiques sur la répartition des données dans des tables avec un certain intervalle de temps, avec une certaine périodicité. Ce sont les valeurs les plus fréquentes : le nombre de valeurs uniques, des informations sur NULL dans le tableau, de nombreuses informations.

Sur la base de ces statistiques, le planificateur crée plusieurs requêtes, choisit la plus optimale et utilise ce plan de requête pour exécuter la requête elle-même et renvoyer les données.

Et il arrive que les statistiques « flottent ». Les données qualitatives et quantitatives ont changé d’une manière ou d’une autre dans le tableau, mais les statistiques n’ont pas été collectées. Et les plans élaborés ne sont peut-être pas optimaux. Et si nos plans s'avèrent sous-optimaux en termes de surveillance collectée, selon les tableaux, nous pourrons constater ces anomalies. Par exemple, quelque part, les données ont changé qualitativement et au lieu de l'index, un passage séquentiel à travers le tableau a commencé à être utilisé, c'est-à-dire si la requête ne doit renvoyer que 100 lignes (il existe une limite de 100), alors une énumération complète sera effectuée pour cette requête. Et cela a toujours un très mauvais effet sur les performances.

Et nous pouvons le constater dans la surveillance. Et déjà, regardez cette requête, expliquez-la, collectez des statistiques, créez un nouvel index supplémentaire. Et répondez déjà à ce problème. C’est donc important.

Un autre exemple de surveillance. Je pense que beaucoup de gens le reconnaissent car il est très populaire. Qui utilise dans ses projets Prométhée? Et qui utilise ce produit en conjonction avec Prometheus ? Le fait est que dans le référentiel standard de cette surveillance, il existe un tableau de bord pour travailler avec PostgreSQL - postgres_exporter Prométhée. Mais il y a un mauvais détail ici.

Il existe plusieurs graphiques. Et les octets sont spécifiés comme unité, c'est-à-dire qu'il y a 5 graphiques. Il s'agit d'insérer des données, de mettre à jour des données, de supprimer des données, de récupérer des données et de renvoyer des données. Les octets sont spécifiés comme dimension unitaire. Mais le fait est que les statistiques de PostgreSQL renvoient des données sous forme de tuple (lignes). Et, par conséquent, ces graphiques sont un très bon moyen de sous-estimer votre charge de travail plusieurs fois, des dizaines de fois, car un tuple n'est pas un octet, un tuple est une chaîne, il fait beaucoup d'octets et il est toujours de longueur variable. Autrement dit, calculer la charge de travail en octets à l’aide de tuples est une tâche irréaliste ou très difficile. Par conséquent, lorsque vous utilisez un tableau de bord ou une surveillance intégrée, il est toujours important de comprendre qu'il fonctionne correctement et vous renvoie des données correctement évaluées.

Comment obtenir des statistiques sur ces tableaux ? Pour ce faire, PostgreSQL dispose d'une famille de vues. Et la vue principale est pg_stat_user_tables. User_tables - cela signifie que les tables sont créées au nom de l'utilisateur. En revanche, il existe des vues système utilisées par PostgreSQL lui-même. Et il existe un tableau récapitulatif Alltables, qui inclut à la fois le système et l'utilisateur. Vous pouvez commencer par celui que vous préférez.

Les champs ci-dessus peuvent être utilisés pour estimer le nombre d'insertions, de mises à jour et de suppressions. L'exemple de tableau de bord que j'ai utilisé utilise ces champs pour évaluer les caractéristiques de la charge de travail. Par conséquent, nous pouvons également nous appuyer sur eux. Mais il convient de rappeler qu'il s'agit de tuples et non d'octets, nous ne pouvons donc pas les prendre et en faire des octets.

Sur la base de ces données, nous pouvons construire ce que l'on appelle les tables TopN. Par exemple, Top-5, Top-10. Et vous pouvez garder une trace de ces tables chaudes qui sont plus utilisées que d’autres. Par exemple, 5 tables « chaudes » à insérer. Et selon ces tableaux TopN, nous évaluons notre charge de travail et pouvons évaluer les rafales de charge de travail après toute version, mise à jour et déploiement.

Il est également important d'évaluer la taille de la table, car parfois les développeurs déploient une nouvelle fonctionnalité et nos tables commencent à gonfler dans leur grande taille, car ils ont décidé d'ajouter une quantité supplémentaire de données, mais n'ont pas prédit comment cela se produirait. affecter la taille de la base de données. De tels cas nous surprennent également.

Et maintenant une petite question pour vous. Quelle est la question lorsque vous remarquez la charge sur le serveur de base de données ? Quelle est votre prochaine question ?

Mais la vraie question est la suivante. Quelles requêtes la charge provoque-t-elle ? Autrement dit, il n’est pas intéressant d’observer les processus provoqués par la charge. Il est clair que si l'hôte dispose d'une base de données, alors la base de données y est exécutée et il est clair que seules les bases de données y seront supprimées. Si nous ouvrons Top, nous y verrons une liste de processus PostgreSQL qui font quelque chose. De haut, ce qu'ils font ne sera pas clair.

Par conséquent, vous devez rechercher les requêtes qui génèrent le plus de charge, car le réglage des requêtes, en règle générale, donne plus de bénéfices que la configuration PostgreSQL ou le réglage du système d'exploitation, ou même le réglage du matériel. Selon mon estimation, cela représente environ 80-85-90 %. Et cela se fait beaucoup plus rapidement. Il est plus rapide de corriger la requête que de corriger la configuration, de planifier un redémarrage, notamment si la base de données ne peut pas être redémarrée, ou d'ajouter du matériel. Il est plus facile de réécrire la requête quelque part ou d'ajouter un index pour obtenir un meilleur résultat de cette requête.

Il est donc nécessaire de contrôler les demandes et leur adéquation. Prenons un autre exemple de surveillance. Et là aussi, il semble y avoir un excellent suivi. Il existe des informations sur la réplication, des informations sur le débit, le blocage et l'utilisation des ressources. Tout va bien, mais il n'y a aucune information sur les demandes. Il n'est pas clair quelles requêtes sont exécutées dans notre base de données, combien de temps elles s'exécutent, combien de ces requêtes. Nous devons toujours avoir cette information en surveillance.

Et pour obtenir ces informations, nous pouvons utiliser le module pg_stat_statements. Sur cette base, vous pouvez créer une variété de graphiques. Par exemple, vous pouvez obtenir des informations sur les requêtes les plus fréquentes, c'est-à-dire sur les requêtes exécutées le plus souvent. Oui, après les déploiements, il est également très utile de l'examiner et de comprendre s'il y a une augmentation des demandes.

Vous pouvez surveiller les requêtes les plus longues, c'est-à-dire les requêtes qui s'exécutent le plus longtemps. Ils fonctionnent sur le processeur, ils consomment des E/S. Nous pouvons également évaluer cela par les champs total_time, Mean_time, blk_write_time et blk_read_time.

Nous pouvons évaluer et surveiller les requêtes les plus lourdes en termes d'utilisation des ressources, celles qui lisent sur le disque, celles qui travaillent avec la mémoire ou, au contraire, créent une sorte de charge d'écriture.

Nous pouvons évaluer les demandes les plus généreuses. Ce sont les requêtes qui renvoient un grand nombre de lignes. Par exemple, il peut s'agir d'une sorte de demande pour laquelle ils ont oublié de fixer une limite. Et il renvoie simplement l'intégralité du contenu de la table ou de la requête sur les tables demandées.

Et vous pouvez également surveiller les requêtes qui utilisent des fichiers temporaires ou des tables temporaires.

Et nous avons toujours des processus en arrière-plan. Les processus en arrière-plan sont principalement des points de contrôle ou sont également appelés points de contrôle, il s'agit du vide automatique et de la réplication.

Un autre exemple de surveillance. Il y a un onglet Maintenance sur la gauche, allez-y et espérez voir quelque chose d'utile. Mais ici, seulement le temps du vide et de la collecte de statistiques, rien d'autre. Il s'agit d'informations très pauvres, vous devez donc toujours avoir des informations sur le fonctionnement des processus en arrière-plan dans notre base de données et s'il y a des problèmes liés à leur travail.

Lorsque nous examinons les points de contrôle, il ne faut pas oublier que nos points de contrôle vident les pages « sales » de la zone de mémoire partagée vers le disque, puis créent un point de contrôle. Et ce point de contrôle peut déjà être utilisé comme lieu lors de la récupération, si PostgreSQL était soudainement arrêté en cas d'urgence.

En conséquence, afin de vider toutes les pages « sales » sur le disque, vous devez effectuer une certaine quantité d'écriture. Et, en règle générale, sur les systèmes dotés d'une grande quantité de mémoire, cela représente beaucoup. Et si nous effectuons des points de contrôle très souvent dans un court intervalle, les performances du disque diminueront considérablement. Et les demandes des clients souffriront d’un manque de ressources. Ils seront en compétition pour les ressources et manqueront de productivité.

En conséquence, grâce à pg_stat_bgwriter sur les champs spécifiés, nous pouvons surveiller le nombre de points de contrôle qui se produisent. Et si nous avons beaucoup de points de contrôle pendant une certaine période (pendant 10-15-20 minutes, pendant une demi-heure), par exemple 3-4-5, alors cela peut déjà être un problème. Et vous devez déjà regarder dans la base de données, dans la configuration, ce qui provoque une telle abondance de points de contrôle. Peut-être qu’un gros disque arrive. Nous pouvons déjà évaluer la charge de travail, car nous avons déjà ajouté des graphiques de charge de travail. Nous pouvons déjà modifier les paramètres du point d'arrêt et nous assurer qu'ils n'affectent pas grandement les performances des requêtes.

Je reviens à l'autovacuum car c'est le genre de chose, comme je l'ai dit, qui peut facilement additionner les performances du disque et des requêtes, il est donc toujours important de mesurer la quantité d'autovacuum.

Le nombre de travailleurs de l'aspirateur automatique dans la base de données est limité. Par défaut, il y en a trois, donc si nous avons trois travailleurs qui travaillent tout le temps dans la base de données, cela signifie que notre autovacuum est sous-configuré, nous devons augmenter les limites, réviser les paramètres de l'autovacuum et déjà monter dans la configuration.
Il est important d’évaluer quels aspirateurs travaillent pour nous. Soit il a été lancé par l'utilisateur, soit le DBA est entré et a lancé une sorte de vide avec ses mains, ce qui a créé une charge. Nous avons un problème. Ou c'est le nombre d'aspirateurs qui dévissent le compteur de transactions. Pour certaines versions de PostgreSQL, ce sont des vides très lourds. Et ils peuvent facilement ajouter des performances car ils soustraient la totalité du tableau, analysant tous les blocs de ce tableau.

Et bien sûr, la durée des aspirateurs. Si nous avons des aspirateurs qui fonctionnent très longtemps, cela signifie que nous devons à nouveau prêter attention à la configuration de l'aspirateur et peut-être reconsidérer ses réglages. Parce qu'une situation peut survenir lorsque l'aspirateur fonctionne sur la table pendant une longue période (3-4 heures), mais pendant le travail de l'aspirateur, un grand nombre de rangées mortes ont à nouveau réussi à s'accumuler dans la table. Et dès que l'aspirateur est terminé, il doit à nouveau passer l'aspirateur sur cette table. Et nous arrivons à une situation : un vide infini. Et dans ce cas, le vide ne fait pas son travail et les tableaux commencent à grossir progressivement, bien que la quantité de données utiles qu'ils contiennent reste la même. Par conséquent, dans le vide prolongé, nous examinons toujours la configuration et essayons de l'optimiser, mais en même temps, afin que les performances des demandes des clients n'en souffrent pas.

Il n'existe désormais pratiquement aucune installation de PostgreSQL sans réplication en streaming. La réplication est le processus de transfert de données d'un maître vers une réplique.

La réplication dans PostgreSQL est organisée via un journal des transactions. Le maître génère un journal des transactions. Le journal des transactions sur la connexion réseau va vers la réplique, puis il est reproduit sur la réplique. Tout est simple.

En conséquence, la vue pg_stat_replication est utilisée pour surveiller le décalage de réplication. Mais ce n'est pas facile pour elle. En version 10, la vue a subi plusieurs modifications. Premièrement, certains champs ont été renommés. Et certains champs ont été ajoutés. Dans la 10ème version, des champs sont apparus qui permettent d'évaluer le délai de réplication en secondes. C'est très confortable. Avant la version 10, il était possible d'estimer le délai de réplication en octets. Cette fonctionnalité est restée dans la 10ème version, c'est-à-dire que vous pouvez choisir ce qui vous convient le mieux - évaluer le décalage en octets ou évaluer le décalage en secondes. Beaucoup font les deux.

Cependant, afin d'évaluer le délai de réplication, vous devez connaître la position du journal dans la transaction. Et ces positions du journal des transactions se trouvent uniquement dans la vue pg_stat_replication. Relativement parlant, nous pouvons utiliser la fonction pg_xlog_location_diff() pour prendre deux points dans le journal des transactions. Calculez le delta entre eux et obtenez le décalage de réplication en octets. C'est très pratique et simple.

Dans la version 10, cette fonction a été renommée pg_wal_lsn_diff(). En général, dans toutes les fonctions, vues, utilitaires où le mot "xlog" était rencontré, il était remplacé par la valeur "wal". C'est à la fois dans les vues et dans les fonctions. C’est une telle innovation.

De plus, dans la 10ème version, des lignes ont été ajoutées pour afficher spécifiquement le décalage. Il s'agit du décalage d'écriture, du décalage de vidage et du décalage de relecture. Autrement dit, il est important de surveiller ces choses. Si nous constatons un retard de réplication, nous devons alors rechercher pourquoi il est apparu, d'où il vient et résoudre le problème.

Avec les métriques du système, presque tout est en ordre. À la naissance d’une surveillance, elle commence par les métriques du système. Il s'agit de l'utilisation des processeurs, de la mémoire, du swap, du réseau et du disque. Mais néanmoins, de nombreux paramètres ne sont pas présents par défaut.

Si tout est en ordre avec l'élimination du processus, des problèmes surviennent avec l'élimination du disque. En règle générale, les développeurs de surveillance ajoutent des informations sur la bande passante. Cela peut être en iops ou en octets. Mais ils oublient la latence et l’utilisation des périphériques disques. Ce sont des paramètres plus importants qui nous permettent d'évaluer le niveau de charge de nos disques et leur ralentissement. Si nous avons une latence élevée, cela signifie qu'il y a des problèmes avec les disques. Si nous avons une utilisation élevée, cela signifie que les disques ne peuvent pas faire face. Ce sont des caractéristiques plus qualitatives que la bande passante.

Cependant, ces statistiques peuvent également être obtenues à partir du système de fichiers /proc, comme c'est le cas pour le recyclage des processeurs. Pourquoi cette information n'est pas ajoutée à la surveillance, je ne le sais pas. Mais il est quand même important de l’avoir dans votre suivi.

Il en va de même pour les interfaces réseau. Il existe des informations sur la bande passante du réseau en paquets, en octets, mais néanmoins il n'y a aucune information sur la latence ni aucune information sur l'utilisation, bien qu'il s'agisse également d'informations utiles.

Toute surveillance a ses inconvénients. Et quel que soit le type de surveillance que vous effectuez, elle ne répondra toujours pas à certains critères. Mais néanmoins, ils se développent, de nouvelles fonctionnalités sont ajoutées, de nouvelles choses, alors choisissez quelque chose et terminez-le.

Et pour terminer, vous devez toujours avoir une idée de ce que signifient les statistiques données et de la manière dont vous pouvez résoudre les problèmes avec celles-ci.

Et quelques points clés :

• Il faut toujours surveiller la disponibilité, disposer de tableaux de bord pour pouvoir évaluer rapidement que tout est en ordre avec la base.
• Vous devez toujours avoir une idée des clients qui travaillent avec votre base de données afin d'éliminer les mauvais clients et de les éliminer.
• Il est important d'évaluer la manière dont ces clients utilisent les données. Vous devez avoir une idée de votre charge de travail.
• Il est important d'évaluer comment cette charge de travail est constituée, à l'aide de quelles requêtes. Vous pouvez évaluer les requêtes, les optimiser, les refactoriser, créer des index pour elles. Il est très important.
• Les processus en arrière-plan peuvent avoir un impact négatif sur les demandes des clients. Il est donc important de s'assurer qu'ils n'utilisent pas trop de ressources.
• Les métriques du système vous permettent d'élaborer des plans de mise à l'échelle et d'augmentation de la capacité de vos serveurs. Il est donc important de les suivre et de les évaluer également.

Si ce sujet vous intéresse, vous pouvez suivre ces liens.
http://bit.do/stats_collector est la documentation officielle du collecteur de statistiques. Il y a une description de toutes les vues statistiques et une description de tous les champs. Vous pouvez les lire, les comprendre et les analyser. Et sur cette base, construisez vos propres graphiques, enrichissez votre suivi.

Exemples de demandes :
http://bit.do/dataegret_sql
http://bit.do/lesovsky_sql

Il s'agit de notre référentiel d'entreprise et du mien. Ils ont des demandes d’échantillons. Il n'y a aucune requête de la série select* from, quelque chose là-bas. Il existe déjà des requêtes toutes faites avec des jointures, utilisant des fonctions intéressantes qui vous permettent de créer des valeurs lisibles et pratiques à partir de nombres bruts, c'est-à-dire des octets, du temps. Vous pouvez les sélectionner, les regarder, les analyser, les ajouter à vos suivis, construire vos propres suivis à partir d'eux.

des questions

Question : Vous avez dit que vous ne feriez pas de publicité pour les marques, mais je me demande toujours : quel type de tableaux de bord utilisez-vous dans vos projets ?
Réponse : De différentes manières. Il arrive que nous venions chez le client et qu'il dispose déjà de son propre suivi. Et nous conseillons le client sur ce qui doit être ajouté à son suivi. La pire situation est avec Zabbix. Parce qu'il n'a pas la capacité de créer des graphiques TopN. Nous utilisons nous-mêmes Okmètreparce que nous avons consulté ces gars-là sur la surveillance. Ils ont effectué une surveillance PostgreSQL basée sur nos TDR. J'écris mon propre projet animalier, qui collecte des données via Prometheus et les attire grafana. Ma tâche est de créer mon propre exportateur dans Prometheus, puis de tout dessiner dans Grafana.

Question : Existe-t-il des analogues des rapports AWR ou des... agrégations ? Êtes-vous au courant de quelque chose comme ça ?
Réponse : Oui, je sais ce qu'est AWR, c'est une bonne chose. À l'heure actuelle, il existe une variété de vélos qui mettent en œuvre approximativement le modèle suivant. À un certain intervalle de temps, certaines lignes de base sont écrites dans le même PostgreSQL ou dans un stockage séparé. Vous pouvez les rechercher sur Internet, ils le sont. L'un des développeurs d'une telle chose siège sur le forum sql.ru dans le fil PostgreSQL. Vous pouvez l'attraper là-bas. Oui, de telles choses existent, elles peuvent être utilisées. plus dans son pgCentre J'écris aussi quelque chose qui vous permet de faire la même chose.

PS1 Si vous utilisez postgres_exporter, quel tableau de bord utilisez-vous ? Il y en a plusieurs. Ils sont déjà dépassés. La communauté peut-elle créer un modèle mis à jour ?

PS2 Supprimé pganalyze car il s'agit d'une offre SaaS propriétaire qui se concentre sur la surveillance des performances et les suggestions de réglage automatisées.

Seuls les utilisateurs enregistrés peuvent participer à l'enquête. se connecters'il te plait.

Selon vous, quelle surveillance postgresql auto-hébergée (avec tableau de bord) est la meilleure ?

• 30,0%Zabbix + ajouts d'Alexey Lesovsky ou zabbix 4.4 ou libzbxpgsql + zabbix libzbxpgsql + zabbix3

• 0,0%https://github.com/lesovsky/pgcenter0

• 0,0%https://github.com/pg-monz/pg_monz0

• 20,0%https://github.com/cybertec-postgresql/pgwatch22

• 20,0%https://github.com/postgrespro/mamonsu2

• 0,0%https://www.percona.com/doc/percona-monitoring-and-management/conf-postgres.html0

• 10,0%pganalyze est un SaaS propriétaire - impossible de supprimer1

• 10,0%https://github.com/powa-team/powa1

• 0,0%https://github.com/darold/pgbadger0

• 0,0%https://github.com/darold/pgcluu0

• 0,0%https://github.com/zalando/PGObserver0

• 10,0%https://github.com/spotify/postgresql-metrics1

10 utilisateurs ont voté. 26 utilisateurs se sont abstenus.

Source: habr.com