🥇Plongez dans les statistiques internes de PostgreSQL. Alexeï Lesovsky

Transcription du rapport 2015 d'Alexey Lesovsky "Deep dive into PostgreSQL internal statistics"

Avis de non-responsabilité de l'auteur du rapport : Je note que ce rapport est daté de novembre 2015 - plus de 4 ans se sont écoulés et beaucoup de temps s'est écoulé. La version 9.4 discutée dans le rapport n'est plus prise en charge. Au cours des 4 dernières années, 5 nouvelles versions ont été publiées dans lesquelles de nombreuses innovations, améliorations et modifications concernant les statistiques sont apparues, et une partie du matériel est obsolète et non pertinente. Au fur et à mesure de mes révisions, j'ai essayé de marquer ces endroits afin de ne pas induire le lecteur en erreur. Je n'ai pas réécrit ces lieux, il y en a beaucoup, et en conséquence, un rapport complètement différent se révélera.

Le SGBD PostgreSQL est un mécanisme énorme, et ce mécanisme se compose de nombreux sous-systèmes, dont le travail coordonné affecte directement les performances du SGBD. Pendant le fonctionnement, des statistiques et des informations sur le fonctionnement des composants sont collectées, ce qui vous permet d'évaluer l'efficacité de PostgreSQL et de prendre des mesures pour améliorer les performances. Cependant, ces informations sont nombreuses et présentées sous une forme plutôt simplifiée. Traiter ces informations et les interpréter est parfois une tâche tout à fait non triviale, et le "zoo" d'outils et d'utilitaires peut facilement confondre même un DBA avancé.

Bon après-midi Je m'appelle Aleksey. Comme Ilya l'a dit, je vais parler des statistiques PostgreSQL.

Statistiques d'activité PostgreSQL. PostgreSQL a deux statistiques. Statistiques d'activité, qui seront discutées. Et les statistiques du planificateur sur la distribution des données. Je parlerai spécifiquement des statistiques d'activité de PostgreSQL, qui nous permettent de juger des performances et de les améliorer d'une manière ou d'une autre.

Je vais vous dire comment utiliser efficacement les statistiques pour résoudre une variété de problèmes que vous avez ou pourriez avoir.

Qu'est-ce qui ne figurera pas dans le rapport ? Dans le rapport, je n'aborderai pas les statistiques du planificateur, car. il s'agit d'une rubrique distincte pour un rapport distinct sur la manière dont les données sont stockées dans la base de données et sur la manière dont le planificateur de requêtes se fait une idée des caractéristiques qualitatives et quantitatives de ces données.

Et il n'y aura pas de critiques d'outils, je ne comparerai pas un produit avec un autre. Il n'y aura pas de publicité. Laissons tomber.

Je veux vous montrer que l'utilisation des statistiques est utile. Il est nécessaire. Utilisez-le sans crainte. Tout ce dont nous avons besoin est du SQL simple et une connaissance de base de SQL.

Et nous parlerons des statistiques à choisir pour résoudre les problèmes.

Si nous regardons PostgreSQL et exécutons une commande sur le système d'exploitation pour afficher les processus, nous verrons une "boîte noire". Nous verrons certains processus qui font quelque chose, et par leur nom, nous pouvons imaginer à peu près ce qu'ils font là, ce qu'ils font. Mais, en fait, c'est une boîte noire, on ne peut pas regarder à l'intérieur.

Nous pouvons regarder la charge du processeur dans top, nous pouvons voir l'utilisation de la mémoire par certains utilitaires système, mais nous ne pourrons pas regarder à l'intérieur de PostgreSQL. Pour cela, nous avons besoin d'autres outils.

Et en continuant plus loin, je vais vous dire où le temps est passé. Si nous représentons PostgreSQL sous la forme d'un tel schéma, il sera alors possible de répondre où le temps est passé. Il s'agit de deux choses : il s'agit du traitement des requêtes client des applications et des tâches d'arrière-plan que PostgreSQL exécute pour le faire fonctionner.

Si nous commençons à regarder dans le coin supérieur gauche, nous pouvons voir comment les demandes des clients sont traitées. La demande provient de l'application et une session client est ouverte pour un travail ultérieur. La demande est transmise au planificateur. Le planificateur construit un plan de requête. L'envoie plus loin pour exécution. Il existe une sorte de données d'E / S de bloc associées aux tables et aux index. Les données nécessaires sont lues à partir des disques dans la mémoire dans une zone spéciale appelée "tampons partagés". Les résultats de la requête, s'il s'agit de mises à jour, de suppressions, sont enregistrés dans le journal des transactions dans WAL. Certaines informations statistiques vont dans le journal ou le collecteur de statistiques. Et le résultat de la requête est rendu au client. Après cela, le client peut tout répéter avec une nouvelle requête.

Qu'avons-nous avec les tâches d'arrière-plan et les processus d'arrière-plan ? Nous avons plusieurs processus qui maintiennent la base de données opérationnelle dans un mode de fonctionnement normal. Ces processus seront également couverts dans le rapport : il s'agit de l'autovacuum, du pointeur de contrôle, des processus liés à la réplication, de l'écriture en arrière-plan. J'aborderai chacun d'eux dans mon rapport.

