Une approche industrielle du réglage de PostgreSQL : expériences avec des bases de données. Nikolaï Samokhvalov

Je vous suggère de lire la transcription du rapport de Nikolai Samokhvalov « Approche industrielle du réglage de PostgreSQL : expériences sur les bases de données »

Shared_buffers = 25 % – est-ce beaucoup ou un peu ? Ou juste, n'est-ce pas ? Comment savoir si cette recommandation – plutôt dépassée – est appropriée dans votre cas particulier ?

Il est temps d'aborder la question de la sélection des paramètres postgresql.conf "comme un adulte". Non pas avec l'aide de « tuners automatiques » aveugles ou de conseils obsolètes provenant d'articles et de blogs, mais sur la base de :

1. des expérimentations strictement vérifiées sur bases de données, réalisées automatiquement, en grande quantité et dans des conditions aussi proches que possible de celles du « combat »,
2. compréhension approfondie des fonctionnalités du SGBD et du système d'exploitation.

Utilisation de Nancy CLI (https://gitlab.com/postgres.ai/nancy), nous examinerons un exemple spécifique - les fameux shared_buffers - dans différentes situations, dans différents projets et essaierons de comprendre comment choisir le paramètre optimal pour notre infrastructure, notre base de données et notre charge.

Nous parlerons d'expérimentations avec des bases de données. C'est une histoire qui dure un peu plus de six mois.

À propos de moi. Expérience avec Postgres depuis plus de 14 ans. Un certain nombre de sociétés de réseaux sociaux ont été fondées. Postgres était et est utilisé partout.

Egalement le groupe RuPostgres sur Meetup, 2ème place mondiale. Nous approchons petit à petit des 2 000 personnes. RuPostgres.org.

Et sur les PC de diverses conférences, dont Highload, je suis responsable des bases de données, notamment Postgres depuis le tout début.

Et au cours des dernières années, j'ai redémarré ma pratique de conseil Postgres sur 11 fuseaux horaires à partir d'ici.

Et lorsque j'ai fait cela il y a quelques années, j'ai eu une certaine interruption dans le travail manuel actif avec Postgres, probablement depuis 2010. J'ai été surpris de voir à quel point la routine de travail d'un administrateur de base de données a peu changé et combien de travail manuel doit encore être utilisé. Et j’ai immédiatement pensé que quelque chose n’allait pas ici, je devais tout automatiser davantage.

Et comme tout se faisait à distance, la plupart des clients étaient dans les nuages. Et bien évidemment, beaucoup de choses ont déjà été automatisées. Nous en reparlerons plus tard. Autrement dit, tout cela a donné naissance à l'idée qu'il devrait y avoir un certain nombre d'outils, c'est-à-dire une sorte de plate-forme qui automatiserait presque toutes les actions DBA afin de pouvoir gérer un grand nombre de bases de données.

Ce rapport ne comprendra pas :

• Des « solutions miracles » et des déclarations telles que : définissez 8 Go ou 25 % de shared_buffers et tout ira bien. Il n'y aura pas grand-chose sur shared_buffers.
• Des « entrailles » hardcore.

Qu'est-ce qui va arriver?

• Il y aura des principes d’optimisation que nous appliquerons et développerons. Il y aura toutes sortes d'idées qui surgiront en cours de route et divers outils que nous créons pour la plupart en Open Source, c'est-à-dire que nous faisons la base en Open Source. De plus, nous avons des tickets, toute communication est pratiquement Open Source. Vous pouvez voir ce que nous faisons actuellement, ce qui sera dans la prochaine version, etc.
• Il y aura également une certaine expérience de l'utilisation de ces principes, de ces outils dans un certain nombre d'entreprises : des petites startups aux grandes entreprises.

Comment tout cela évolue-t-il ?

Premièrement, la tâche principale d’un DBA, en plus d’assurer la création des instances, le déploiement des sauvegardes, etc., est de trouver les goulots d’étranglement et d’optimiser les performances.

Maintenant, c'est configuré comme ça. On regarde le monitoring, on voit quelque chose, mais il nous manque certains détails. Nous commençons à creuser plus soigneusement, généralement avec nos mains, et comprenons quoi en faire d'une manière ou d'une autre.

Et il existe deux approches. Pg_stat_statements est la solution par défaut pour identifier les requêtes lentes. Et analyse des journaux Postgres à l'aide de pgBadger.

Chaque approche présente de sérieux inconvénients. Dans la première approche, nous avons supprimé tous les paramètres. Et si l'on voit la table des groupes SELECT * FROM où la colonne est égale au "?" ou « $ » depuis Postgres 10. Nous ne savons pas s'il s'agit d'une analyse d'index ou d'une analyse séquentielle. Cela dépend beaucoup du paramètre. Si vous y remplacez une valeur rarement rencontrée, ce sera une analyse d'index. Si vous y remplacez une valeur qui occupe 90 % du tableau, l'analyse séquentielle sera évidente, car Postgres connaît les statistiques. Et c'est un gros inconvénient de pg_stat_statements, même si certains travaux sont en cours.

Le plus gros inconvénient de l'analyse des journaux est que vous ne pouvez généralement pas vous permettre "log_min_duration_statement = 0". Et nous en parlerons aussi. Par conséquent, vous ne voyez pas l’ensemble du tableau. Et certaines requêtes, très rapides, peuvent consommer une énorme quantité de ressources, mais vous ne les verrez pas car elles sont inférieures à votre seuil.

Comment les administrateurs de base de données résolvent-ils les problèmes qu’ils rencontrent ?

Par exemple, nous avons trouvé un problème. Que fait-on habituellement ? Si vous êtes développeur, vous ferez quelque chose sur une instance qui n'est pas de la même taille. Si vous êtes un administrateur de base de données, vous disposez d'une mise en scène. Et il ne peut y en avoir qu'un. Et il avait six mois de retard. Et vous pensez que vous irez en production. Et même les administrateurs de base de données expérimentés vérifient ensuite en production, sur une réplique. Et il arrive qu'ils créent un index temporaire, s'assurent qu'il aide, le déposent et le donnent aux développeurs afin qu'ils puissent le mettre dans les fichiers de migration. C’est le genre d’absurdités qui se produisent actuellement. Et c'est un problème.

• Ajustez les configurations.
• Optimisez l’ensemble des index.
• Modifiez la requête SQL elle-même (c'est la méthode la plus difficile).
• Ajoutez de la capacité (le moyen le plus simple dans la plupart des cas).

Il se passe beaucoup de choses avec ces choses. Il y a beaucoup de handles dans Postgres. Il y a beaucoup à savoir. Il existe de nombreux index dans Postgres, grâce également aux organisateurs de cette conférence. Et tout cela doit être connu, et c’est ce qui donne aux non-administrateurs de bases de données l’impression qu’ils pratiquent la magie noire. Autrement dit, il faut étudier pendant 10 ans pour commencer à comprendre tout cela normalement.

Et je suis un combattant contre cette magie noire. Je veux tout faire pour qu'il y ait de la technologie, et qu'il n'y ait pas d'intuition dans tout ça.

Exemples de vie

J'ai observé cela dans au moins deux projets, dont le mien. Un autre article de blog nous indique qu'une valeur de 1 000 pour default_statistict_target est bonne. D'accord, essayons-le en production.

Et nous voilà, utilisant notre outil deux ans plus tard, à l’aide d’expérimentations sur les bases de données dont nous parlons aujourd’hui, nous pouvons comparer ce qui était et ce qui est devenu.

Et pour cela, nous devons créer une expérience. Il se compose de quatre parties.

• Le premier est l’environnement. Nous avons besoin d'un morceau de matériel. Et quand je viens dans une entreprise et que je signe un contrat, je leur dis de me donner le même matériel qu'en production. Pour chacun de vos Masters, j'ai besoin d'au moins un matériel comme celui-ci. Soit il s'agit d'une instance de machine virtuelle sur Amazon ou Google, soit j'ai besoin exactement du même matériel. Autrement dit, je veux recréer l'environnement. Et dans la notion d'environnement nous incluons la version majeure de Postgres.
• La deuxième partie fait l’objet de notre recherche. Ceci est une base de données. Il peut être créé de plusieurs manières. Je vais vous montrer comment.
• La troisième partie est la charge. C'est le moment le plus difficile.
• Et la quatrième partie est ce que nous vérifions, c’est-à-dire ce que nous comparerons avec quoi. Disons que l'on peut modifier un ou plusieurs paramètres dans la configuration, ou que l'on peut créer un index, etc.

Nous lançons une expérimentation. Voici pg_stat_statements. À gauche, ce qui s'est passé. À droite - que s'est-il passé.

A gauche default_statistics_target = 100, à droite = 1 000. On voit que cela nous a aidé. Dans l'ensemble, tout s'est amélioré de 8 %.

Mais si nous faisons défiler vers le bas, il y aura des groupes de requêtes de pgBadger ou de pg_stat_statements. Il existe deux options. Nous verrons que certaines demandes ont chuté de 88%. Et voici l’approche technique. On peut creuser plus loin à l’intérieur car on se demande pourquoi il a coulé. Vous devez comprendre ce qui s'est passé avec les statistiques. Pourquoi plus de catégories dans les statistiques conduisent à de moins bons résultats.

Ou nous ne pouvons pas creuser, mais faire « ALTER TABLE ... ALTER COLUMN » et renvoyer 100 buckets aux statistiques de cette colonne. Et puis, avec une autre expérience, nous pouvons nous assurer que ce correctif a aidé. Tous. Il s’agit d’une approche d’ingénierie qui nous aide à avoir une vue d’ensemble et à prendre des décisions basées sur des données plutôt que sur l’intuition.

Quelques exemples provenant d'autres domaines. Il existe des tests CI dans les tests depuis de nombreuses années. Et aucun projet sensé ne pourrait vivre sans tests automatisés.

Dans d’autres industries : dans l’aviation, dans l’automobile, quand on teste l’aérodynamique, on a aussi la possibilité de faire des expérimentations. Nous ne lancerons pas quelque chose d’un dessin directement dans l’espace, ni ne mettrons immédiatement une voiture sur la piste. Par exemple, il y a une soufflerie.

Nous pouvons tirer des conclusions des observations d’autres industries.

Premièrement, nous avons un environnement particulier. C'est proche de la production, mais pas proche. Sa principale caractéristique est qu’il doit être bon marché, reproductible et aussi automatisé que possible. Et il doit également exister des outils spéciaux pour effectuer une analyse détaillée.

Très probablement, lorsque nous lançons un avion et volons, nous avons moins de possibilités d'étudier chaque millimètre de la surface de l'aile que dans une soufflerie. Nous disposons de davantage d'outils de diagnostic. Nous pouvons nous permettre de transporter des objets plus lourds que nous ne pouvons pas nous permettre de transporter dans un avion. Idem avec Postgres. Nous pouvons, dans certains cas, activer la journalisation complète des requêtes pendant les expériences. Et nous ne voulons pas faire cela en production. Nous pourrions même prévoir d'activer cela en utilisant auto_explain.

Et comme je l'ai dit, un haut niveau d'automatisation signifie que nous appuyons sur le bouton et répétons. C’est ainsi que cela devrait être, pour qu’il y ait beaucoup d’expérimentation, pour que cela soit opérationnel.

Nancy CLI - la fondation du « laboratoire de bases de données »

Et donc nous avons fait cette chose. Autrement dit, j'ai parlé de ces idées en juin, il y a presque un an. Et nous avons déjà ce qu'on appelle Nancy CLI en Open Source. C’est la base pour construire un laboratoire de bases de données.

Nancy — C'est en Open Source, sur Gitlab. Vous pouvez le dire, vous pouvez l'essayer. J'ai fourni un lien dans les diapositives. Vous pouvez cliquer dessus et il sera là aider avec tout mon respect.

Bien sûr, beaucoup de choses sont encore en développement. Il y a beaucoup d'idées là-bas. Mais c’est quelque chose que nous utilisons presque tous les jours. Et quand nous avons une idée - pourquoi lorsque nous supprimons 40 000 000 de lignes, tout se résume à IO, alors nous pouvons mener une expérience et regarder plus en détail pour comprendre ce qui se passe, puis essayer de le corriger à la volée. Autrement dit, nous faisons une expérience. Par exemple, nous modifions quelque chose et voyons ce qui se passe à la fin. Et nous ne faisons pas cela en production. C’est l’essence de l’idée.

Où cela peut-il fonctionner ? Cela peut fonctionner localement, c'est-à-dire que vous pouvez le faire n'importe où, vous pouvez même l'exécuter sur un MacBook. Nous avons besoin d'un docker, allons-y. C'est tout. Vous pouvez l'exécuter dans certains cas sur un élément matériel ou sur une machine virtuelle, n'importe où.

Et il est également possible d'exécuter à distance sur Amazon dans l'instance EC2, par endroits. Et c’est une opportunité très intéressante. Par exemple, hier, nous avons mené plus de 500 expériences sur l'instance i3, en commençant par la plus jeune et en terminant par i3-16-xlarge. Et 500 expériences nous coûtent 64 dollars. Chacune durait 15 minutes. Autrement dit, étant donné que des spots y sont utilisés, c'est très bon marché - une réduction de 70 %, la facturation à la seconde d'Amazon. Vous pouvez faire beaucoup de choses. Vous pouvez faire de vraies recherches.

Et trois versions majeures de Postgres sont prises en charge. Ce n’est pas si difficile de terminer certaines anciennes versions ainsi que la nouvelle 12ème version.

Nous pouvons définir un objet de trois manières. Ce:

• Fichier dump/sql.
• La méthode principale consiste à cloner le répertoire PGDATA. En règle générale, il provient du serveur de sauvegarde. Si vous disposez de sauvegardes binaires normales, vous pouvez créer des clones à partir de là. Si vous disposez de nuages, un bureau cloud comme Amazon et Google le fera pour vous. C'est le moyen le plus important de cloner une production réelle. C'est ainsi que nous nous développons.
• Et la dernière méthode convient à la recherche lorsque vous souhaitez comprendre comment quelque chose fonctionne dans Postgres. C'est pgbench. Vous pouvez générer en utilisant pgbench. C'est juste une option "db-pgbench". Vous lui dites à quelle échelle. Et tout sera généré dans le cloud, comme indiqué.

Et chargez :

• Nous pouvons exécuter le chargement dans un seul thread SQL. C'est la manière la plus primitive.
• Et nous pouvons émuler la charge. Et nous pouvons l’imiter tout d’abord de la manière suivante. Nous devons collecter tous les journaux. Et c'est douloureux. Je vais vous montrer pourquoi. Et nous jouons en utilisant pgreplay, qui est intégré à Nancy.
• Ou une autre option. La charge dite artisanale, que nous effectuons avec un certain effort. En analysant notre charge actuelle sur le système de combat, nous retirons les principaux groupes de demandes. Et en utilisant pgbench, nous pouvons émuler cette charge en laboratoire.

• Soit nous devons effectuer une sorte de SQL, c'est-à-dire que nous vérifions une sorte de migration, y créons un index, y exécutons ANALAZE. Et nous regardons ce qui s’est passé avant et après le vide. En général, n'importe quel SQL.
• Soit on change un ou plusieurs paramètres dans la config. Nous pouvons nous dire de vérifier, par exemple, 100 valeurs​​sur Amazon pour notre base de données de téraoctets. Et dans quelques heures vous aurez le résultat. En règle générale, le déploiement d'une base de données de téraoctets vous prendra plusieurs heures. Mais il y a un patch en développement, nous avons une série possible, c'est-à-dire que vous pouvez systématiquement utiliser les mêmes pgdata sur le même serveur et vérifier. Postgres redémarrera et les caches seront réinitialisés. Et vous pouvez conduire la charge.

• Un répertoire arrive avec un tas de fichiers différents, à partir des instantanés pgétat***. Et la chose la plus intéressante est pg_stat_statements, pg_stat_kcacke. Ce sont deux extensions qui analysent les requêtes. Et pg_stat_bgwriter contient non seulement des statistiques sur pgwriter, mais également sur le point de contrôle et sur la façon dont les backends eux-mêmes déplacent les tampons sales. Et tout cela est intéressant à voir. Par exemple, lorsque nous configurons shared_buffers, il est très intéressant de voir combien tout le monde a remplacé.
• Les journaux Postgres arrivent également. Deux journaux : un journal de préparation et un journal de lecture de chargement.
• FlameGraphs est une fonctionnalité relativement nouvelle.
• De plus, si vous avez utilisé les options pgreplay ou pgbench pour lire la charge, leur sortie sera native. Et vous verrez la latence et le TPS. Il sera possible de comprendre comment ils l’ont vu.
• Informations système.
• Vérifications de base du processeur et des E/S. C'est plus pour l'instance EC2 dans Amazon, lorsque vous souhaitez lancer 100 instances identiques dans un thread et y exécuter 100 exécutions différentes, vous aurez alors 10 000 expériences. Et vous devez vous assurer de ne pas tomber sur une instance défectueuse qui est déjà opprimée par quelqu'un. D'autres sont actifs sur ce matériel et il vous reste peu de ressources. Il est préférable d'écarter de tels résultats. Et avec l'aide de sysbench d'Alexey Kopytov, nous effectuons plusieurs brèves vérifications qui viendront et pourront être comparées avec d'autres, c'est-à-dire vous comprendrez comment se comporte le CPU et comment se comportent les E/S.

Quelles sont les difficultés techniques à partir de l’exemple de différentes entreprises ?

Disons que nous voulons répéter la charge réelle en utilisant des journaux. C'est une excellente idée si c'est écrit sur pgreplay Open Source. Nous l'utilisons. Mais pour que cela fonctionne correctement, vous devez activer la journalisation complète des requêtes avec des paramètres et un timing.

Il existe quelques complications liées à la durée et à l'horodatage. Nous allons vider toute cette cuisine. La principale question est de savoir si vous pouvez vous le permettre ou non ?

https://gist.github.com/NikolayS/08d9b7b4845371d03e195a8d8df43408

Le problème est qu'il n'est peut-être pas disponible. Tout d'abord, vous devez comprendre quel flux sera écrit dans le journal. Si vous disposez de pg_stat_statements, vous pouvez utiliser cette requête (le lien sera disponible dans les diapositives) pour comprendre approximativement combien d'octets seront écrits par seconde.

Nous regardons la durée de la demande. Nous négligeons le fait qu'il n'y a pas de paramètres, mais nous connaissons la longueur de la requête et nous savons combien de fois par seconde elle a été exécutée. De cette façon, nous pouvons estimer approximativement le nombre d'octets par seconde. Nous pouvons faire deux fois plus d'erreurs, mais nous comprendrons certainement l'ordre de cette façon.

Nous pouvons voir que 802 fois par seconde cette requête est exécutée. Et nous voyons que bytes_per sec – 300 ko/s sera écrit plus ou moins. Et, en règle générale, nous pouvons nous permettre un tel flux.

Mais! Le fait est qu’il existe différents systèmes de journalisation. Et la valeur par défaut des gens est généralement "syslog".

Et si vous avez syslog, vous pourriez avoir une image comme celle-ci. Nous allons prendre pgbench, activer la journalisation des requêtes et voir ce qui se passe.

Sans journalisation - c'est la colonne de gauche. Nous avons obtenu 161 000 TPS. Avec syslog - c'est dans Ubuntu 16.04 sur Amazon, nous obtenons 37 000 TPS. Et si nous passons à deux autres méthodes de journalisation, la situation est bien meilleure. Autrement dit, nous nous attendions à une baisse, mais pas dans la même mesure.

Et sur CentOS 7, auquel journald participe également, transformant les logs au format binaire pour une recherche facile, etc., alors c'est un cauchemar là-bas, on laisse tomber 44 fois en TPS.

Et c’est avec ça que les gens vivent. Et souvent dans les entreprises, surtout les grandes, cela est très difficile à changer. Si vous pouvez vous éloigner de Syslog, alors éloignez-vous-en.

• Évaluez les IOPS et le flux d’écriture.
• Vérifiez votre système de journalisation.
• Si la charge projetée est excessivement importante, envisagez l’échantillonnage.

Nous avons pg_stat_statements. Comme je l'ai dit, il doit être là. Et nous pouvons prendre et décrire chaque groupe de requêtes d'une manière particulière dans un dossier. Et puis nous pouvons utiliser une fonctionnalité très pratique de pgbench - c'est la possibilité d'insérer plusieurs fichiers à l'aide de l'option « -f ».

Il comprend beaucoup de "-f". Et vous pouvez indiquer à l'aide du « @ » à la fin quel partage chaque fichier doit avoir. Autrement dit, on peut dire que nous faisons cela dans 10 % des cas, et ceci dans 20 % des cas. Et cela nous rapprochera de ce que nous voyons en production.

Comment allons-nous comprendre ce que nous avons en production ? Quel partage et comment ? C'est un peu un aparté. Nous avons un produit supplémentaire vérification postgres. Egalement une base en Open Source. Et nous le développons désormais activement.

Il est né pour des raisons légèrement différentes. Pour des raisons qui font que la surveillance ne suffit pas. Autrement dit, vous venez, regardez la base, regardez les problèmes qui existent. Et, en règle générale, vous effectuez un bilan de santé. Si vous êtes un administrateur de base de données expérimenté, effectuez health_check. Nous avons examiné l'utilisation des index, etc. Si vous avez OKmeter, alors tant mieux. C'est une surveillance intéressante pour Postgres. OKmeter.io – veuillez l’installer, tout y est très bien fait. C'est payant.

Si vous n’en avez pas, vous n’avez généralement pas grand-chose. En surveillance, il y a généralement du CPU, des E/S, puis des réservations, et c'est tout. Et nous avons besoin de plus. Nous devons voir comment fonctionne l'autovacuum, comment fonctionne le point de contrôle, dans io nous devons séparer le point de contrôle du bgwriter et des backends, etc.

Le problème est que lorsque vous aidez une grande entreprise, elle ne peut pas mettre en œuvre quelque chose rapidement. Ils ne peuvent pas acheter rapidement OKmeter. Peut-être qu'ils l'achèteront dans six mois. Ils ne peuvent pas livrer rapidement certains colis.

Et nous avons eu l’idée que nous avions besoin d’un outil spécial qui ne nécessite rien à installer, c’est-à-dire que vous n’avez rien à installer du tout en production. Installez-le sur votre ordinateur portable ou sur un serveur d'observation à partir duquel vous l'exécuterez. Et il analysera beaucoup de choses : le système d'exploitation, le système de fichiers et Postgres lui-même, en effectuant quelques requêtes légères qui peuvent être exécutées directement en production et rien n'échouera.

Nous l'avons appelé Postgres-checkup. En termes médicaux, il s’agit d’un bilan de santé régulier. Si c’est sur le thème de l’automobile, c’est comme la maintenance. Vous effectuez l'entretien de votre voiture tous les six mois ou un an, selon la marque. Effectuez-vous la maintenance de votre base ? Autrement dit, faites-vous régulièrement des recherches approfondies ? Il faut le faire. Si vous faites des sauvegardes, faites une vérification, ce n'est pas moins important.

Et nous avons un tel outil. Il a commencé à émerger activement il y a seulement trois mois environ. Il est encore jeune, mais il y en a beaucoup.

Collecte des groupes de requêtes les plus « influents » - rapport K003 dans Postgres-checkup

Et il y a un groupe de rapports K. Trois rapports jusqu'à présent. Et il existe un tel rapport K003. Il y a le haut de pg_stat_statements, trié par total_time.

Lorsque nous trions les groupes de requêtes par total_time, nous voyons en haut le groupe qui charge le plus notre système, c'est-à-dire qui consomme le plus de ressources. Pourquoi dois-je nommer des groupes de requêtes ? Parce que nous avons jeté les paramètres. Ce ne sont plus des requêtes, mais des groupes de requêtes, c'est à dire qu'elles sont abstraites.

Et si nous optimisons de haut en bas, nous allégerons nos ressources et retarderons le moment où nous devrons mettre à niveau. C'est un très bon moyen d'économiser de l'argent.

Ce n'est peut-être pas une très bonne façon de prendre soin des utilisateurs, car nous ne voyons peut-être pas de cas rares, mais très ennuyeux, où une personne a attendu 15 secondes. Au total, ils sont si rares qu’on ne les voit pas, mais on a affaire à des ressources.

Que s'est-il passé dans ce tableau ? Nous avons pris deux instantanés. Postgres_checkup vous donnera un delta pour chaque métrique : temps total, appels, lignes, shared_blks_read, etc. Ça y est, le delta a été calculé. Le gros problème avec pg_stat_statements est qu'il ne se souvient pas de la date à laquelle il a été réinitialisé. Si pg_stat_database s'en souvient, alors pg_stat_statements ne s'en souvient pas. Vous voyez qu’il y a un nombre de 1 000 000, mais nous ne savons pas d’où nous avons compté.

Et là, nous le savons, nous avons ici deux instantanés. Nous savons que le delta dans ce cas était de 56 secondes. Un écart très court. Trié par total_time. Et puis nous pouvons différencier, c'est-à-dire que nous divisons toutes les mesures par durée. Si nous divisons chaque métrique par durée, nous aurons le nombre d'appels par seconde.

Ensuite, total_time par seconde est ma métrique préférée. Il se mesure en secondes, par seconde, c'est-à-dire combien de secondes il a fallu à notre système pour exécuter ce groupe de requêtes par seconde. Si vous y voyez plus d'une seconde par seconde, cela signifie que vous avez dû donner plus d'un cœur. C'est une très bonne mesure. Vous comprendrez que cet ami, par exemple, a besoin d'au moins trois cœurs.

C’est notre savoir-faire, je n’ai jamais rien vu de pareil nulle part. Veuillez noter que c'est une chose très simple : seconde par seconde. Parfois, lorsque votre CPU est à 100 %, alors une demi-heure par seconde, c'est-à-dire que vous avez passé une demi-heure à faire uniquement ces requêtes.

Ensuite, nous voyons les lignes par seconde. Nous savons combien de lignes par seconde il a renvoyé.

Et puis il y a aussi une chose intéressante. Combien de shared_buffers nous lisons par seconde à partir des shared_buffers lui-même. Les hits étaient déjà là et nous avons récupéré les lignes du cache du système d'exploitation ou du disque. La première option est rapide et la seconde peut être rapide ou non, selon la situation.

Et la deuxième façon de différencier est de diviser le nombre de demandes dans ce groupe. Dans la deuxième colonne, vous aurez toujours une requête divisée par requête. Et puis c'est intéressant - combien de millisecondes y avait-il dans cette requête. Nous savons comment cette requête se comporte en moyenne. 101 millisecondes étaient nécessaires pour chaque requête. C'est la métrique traditionnelle que nous devons comprendre.

Combien de lignes chaque requête a-t-elle renvoyé en moyenne ? Nous voyons 8 ce groupe revenir. En moyenne, combien a été extrait du cache et lu. Nous voyons que tout est bien mis en cache. De solides succès pour le premier groupe.

Et la quatrième sous-chaîne de chaque ligne correspond au pourcentage du total. Nous avons des appels. Disons 1 000 000. Et nous pouvons comprendre quelle contribution ce groupe apporte. On voit que dans ce cas le premier groupe contribue pour moins de 0,01%. Autrement dit, il est si lent que nous ne le voyons pas dans l’ensemble. Et le deuxième groupe est à 5% sur les appels. Autrement dit, 5 % de tous les appels appartiennent au deuxième groupe.

Total_time est également intéressant. Nous avons consacré 14 % de notre temps de travail total au premier groupe de demandes. Et pour le second - 11%, etc.

Je n’entrerai pas dans les détails, mais il y a là des subtilités. Nous affichons une erreur en haut, car lorsque nous comparons, les instantanés peuvent flotter, c'est-à-dire que certaines requêtes peuvent tomber et ne peuvent plus être présentes dans la seconde, tandis que de nouvelles peuvent apparaître. Et là, nous calculons l'erreur. Si vous voyez 0, alors c'est bien. Il n'y a aucune erreur. Si le taux d'erreur atteint 20 %, ce n'est pas grave.

Puis nous revenons à notre sujet. Nous devons façonner la charge de travail. Nous procédons de haut en bas et avançons jusqu'à atteindre 80 ou 90 %. Il s'agit généralement de 10 à 20 groupes. Et nous créons des fichiers pour pgbench. Nous utilisons le hasard ici. Parfois, malheureusement, cela ne fonctionne pas. Et dans Postgres 12, il y aura davantage de possibilités d'utiliser cette approche.

Et puis nous gagnons ainsi 80 à 90 % en temps total. Que dois-je mettre après « @ » ? Nous regardons les appels, regardons combien d’intérêts il y a et comprenons que nous devons tant d’intérêts ici. A partir de ces pourcentages, nous pouvons comprendre comment équilibrer chacun des fichiers. Après cela, nous utilisons pgbench et allons travailler.

Nous avons également K001 et K002.

K001 est une grande chaîne avec quatre sous-chaînes. C'est une caractéristique de l'ensemble de notre chargement. Voir deuxième colonne et deuxième sous-ligne. Nous voyons cela environ une seconde et demie par seconde, c'est-à-dire s'il y a deux cœurs, alors ce sera bien. La capacité sera d'environ 75 %. Et ça fonctionnera comme ça. Si nous avons 10 cœurs, alors nous serons généralement calmes. De cette façon, nous pouvons évaluer les ressources.

K002 est ce que j'appelle des classes de requêtes, c'est-à-dire SELECT, INSERT, UPDATE, DELETE. Et séparément SELECT FOR UPDATE, car c'est un verrou.

Et ici, nous pouvons conclure que SELECT est constitué de lecteurs ordinaires - 82% de tous les appels, mais en même temps - 74% en total_time. Autrement dit, ils sont appelés beaucoup, mais consomment moins de ressources.

Et nous revenons à la question : « Comment choisir les bons shared_buffers ? » J'observe que la plupart des benchmarks sont basés sur l'idée - voyons quel sera le débit, c'est-à-dire quel sera le débit. Il est généralement mesuré en TPS ou QPS.

Et nous essayons d'extraire autant de transactions par seconde que possible de la voiture en utilisant des paramètres de réglage. Voici exactement 311 par seconde pour sélectionner.

Mais personne ne se rend au travail et rentre chez lui à toute vitesse. C'est idiot. Idem avec les bases de données. Nous n’avons pas besoin de rouler à toute vitesse, et personne ne le fait. Personne ne vit en production, qui dispose de 100 % de CPU. Bien que peut-être que quelqu'un vive, ce n'est pas bon.

L’idée est que nous conduisons généralement à 20 % de notre capacité, de préférence pas plus de 50 %. Et nous essayons avant tout d’optimiser le temps de réponse de nos utilisateurs. Autrement dit, nous devons tourner nos boutons pour qu'il y ait une latence minimale à 20% de vitesse, sous condition. C’est une idée que nous essayons également d’utiliser dans nos expériences.

Et enfin, des recommandations :

• Assurez-vous de faire Database Lab.
• Si possible, faites-le à la demande pour qu'il se déroule pendant un moment - jouez et jetez-le. Si vous avez des nuages, cela va de soi, c'est-à-dire que vous avez beaucoup de position debout.
• Être curieux. Et si quelque chose ne va pas, vérifiez expérimentalement comment il se comporte. Nancy peut être utilisée pour vous entraîner à vérifier le fonctionnement de la base.
• Et visez le temps de réponse minimum.
• Et n'ayez pas peur des sources Postgres. Lorsque vous travaillez avec des sources, vous devez connaître l'anglais. Il y a beaucoup de commentaires là-bas, tout y est expliqué.
• Et vérifiez régulièrement l'état de la base de données, au moins une fois tous les trois mois, manuellement ou via une vérification Postgres.

des questions

Merci beaucoup! Une chose très intéressante.

Deux morceaux.

Oui, deux pièces. Seulement, je n'ai pas bien compris. Quand Nancy et moi travaillons, pouvons-nous modifier un seul paramètre ou tout un groupe ?

Nous avons un paramètre de configuration delta. Vous pouvez y tourner autant que vous le souhaitez à la fois. Mais vous devez comprendre que lorsque vous modifiez beaucoup de choses, vous pouvez tirer de mauvaises conclusions.

Oui. Pourquoi ai-je demandé ? Parce qu’il est difficile de faire des expériences quand on ne dispose que d’un seul paramètre. Vous le resserrez, voyez comment ça fonctionne. Je l'ai mis dehors. Ensuite, vous commencez le suivant.

Vous pouvez le resserrer en même temps, mais cela dépend bien sûr de la situation. Mais il vaut mieux tester une idée. Nous avons eu une idée hier. Nous avons eu une situation très serrée. Il y avait deux configurations. Et nous ne comprenions pas pourquoi il y avait une grande différence. Et l'idée est née que vous devez utiliser la dichotomie afin de comprendre et de trouver systématiquement quelle est la différence. Vous pouvez immédiatement rendre la moitié des paramètres identiques, puis un quart, etc. Tout est flexible.

Et il y a encore une question. Le projet est jeune et en développement. La documentation est déjà prête, y a-t-il une description détaillée ?

J'y ai spécifiquement fait un lien vers la description des paramètres. C'est là. Mais beaucoup de choses ne sont pas encore là. Je recherche des personnes partageant les mêmes idées. Et je les retrouve lorsque je joue. C'est très cool. Quelqu'un travaille déjà avec moi, quelqu'un m'a aidé et a fait quelque chose là-bas. Et si ce sujet vous intéresse, donnez votre avis sur ce qui manque.

Une fois que nous aurons construit le laboratoire, il y aura peut-être des retours. Voyons. Merci!

Bonjour! Merci pour le rapport ! J'ai vu qu'il existe un support Amazon. Est-il prévu de soutenir le GSP ?

Bonne question. Nous avons commencé à le faire. Et nous l’avons gelé pour l’instant parce que nous voulons économiser de l’argent. Autrement dit, l'utilisation de run sur localhost est prise en charge. Vous pouvez créer une instance vous-même et travailler localement. D'ailleurs, c'est ce que nous faisons. Je fais ça chez Getlab, là-bas chez GSP. Mais nous ne voyons pas encore l’intérêt de procéder à une telle orchestration, car Google n’a pas d’endroits bon marché. Il y a ??? cas, mais ils ont des limites. Premièrement, ils n’ont toujours qu’une réduction de 70 % et vous ne pouvez pas jouer avec le prix là-bas. Sur les spots, nous augmentons le prix de 5 à 10 % pour réduire le risque que vous soyez expulsé. Autrement dit, vous enregistrez des places, mais elles peuvent vous être retirées à tout moment. Si vous enchérissez un peu plus haut que les autres, vous serez tué plus tard. Google a des spécificités complètement différentes. Et il y a une autre très mauvaise limitation : ils ne vivent que 24 heures. Et parfois, nous souhaitons mener une expérience pendant 5 jours. Mais vous pouvez le faire par endroits ; les spots durent parfois des mois.

Bonjour! Merci pour le rapport! Vous avez parlé de contrôle. Comment calculez-vous les erreurs stat_statements ?

Très bonne question. Je peux vous montrer et vous le dire en détail. En bref, nous regardons comment l'ensemble des groupes de requêtes a flotté : combien ont disparu et combien de nouveaux sont apparus. Et puis nous examinons deux métriques : total_time et Calls, il y a donc deux erreurs. Et nous regardons la contribution des groupes flottants. Il existe deux sous-groupes : ceux qui sont partis et ceux qui sont arrivés. Voyons quelle est leur contribution au tableau d’ensemble.

N'avez-vous pas peur qu'il y tourne deux ou trois fois entre les instantanés ?

Autrement dit, se sont-ils réinscrits ou quoi ?

Par exemple, cette demande a déjà été préemptée une fois, puis elle est arrivée et a été à nouveau préemptée, puis elle est revenue et a été à nouveau préemptée. Et vous avez calculé quelque chose ici, et où est-ce tout ?

Bonne question, il faudra regarder.

J'ai fait une chose similaire. C'était plus simple bien sûr, je l'ai fait seul. Mais j'ai dû réinitialiser, réinitialiser stat_statements et comprendre au moment de la capture instantanée qu'il y avait moins d'une certaine fraction, qui n'atteignait toujours pas le plafond de la quantité de stat_statements pouvant s'y accumuler. Et je crois comprendre que, très probablement, rien n'a été déplacé.

Oui, oui.

Mais je ne comprends pas comment procéder autrement de manière fiable.

Malheureusement, je ne me souviens pas exactement si nous utilisons le texte de la requête ou l'ID de requête avec pg_stat_statements et si nous nous concentrons dessus. Si nous nous concentrons sur queryid, alors en théorie nous comparons des choses comparables.

Non, il peut être expulsé plusieurs fois entre les instantanés et revenir.

Avec le même identifiant ?

Oui.

Nous allons étudier cela. Bonne question. Nous devons l'étudier. Mais pour l'instant, ce que l'on voit s'écrit soit 0...

C'est bien sûr un cas rare, mais j'ai été choqué quand j'ai découvert que stat_statemetns pouvait s'y déplacer.

Il peut y avoir beaucoup de choses dans Pg_stat_statements. Nous avons découvert que si vous avez track_utility = on, alors vos ensembles sont également suivis.

Oui, bien sûr.

Et si vous avez Java Hibernate, qui est aléatoire, alors la table de hachage commence à s'y trouver. Et dès que vous désactivez une application très chargée, vous vous retrouvez avec 50 à 100 groupes. Et tout y est plus ou moins stable. Une façon de lutter contre cela consiste à augmenter pg_stat_statements.max.

Oui, mais il faut savoir à quel point. Et d'une manière ou d'une autre, nous devons garder un œil sur lui. C'est ce que je fais. Autrement dit, j'ai pg_stat_statements.max. Et je vois qu'au moment du cliché je n'avais pas atteint les 70%. D'accord, nous n'avons donc rien perdu. Réinitialisons. Et nous économisons à nouveau. Si le prochain instantané est inférieur à 70, il est fort probable que vous n’ayez plus rien perdu.

Oui. La valeur par défaut est désormais de 5 000. Et cela est suffisant pour de nombreuses personnes.

Généralement oui.

Vidéo:

PS En mon nom personnel, j'ajouterai que si Postgres contient des données confidentielles et qu'elles ne peuvent pas être incluses dans l'environnement de test, alors vous pouvez utiliser Anonymiseur PostgreSQL. Le schéma est approximativement le suivant :

Source: habr.com