Quels sont les problèmes avec les statistiques ?

Beaucoup d'informations. PostgreSQL 9.4 fournit 109 métriques pour afficher les données statistiques. Cependant, si la base de données stocke de nombreuses tables, schémas, bases de données, alors toutes ces métriques devront être multipliées par le nombre correspondant de tables, de bases de données. Autrement dit, il y a encore plus d'informations. Et il est très facile de s'y noyer.
Le problème suivant est que les statistiques sont représentées par des compteurs. Si nous regardons ces statistiques, nous verrons des compteurs en augmentation constante. Et si beaucoup de temps s'est écoulé depuis la réinitialisation des statistiques, nous verrons des milliards de valeurs. Et ils ne nous disent rien.
Il n'y a pas d'historique. Si vous avez une sorte de panne, quelque chose est tombé il y a 15 à 30 minutes, vous ne pourrez pas utiliser les statistiques et voir ce qui s'est passé il y a 15 à 30 minutes. C'est un problème.
L'absence d'outil intégré à PostgreSQL est un problème. Les développeurs du noyau ne fournissent aucun utilitaire. Ils n'ont rien de tel. Ils donnent juste des statistiques dans la base de données. Utilisez-le, faites-lui une demande, ce que vous voulez, puis faites-le.
Puisqu'il n'y a pas d'outil intégré à PostgreSQL, cela pose un autre problème. Beaucoup d'outils tiers. Chaque entreprise qui a des mains plus ou moins directes essaie d'écrire son propre programme. Et par conséquent, la communauté dispose de nombreux outils que vous pouvez utiliser pour travailler avec les statistiques. Et dans certains outils, il y a certaines fonctionnalités, dans d'autres outils, il n'y a pas d'autres fonctionnalités, ou il y a de nouvelles fonctionnalités. Et une situation se présente où vous devez utiliser deux, trois ou quatre outils qui se chevauchent et ont des fonctions différentes. C'est très ennuyeux.

Qu'en découle-t-il ? Il est important de pouvoir prendre des statistiques directement pour ne pas dépendre des programmes, ou d'améliorer d'une manière ou d'une autre ces programmes vous-même : ajoutez quelques fonctions pour en tirer profit.

Et vous avez besoin de connaissances de base en SQL. Pour obtenir des données à partir de statistiques, vous devez effectuer des requêtes SQL, c'est-à-dire que vous devez savoir comment les sélections et les jointures sont effectuées.

Les statistiques nous disent plusieurs choses. Ils peuvent être divisés en catégories.

La première catégorie correspond aux événements qui se déroulent dans la base de données. C'est à ce moment qu'un événement se produit dans la base de données : une requête, un accès à une table, un autovacuum, des commits, alors ce sont tous des événements. Les compteurs correspondant à ces événements sont incrémentés. Et nous pouvons suivre ces événements.
La deuxième catégorie concerne les propriétés des objets tels que les tables, les bases de données. Ils ont des propriétés. C'est la taille des tables. Nous pouvons suivre la croissance des tables, la croissance des index. Nous pouvons voir des changements dans la dynamique.
Et la troisième catégorie est le temps passé sur l'événement. La demande est un événement. Il a sa propre mesure spécifique de la durée. Commencé ici, terminé ici. Nous pouvons le suivre. Soit le temps de lire un bloc sur le disque ou d'écrire. Ces éléments sont également suivis.

Les sources des statistiques sont présentées comme suit :

Dans la mémoire partagée (tampons partagés), il y a un segment pour y placer des données statiques, il y a aussi ces compteurs qui sont constamment incrémentés lorsque certains événements se produisent, ou certains moments surviennent dans le fonctionnement de la base de données.
Tous ces compteurs ne sont pas disponibles pour l'utilisateur et ne sont même pas disponibles pour l'administrateur. Ce sont des choses de bas niveau. Pour y accéder, PostgreSQL fournit une interface sous forme de fonctions SQL. Nous pouvons effectuer des sélections sélectionnées à l'aide de ces fonctions et obtenir une sorte de métrique (ou un ensemble de métriques).
Cependant, il n'est pas toujours pratique d'utiliser ces fonctions, c'est pourquoi les fonctions sont à la base des vues (VIEWs). Ce sont des tables virtuelles qui fournissent des statistiques sur un sous-système spécifique ou sur un ensemble d'événements dans la base de données.
Ces vues intégrées (VIEWs) constituent la principale interface utilisateur pour travailler avec les statistiques. Ils sont disponibles par défaut sans aucun paramètre supplémentaire, vous pouvez immédiatement les utiliser, regarder, prendre des informations à partir de là. Et il y a aussi des contributions. Les contributions sont officielles. Vous pouvez installer le package postgresql-contrib (par exemple, postgresql94-contrib), charger le module nécessaire dans la configuration, spécifier ses paramètres, redémarrer PostgreSQL et vous pouvez l'utiliser. (Note. Selon la distribution, dans les versions récentes de contrib le paquet fait partie du paquet principal).
Et il y a des contrib non officielles. Ils ne sont pas fournis avec la distribution PostgreSQL standard. Ils doivent être compilés ou installés en tant que bibliothèque. Les options peuvent être très différentes, selon ce que le développeur de cette contribution non officielle a proposé.

Cette diapositive montre toutes ces vues (VIEWs) et certaines de ces fonctions qui sont disponibles dans PostgreSQL 9.4. Comme on peut le voir, il y en a beaucoup. Et il est assez facile de s'embrouiller si vous en faites l'expérience pour la première fois.

Cependant, si nous prenons la photo précédente Как тратится время на PostgreSQL et compatible avec cette liste, nous obtenons cette image. Chaque vue (VIEWs), ou chaque fonction, nous pouvons utiliser dans un but ou un autre pour obtenir les statistiques appropriées lorsque PostgreSQL est en cours d'exécution. Et nous pouvons déjà obtenir des informations sur le fonctionnement du sous-système.

La première chose que nous allons regarder est pg_stat_database. Comme on peut le voir, il s'agit d'une représentation. Il contient beaucoup d'informations. Les informations les plus variées. Et cela donne une connaissance très utile de ce qui se passe dans la base de données.

Que pouvons-nous en tirer ? Commençons par les choses les plus simples.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

La première chose que nous pouvons regarder est le pourcentage d'accès au cache. Le pourcentage d'accès au cache est une mesure utile. Il vous permet d'estimer la quantité de données extraites du cache des tampons partagés et la quantité lue sur le disque.

Il est clair que plus nous avons d'accès au cache, mieux c'est. Nous évaluons cette métrique en pourcentage. Et, par exemple, si nous avons un pourcentage de ces accès au cache supérieur à 90 %, alors c'est bon. S'il descend en dessous de 90 %, nous n'avons pas assez de mémoire pour conserver la tête brûlante des données en mémoire. Et pour utiliser ces données, PostgreSQL est obligé d'accéder au disque et c'est plus lent que si les données étaient lues depuis la mémoire. Et vous devez penser à augmenter la mémoire : soit augmenter les tampons partagés, soit augmenter la mémoire fer (RAM).

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

Que retenir d'autre de cette présentation ? Vous pouvez voir les anomalies qui se produisent dans la base de données. Qu'est-ce qui est montré ici ? Il y a les commits, les rollbacks, la création de fichiers temporaires, leur taille, les blocages et les conflits.

Nous pouvons utiliser cette requête. Ce SQL est assez simple. Et nous pouvons voir ces données par nous-mêmes.

Et voici les valeurs seuils. Nous examinons le rapport entre les commits et les rollbacks. Commits est une confirmation réussie de la transaction. Les retours en arrière sont des retours en arrière, c'est-à-dire que la transaction a effectué du travail, sollicité la base de données, considéré quelque chose, puis un échec s'est produit et les résultats de la transaction sont ignorés. c'est à dire. le nombre de rollbacks en constante augmentation est mauvais. Et vous devriez en quelque sorte les éviter et modifier le code pour que cela ne se produise pas.

Les conflits sont liés à la réplication. Et ils doivent être évités aussi. Si certaines requêtes sont exécutées sur le réplica et que des conflits surviennent, vous devez analyser ces conflits et voir ce qui se passe. Les détails peuvent être trouvés dans les journaux. Et résolvez les conflits afin que les demandes d'application fonctionnent sans erreur.

Les impasses sont également une mauvaise situation. Lorsque les demandes sont en concurrence pour les ressources, une demande a accédé à une ressource et a pris le verrou, la deuxième demande a accédé à la deuxième ressource et a également pris le verrou, puis les deux demandes ont accédé aux ressources de l'autre et se sont bloquées en attendant que le voisin libère le verrou. C'est aussi une situation problématique. Ils doivent être traités au niveau de la réécriture des applications et de la sérialisation de l'accès aux ressources. Et si vous voyez que vos blocages augmentent constamment, vous devez regarder les détails dans les journaux, analyser les situations qui se sont produites et voir quel est le problème.

Les fichiers temporaires (temp_files) sont également mauvais. Lorsqu'une demande d'utilisateur ne dispose pas de suffisamment de mémoire pour accueillir les données opérationnelles temporaires, elle crée un fichier sur le disque. Et toutes les opérations qu'il pourrait effectuer dans un tampon temporaire en mémoire commencent déjà à s'effectuer sur le disque. C'est lent. Cela augmente le temps d'exécution de la requête. Et le client qui a envoyé une requête à PostgreSQL recevra une réponse un peu plus tard. Si toutes ces opérations sont effectuées en mémoire, Postgres répondra beaucoup plus rapidement et le client attendra moins.

pg_stat_bgwriter - Cette vue décrit le fonctionnement de deux sous-systèmes d'arrière-plan PostgreSQL : checkpointer и background writer.

Pour commencer, analysons les points de contrôle, les soi-disant. checkpoints. Que sont les points de contrôle ? Un point de contrôle est une position dans le journal des transactions indiquant que toutes les modifications de données validées dans le journal sont correctement synchronisées avec les données sur le disque. Le processus, selon la charge de travail et les paramètres, peut être long et consiste principalement à synchroniser les pages modifiées dans des tampons partagés avec des fichiers de données sur disque. Pourquoi est-ce? Si PostgreSQL accédait au disque tout le temps et y prenait des données, et écrivait des données à chaque accès, ce serait lent. Par conséquent, PostgreSQL possède un segment de mémoire dont la taille dépend des paramètres de la configuration. Postgres alloue des données opérationnelles dans cette mémoire pour un traitement ultérieur ou une interrogation. Dans le cas de demandes de modification de données, celles-ci sont modifiées. Et nous obtenons deux versions des données. L'un est en mémoire, l'autre sur disque. Et périodiquement, vous devez synchroniser ces données. Nous avons besoin que ce qui est modifié en mémoire soit synchronisé sur le disque. Cela nécessite un point de contrôle.

Le point de contrôle passe par des tampons partagés, marque les pages sales qu'elles sont nécessaires pour le point de contrôle. Ensuite, il commence le deuxième passage à travers les tampons partagés. Et les pages qui sont marquées pour le point de contrôle, il les synchronise déjà. Ainsi, les données sont déjà synchronisées avec le disque.

Il existe deux types de points de contrôle. Un point de contrôle est exécuté à l'expiration du délai. Ce point de contrôle est utile et bon - checkpoint_timed. Et il y a des points de contrôle à la demande - checkpoint required. Un tel point de contrôle se produit lorsque nous avons un très grand enregistrement de données. Nous avons enregistré de nombreux journaux de transactions. Et PostgreSQL estime qu'il doit synchroniser tout cela le plus rapidement possible, faire un point de contrôle et passer à autre chose.

Et si vous regardiez les statistiques pg_stat_bgwriter et regarde ce que tu as checkpoint_req est beaucoup plus grand que checkpoint_timed, alors c'est mauvais. Pourquoi mauvais ? Cela signifie que PostgreSQL est constamment sollicité lorsqu'il doit écrire des données sur le disque. Le point de contrôle par timeout est moins stressant et est exécuté selon le calendrier interne et, pour ainsi dire, étiré dans le temps. PostgreSQL a la capacité de faire une pause dans le travail et de ne pas forcer le sous-système de disque. Ceci est utile pour PostgreSQL. Et les requêtes exécutées pendant le point de contrôle ne subiront pas de stress du fait que le sous-système de disque est occupé.

Et il y a trois paramètres pour ajuster le point de contrôle :

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Ils vous permettent de contrôler le fonctionnement des points de contrôle. Mais je ne m'attarderai pas sur eux. Leur influence est une question distincte.

Attention: La version 9.4 considérée dans le rapport n'est plus pertinente. Dans les versions modernes de PostgreSQL, le paramètre checkpoint_segments remplacés par des paramètres min_wal_size и max_wal_size.

Le sous-système suivant est l'écrivain d'arrière-plan - background writer. Que fait-il? Il tourne constamment dans une boucle sans fin. Il scanne les pages dans des tampons partagés et vide les pages sales qu'il trouve sur le disque. De cette façon, cela aide le pointeur à faire moins de travail pendant le pointage.

A quoi d'autre a-t-il besoin ? Il prévoit le besoin de pages propres dans les tampons partagés si elles sont soudainement nécessaires (en grande quantité et immédiatement) pour accueillir des données. Supposons qu'une situation se produise lorsque la demande nécessite des pages propres et qu'elles se trouvent déjà dans des tampons partagés. postgres backend il les prend juste et les utilise, il n'a rien à nettoyer lui-même. Mais si soudainement il n'y a pas de telles pages, le backend s'arrête et commence à rechercher des pages pour les vider sur le disque et les prendre pour ses propres besoins - ce qui affecte négativement le temps de la requête en cours d'exécution. Si vous voyez que vous avez un paramètre maxwritten_clean grand, cela signifie que le rédacteur en arrière-plan ne fait pas son travail et que vous devez augmenter les paramètres bgwriter_lru_maxpagespour qu'il puisse faire plus de travail en un cycle, effacer plus de pages.

Et un autre indicateur très utile est buffers_backend_fsync. Les backends ne font pas fsync car c'est lent. Ils transmettent fsync au pointeur de contrôle de la pile d'E/S. Le pointeur de contrôle a sa propre file d'attente, il traite périodiquement fsync et synchronise les pages en mémoire avec les fichiers sur le disque. Si la file d'attente du point de contrôle est grande et pleine, le backend est obligé de faire fsync lui-même et cela ralentit le backend, c'est-à-dire que le client recevra une réponse plus tard qu'il ne le pourrait. Si vous voyez que vous avez cette valeur supérieure à zéro, c'est déjà un problème et vous devez faire attention aux paramètres de l'écrivain d'arrière-plan et également évaluer les performances du sous-système de disque.

Attention: _Le texte suivant décrit les vues statistiques associées à la réplication. La plupart des noms de vue et de fonction ont été renommés dans Postgres 10. L'essence des changements de nom était de remplacer xlog sur wal и location sur lsn dans les noms de fonctions/vues, etc. Exemple particulier, fonction pg_xlog_location_diff() a été renommé en pg_wal_lsn_diff()._

Nous avons beaucoup ici aussi. Mais nous n'avons besoin que d'éléments liés à l'emplacement.

Si nous voyons que toutes les valeurs sont égales, alors c'est idéal et la réplique n'est pas en retard sur le maître.

Cette position hexadécimale est ici la position dans le journal des transactions. Il augmente constamment s'il y a une activité dans la base de données : insertions, suppressions, etc.

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

Si ces choses sont différentes, alors il y a une sorte de décalage. Lag est le décalage de la réplique par rapport au maître, c'est-à-dire que les données diffèrent entre les serveurs.

Il y a trois raisons au retard :

C'est le sous-système de disque qui ne peut pas gérer les écritures de synchronisation de fichiers.
Il s'agit d'erreurs réseau possibles, ou de surcharge réseau, lorsque les données n'ont pas le temps d'atteindre la réplique et qu'elle ne peut pas la reproduire.
Et le processeur. Le processeur est un cas très rare. Et j'ai vu ça deux ou trois fois, mais ça peut arriver aussi.

Et voici trois requêtes qui nous permettent d'utiliser des statistiques. Nous pouvons estimer combien est enregistré dans notre journal des transactions. Il existe une telle fonction pg_xlog_location_diff et nous pouvons estimer le délai de réplication en octets et en secondes. Nous utilisons également la valeur de cette vue (VIEWs) pour cela.

Note: _Au lieu de pg_xlog_locationdiff(), vous pouvez utiliser l'opérateur de soustraction et soustraire un emplacement d'un autre. Confortable.

Avec un décalage, qui est en secondes, il y a un moment. S'il n'y a pas d'activité sur le maître, la transaction était là il y a environ 15 minutes et il n'y a pas d'activité, et si nous regardons ce décalage sur la réplique, nous verrons un décalage de 15 minutes. Cela vaut la peine d'être rappelé. Et cela peut conduire à une stupeur lorsque vous avez observé ce décalage.

pg_stat_all_tables est une autre vue utile. Il affiche des statistiques sur les tables. Lorsque nous avons des tables dans la base de données, il y a une activité avec elle, certaines actions, nous pouvons obtenir ces informations à partir de cette vue.

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

La première chose que nous pouvons examiner est les analyses de table séquentielles. Le nombre lui-même après ces passages n'est pas nécessairement mauvais et n'indique pas que nous devons déjà faire quelque chose.

Cependant, il existe une deuxième métrique - seq_tup_read. Il s'agit du nombre de lignes renvoyées par l'analyse séquentielle. Si le nombre moyen dépasse 1 000, 10 000, 50 000, 100 000, cela indique déjà que vous devrez peut-être créer un index quelque part pour que les accès se fassent par index, ou qu'il est possible d'optimiser les requêtes qui utilisent de tels parcours séquentiels pour que cela n'arrive pas.

Un exemple simple - disons qu'une requête avec un grand OFFSET et LIMIT en vaut la peine. Par exemple, 100 000 lignes d'une table sont analysées, puis 50 000 lignes requises sont prises, et les lignes analysées précédemment sont supprimées. C'est aussi un mauvais cas. Et ces demandes doivent être optimisées. Et voici une requête SQL si simple sur laquelle vous pouvez la voir et évaluer les nombres reçus.

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

Les tailles de tableau peuvent également être obtenues à l'aide de ce tableau et à l'aide de fonctions supplémentaires pg_total_relation_size(), pg_relation_size().

En général, il existe des métacommandes dt и di, que vous pouvez utiliser dans PSQL et voir également les tailles de table et d'index.

Cependant, l'utilisation de fonctions nous aide à regarder les tailles des tables, même en tenant compte des index, ou sans prendre en compte les index, et déjà à faire quelques estimations basées sur la croissance de la base de données, c'est-à-dire comment elle grandit avec nous, avec quelle intensité, et tire déjà quelques conclusions sur l'optimisation du dimensionnement.

Activité d'écriture. Qu'est-ce qu'un enregistrement ? Voyons le fonctionnement UPDATE – l'opération de mise à jour des lignes de la table. En fait, la mise à jour consiste en deux opérations (voire plus). Il s'agit d'insérer une nouvelle version de ligne et de marquer l'ancienne version de ligne comme obsolète. Plus tard, l'autovacuum viendra nettoyer ces versions obsolètes des lignes, marquer cet endroit comme disponible pour réutilisation.

De plus, la mise à jour ne consiste pas seulement à mettre à jour une table. C'est toujours une mise à jour de l'index. Si vous avez beaucoup d'index sur la table, alors avec update, tous les index auxquels participent les champs mis à jour dans la requête devront également être mis à jour. Ces index auront également des versions de ligne obsolètes qui devront être nettoyées.

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

Et en raison de sa conception, UPDATE est une opération lourde. Mais ils peuvent être simplifiés. Manger hot updates. Ils sont apparus dans PostgreSQL version 8.3. Et qu'est ce que c'est? Il s'agit d'une mise à jour légère qui n'entraîne pas la reconstruction des index. Autrement dit, nous avons mis à jour l'enregistrement, mais seul l'enregistrement de la page (qui appartient à la table) a été mis à jour, et les index pointent toujours vers le même enregistrement de la page. Il y a un peu une logique de travail si intéressante, quand un vide vient, alors il a ces chaînes hot reconstruit et tout continue de fonctionner sans mettre à jour les index, et tout se passe avec moins de gaspillage de ressources.

Et quand tu as n_tup_hot_upd grand, c'est très bon. Cela signifie que les mises à jour légères prévalent et que cela nous coûte moins cher en termes de ressources et que tout va bien.

ALTER TABLE table_name SET (fillfactor = 70);

Comment augmenter le volume hot updatevo? On peut utiliser fillfactor. Il détermine la taille de l'espace libre réservé lors du remplissage d'une page dans une table à l'aide d'INSERT. Lorsque les insertions vont au tableau, elles remplissent complètement la page, ne laissent pas d'espace vide dedans. Une nouvelle page est alors mise en surbrillance. Les données sont à nouveau remplies. Et c'est le comportement par défaut, fillfactor = 100%.

Nous pouvons régler le facteur de remplissage à 70 %. C'est-à-dire qu'avec les insertions, une nouvelle page a été allouée, mais seulement 70% de la page a été remplie. Et il nous reste 30% en réserve. Lorsque vous devez effectuer une mise à jour, cela se produira très probablement sur la même page et la nouvelle version de la ligne tiendra sur la même page. Et hot_update sera fait. Cela facilite l'écriture sur les tableaux.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

File d'attente de vide automatique. Autovacuum est un tel sous-système pour lequel il existe très peu de statistiques dans PostgreSQL. Nous ne pouvons voir dans les tableaux de pg_stat_activity que le nombre de vides que nous avons en ce moment. Cependant, il est très difficile de comprendre combien de tables dans la file d'attente il a en mouvement.

Note: _Depuis Postgres 10, la situation avec le suivi du vide vide s'est beaucoup améliorée - la vue pg_stat_progress est apparuevide, ce qui simplifie grandement la question de la surveillance de l'autovide.

Nous pouvons utiliser cette requête simplifiée. Et nous pouvons voir quand le vide doit être fait. Mais, comment et quand le vide doit-il commencer ? Ce sont les anciennes versions des chaînes dont j'ai parlé plus tôt. La mise à jour a eu lieu, la nouvelle version de la ligne a été insérée. Une version obsolète de la chaîne est apparue. Tableau pg_stat_user_tables il existe un tel paramètre n_dead_tup. Il indique le nombre de lignes "mortes". Et dès que le nombre de lignes mortes est devenu supérieur à un certain seuil, un autovacuum viendra à la table.

Et comment ce seuil est-il calculé ? Il s'agit d'un pourcentage très précis du nombre total de lignes du tableau. Il y a un paramètre autovacuum_vacuum_scale_factor. Il définit le pourcentage. Disons 10% + il y a un seuil de base supplémentaire de 50 lignes. Et que se passe-t-il ? Lorsque nous avons plus de lignes mortes que "10 % + 50" de toutes les lignes du tableau, nous mettons le tableau en autovacuum.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Cependant, il y a un point. Seuils de base pour les paramètres av_base_thresh и av_scale_factor peuvent être attribués individuellement. Et, en conséquence, le seuil ne sera pas global, mais individuel pour la table. Par conséquent, pour calculer, vous devez utiliser des trucs et astuces. Et si vous êtes intéressé, vous pouvez consulter l'expérience de nos collègues d'Avito (le lien sur la diapositive n'est pas valide et a été mis à jour dans le texte).

Ils ont écrit pour plugin muninqui tient compte de ces éléments. Il y a une toile de pied sur deux feuilles. Mais il considère correctement et assez efficacement nous permet d'évaluer où nous avons besoin de beaucoup de vide pour les tables, où il y en a peu.

Que pouvons-nous y faire? Si nous avons une longue file d'attente et que l'autovacuum ne peut pas faire face, nous pouvons augmenter le nombre de travailleurs sous vide, ou simplement rendre le vide plus agressifafin qu'il se déclenche plus tôt, traite la table en petits morceaux. Et ainsi la file d'attente diminuera. - L'essentiel ici est de surveiller la charge sur les disques, car. Le vide n'est pas gratuit, bien qu'avec l'avènement des appareils SSD / NVMe, le problème soit devenu moins perceptible.

pg_stat_all_indexes est des statistiques sur les index. Elle n'est pas grande. Et nous pouvons en tirer des informations sur l'utilisation des index. Et par exemple, nous pouvons déterminer quels index nous avons en plus.

Comme je l'ai déjà dit, update ne met pas seulement à jour les tables, il met également à jour les index. Par conséquent, si nous avons beaucoup d'index sur la table, lors de la mise à jour des lignes de la table, les index des champs indexés doivent également être mis à jour, et si nous avons des index inutilisés pour lesquels il n'y a pas de scans d'index, alors ils restent avec nous comme lest. Et vous devez vous en débarrasser. Pour cela, nous avons besoin d'un champ idx_scan. Nous regardons juste le nombre de balayages d'index. Si les index n'ont aucune analyse sur une période de stockage de statistiques relativement longue (au moins 2-3 semaines), il s'agit très probablement de mauvais index, nous devons nous en débarrasser.

Note: Lors de la recherche d'index inutilisés dans le cas de clusters de réplication en continu, vous devez vérifier tous les nœuds du cluster, car les statistiques ne sont pas globales, et si l'index n'est pas utilisé sur le maître, alors il peut être utilisé sur les répliques (s'il y a une charge).

Deux liens :

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Voici des exemples de requêtes plus avancées sur la façon de rechercher des index inutilisés.

Le deuxième lien est une requête plutôt intéressante. Il y a une logique très non triviale là-dedans. Je le recommande pour examen.

Quoi d'autre devrait être résumé par des index?

Les index inutilisés sont mauvais.
Ils prennent de la place.
Ralentir les opérations de mise à jour.
Travail supplémentaire pour l'aspirateur.

Si nous supprimons les index inutilisés, nous ne ferons qu'améliorer la base de données.

La vue suivante est pg_stat_activity. Ceci est un analogue de l'utilitaire ps, uniquement dans PostgreSQL. Si ps'Oh vous regardez les processus dans le système d'exploitation, puis pg_stat_activity vous montrera l'activité à l'intérieur de PostgreSQL.

Que pouvons-nous en tirer ?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

Nous pouvons voir l'activité globale qui se passe dans la base de données. Nous pouvons faire un nouveau déploiement. Tout y a explosé, les nouvelles connexions ne sont pas acceptées, les erreurs affluent dans l'application.

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

Nous pouvons exécuter une requête comme celle-ci et voir le pourcentage total de connexions par rapport à la limite de connexion maximale et voir qui nous avons le plus de connexions. Et dans ce cas précis, nous voyons que l'utilisateur cron_role ouvert 508 connexions. Et quelque chose lui est arrivé. Il faut s'en occuper et voir. Et il est tout à fait possible qu'il s'agisse d'une sorte de nombre anormal de connexions.

Si nous avons une charge OLTP, les requêtes doivent être rapides, très rapides et il ne doit pas y avoir de longues requêtes. Cependant, s'il y a de longues demandes, à court terme, il n'y a rien à craindre, mais à long terme, les requêtes longues endommagent la base de données, elles augmentent l'effet de gonflement des tables lorsque la fragmentation des tables se produit. Les requêtes volumineuses et longues doivent être éliminées.

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

Attention : avec une telle requête, nous pouvons définir des requêtes et des transactions longues. Nous utilisons la fonction clock_timestamp() pour déterminer le temps de travail. Les longues requêtes qu'on a trouvées, on peut s'en souvenir, les exécuter explain, regardez les plans et optimisez en quelque sorte. Nous tournons les longues demandes actuelles et continuons à vivre.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Les transactions incorrectes sont inactives dans les transactions et inactives dans les transactions (abandonnées).

Qu'est-ce que ça veut dire? Les transactions ont plusieurs états. Et l'un de ces états peut prendre à tout moment. Il y a un champ pour définir les états state dans cette vue. Et nous l'utilisons pour déterminer l'état.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Et, comme je l'ai dit plus haut, ces deux états inactif dans la transaction et inactif dans la transaction (abandonnée) sont incorrects. Ce que c'est? C'est à ce moment que l'application a ouvert une transaction, effectué certaines actions et poursuivi ses activités. La transaction reste ouverte. Il se bloque, rien ne s'y passe, il prend une connexion, verrouille les lignes modifiées et augmente potentiellement encore le gonflement des autres tables, en raison de l'architecture du moteur transactionnel Postrges. Et de telles transactions devraient également être fusillées, car elles sont nuisibles en général, dans tous les cas.

Si vous voyez que vous en avez plus de 5-10-20 dans votre base de données, alors vous devez vous inquiéter et commencer à faire quelque chose avec eux.

Ici, nous utilisons également pour le temps de calcul clock_timestamp(). Nous filmons les transactions, nous optimisons l'application.

Comme je l'ai dit plus haut, les verrous se produisent lorsque deux ou plusieurs transactions sont en concurrence pour une ou un groupe de ressources. Pour cela, nous avons un champ waiting avec valeur booléenne true ou false.

Vrai - cela signifie que le processus attend, quelque chose doit être fait. Lorsqu'un processus attend, le client qui a lancé le processus attend également. Le client dans le navigateur est assis et attend également.

Attention: _A partir de Postgres 9.6, le champ waiting supprimé et remplacé par deux autres champs informatifs wait_event_type и wait_event._

Que faire? Si vous voyez vrai depuis longtemps, vous devriez vous débarrasser de telles demandes. Nous tirons juste de telles transactions. Nous écrivons aux développeurs ce qui doit être optimisé d'une manière ou d'une autre afin qu'il n'y ait pas de course aux ressources. Et puis les développeurs optimisent l'application pour que cela n'arrive pas.

Et le cas extrême, mais en même temps potentiellement non mortel, est survenance de blocages. Deux transactions ont mis à jour deux ressources, puis elles y accèdent à nouveau, déjà à des ressources opposées. Dans ce cas, PostgreSQL prend et exécute la transaction elle-même afin que l'autre puisse continuer à fonctionner. C'est une situation sans issue et elle ne se comprend pas. Par conséquent, PostgreSQL est obligé de prendre des mesures extrêmes.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

Et voici deux requêtes qui vous permettent de suivre les verrous. Nous utilisons la vue pg_locks, qui vous permet de suivre les serrures lourdes.

Et le premier lien est le texte de la demande lui-même. C'est assez long.

Et le deuxième lien est un article sur les serrures. C'est utile à lire, c'est très intéressant.

Alors que voyons-nous ? Nous voyons deux demandes. Transaction avec ALTER TABLE est une transaction bloquante. Cela a commencé, mais ne s'est pas terminé, et l'application qui a publié cette transaction fait autre chose quelque part. Et la deuxième demande est la mise à jour. Il attend que la table de modification soit terminée avant de continuer son travail.

C'est ainsi que nous pouvons savoir qui a enfermé qui, qui détient qui, et nous pouvons traiter cela plus loin.

Le module suivant est pg_stat_statements. Comme je l'ai dit, c'est un module. Pour l'utiliser, vous devez charger sa bibliothèque dans la configuration, redémarrer PostgreSQL, installer le module (avec une seule commande), puis nous aurons une nouvelle vue.

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

Que pouvons-nous en tirer ? Si on parle de choses simples, on peut prendre le temps moyen d'exécution des requêtes. Le temps presse, ce qui signifie que PostgreSQL répond lentement et que quelque chose doit être fait.

Nous pouvons voir les transactions d'écriture les plus actives dans la base de données qui modifient les données dans les tampons partagés. Voyez qui met à jour ou supprime des données là-bas.

Et nous pouvons simplement examiner différentes statistiques pour ces demandes.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

Nous pg_stat_statements utilisé pour créer des rapports. Nous réinitialisons les statistiques une fois par jour. Accumulons-le. Avant de réinitialiser les statistiques la prochaine fois, nous créons un rapport. Voici un lien vers le rapport. Vous pouvez le regarder.

Qu'est-ce que nous faisons? Nous calculons les statistiques globales pour toutes les requêtes. Ensuite, pour chaque requête, nous comptons sa contribution individuelle à cette statistique globale.

Et que voit-on ? Nous pouvons voir le temps d'exécution total de toutes les requêtes d'un type particulier par rapport à toutes les autres requêtes. Nous pouvons examiner l'utilisation du processeur et des E / S par rapport à l'image globale. Et déjà d'optimiser ces demandes. Nous construisons les principales requêtes sur la base de ce rapport et nous obtenons déjà matière à réflexion sur ce qu'il faut optimiser.

Qu'avons-nous dans les coulisses ? Il y a encore quelques soumissions que je n'ai pas prises en considération, parce que le temps est limité.

Il est pgstattuple est également un module supplémentaire du package standard des contributions. Il permet d'évaluer bloat tables, soi-disant. fragmentation des tables. Et si la fragmentation est importante, vous devez la supprimer, utilisez différents outils. Et fonction pgstattuple fonctionne longtemps. Et plus il y a de tables, plus cela fonctionnera longtemps.

La prochaine contribution est pg_buffercache. Il vous permet d'inspecter les tampons partagés : à quelle intensité et pour quelles tables les pages de tampon sont utilisées. Et cela vous permet simplement de regarder dans les tampons partagés et d'évaluer ce qui s'y passe.

Le module suivant est pgfincore. Il vous permet d'effectuer des opérations de table de bas niveau via un appel système mincore(), c'est-à-dire qu'il vous permet de charger la table dans des tampons partagés ou de la décharger. Et cela permet, entre autres, d'inspecter le cache de pages du système d'exploitation, c'est-à-dire combien la table occupe dans le cache de pages, dans les tampons partagés, et permet simplement d'évaluer la charge sur la table.

Le module suivant est pg_stat_kcache. Il utilise également l'appel système getrusage(). Et il l'exécute avant et après l'exécution de la requête. Et dans les statistiques obtenues, cela nous permet d'estimer combien notre demande a dépensé en E / S disque, c'est-à-dire les opérations avec le système de fichiers et regarde l'utilisation du processeur. Cependant, le module est jeune (khe-khe) et pour son travail, il nécessite PostgreSQL 9.4 et pg_stat_statements, dont j'ai parlé plus tôt.

La possibilité d'utiliser des statistiques est utile. Vous n'avez pas besoin de logiciel tiers. Vous pouvez regarder, voir, faire quelque chose, jouer.
L'utilisation des statistiques est facile, c'est du SQL simple. Vous avez recueilli une demande, l'avez compilée, l'avez envoyée, l'avez consultée.
Les statistiques aident à répondre aux questions. Si vous avez des questions, vous vous tournez vers les statistiques - regardez, tirez des conclusions, analysez les résultats.
Et expérimentez. Beaucoup de demandes, beaucoup de données. Vous pouvez toujours optimiser une requête existante. Vous pouvez créer votre propre version de la demande qui vous convient mieux que l'original et l'utiliser.