En prenant comme base l'article de Peter Zaitsev sur les goulots d'étranglement des performances de MySQL , je veux parler un peu de PostgreSQL.
Les frameworks ORM sont souvent utilisés pour travailler avec PostgreSQL ces jours-ci. Ils fonctionnent généralement bien, mais avec le temps, la charge augmente et il devient nécessaire de régler le serveur de base de données. Aussi fiable que soit PostgreSQL, il peut ralentir à mesure que le trafic augmente.
Il existe de nombreuses façons d'éliminer les goulots d'étranglement des performances, mais dans cet article, nous nous concentrerons sur les éléments suivants:
- Paramètres du serveur
- Gestion des connexions
- Réglage de l'autovacuum
- Réglage supplémentaire de l'autovacuum
- Tables de ballonnements (ballonnement)
- Points chauds dans les données
- Serveurs d'applications
- Réplication
- Environnement serveur
À propos des "catégories" et de "l'impact potentiel"
La «complexité» fait référence à la facilité de mise en œuvre de la solution proposée. Et «l'impact potentiel» donne une indication du degré d'amélioration des performances du système. Cependant, en raison de l'âge du système, de son type, de sa dette technique, etc. décrire avec précision la complexité et l'impact peut être problématique. Après tout, dans les situations difficiles, le choix final vous appartient toujours.
Catégories:
- Complexité
- Faible
- Moyenne
- Haute
- Faible-moyen-élevé
- Impact potentiel
- Faible
- La moyenne
- Haute
- Faible-moyen-élevé
Paramètres du serveur
Difficulté: faible.
Impact potentiel: élevé.
Il n'y a pas si longtemps, les versions actuelles de postgres pouvaient fonctionner sur i386. Les paramètres par défaut ont été modifiés depuis, mais ils sont toujours configurés pour utiliser le moins de ressources possible.
Ces paramètres sont très faciles à modifier et sont généralement configurés lors de l'installation initiale. Des valeurs incorrectes de ces paramètres peuvent conduire à une utilisation élevée du processeur et des E / S:
- Paramètre effective_cache_size ~ 50 à 75%
- Paramètre shared_buffers ~ 1/4 - 1/3 de la quantité de RAM
- Paramètre work_mem ~ 10 Mo
La valeur recommandée pour effective_cache_size, bien que typique, peut être calculée avec plus de précision si nous nous référons à «top» - gratuit + mis en cache .
Le calcul de la valeur de shared_buffers est un casse-tête intéressant. Vous pouvez le regarder sous deux angles: si vous avez une petite base de données, vous pouvez définir la valeur de shared_buffers suffisamment grande pour contenir toute la base de données en RAM. D'autre part, vous pouvez configurer le chargement en mémoire uniquement des tables et des index fréquemment utilisés (rappelez-vous le 80/20). Auparavant, il était recommandé de définir la valeur sur 1/3 de la quantité de RAM, mais au fil du temps, à mesure que la quantité de mémoire augmentait, elle était réduite à 1/4. Si peu de mémoire est allouée, les E / S et la charge du processeur augmenteront. Une allocation de mémoire trop importante sera indiquée en atteignant le plateau de charge du processeur et des E / S.
Un autre facteur à considérer est le cache du système d'exploitation . Avec suffisamment de RAM, Linux mettra en cache les tables et les index en mémoire et, en fonction de la configuration, peut faire croire à PostgreSQL qu'il lit des données à partir du disque plutôt que de la RAM. La même page se trouve à la fois dans le tampon postgres et dans le cache du système d'exploitation, et c'est une raison pour ne pas rendre shared_buffers très volumineux. Avec l'extension pg_buffercachevous pouvez voir l'utilisation du cache en temps réel.
Le paramètre work_mem spécifie la quantité de mémoire utilisée pour les opérations de tri. La définition de cette valeur trop faible garantit des performances médiocres, car le tri sera effectué à l'aide de fichiers temporaires sur le disque. D'autre part, bien que la définition d'une valeur élevée n'affecte pas les performances, avec un grand nombre de connexions, il y a un risque de manquer de RAM. En analysant la mémoire utilisée par toutes les demandes et sessions, vous pouvez calculer la valeur requise.
En utilisant EXPLAIN ANALYZE, vous pouvez voir comment les opérations de tri sont effectuées et, en modifiant la valeur de la session, déterminer quand le vidage sur le disque commence.
Vous pouvez également utiliser des benchmarks systèmes.
Gestion des connexions
Difficulté: faible.
Impact potentiel: faible-moyen-élevé
Une charge élevée est généralement associée à une augmentation des sessions client par unité de temps. Un trop grand nombre d'entre eux peut bloquer des processus, entraîner des retards ou même entraîner des erreurs.
La solution simple consiste à augmenter le nombre maximum de connexions simultanées:
# postgresql.conf: default is set to 100<br />max_connections
Mais une approche plus efficace est la mise en commun de connexions . Il existe de nombreuses solutions, mais la plus populaire est pgbouncer . PgBouncer peut gérer les connexions en utilisant l'un des trois modes suivants:
- (session pooling). . , . , . .
- (transaction pooling). . PgBouncer , , .
- (statement pooling). . . , .
Vous devez également faire attention à Secure Socket Layer (SSL). Lorsqu'elles sont activées, les connexions utiliseront SSL par défaut, ce qui augmentera la charge du processeur par rapport aux connexions non chiffrées. Pour les clients standard, vous pouvez configurer l'authentification basée sur l'hôte sans SSL (
pg_hba.conf) et utiliser SSL pour les tâches administratives ou pour la réplication en continu.
Réglage de l'autovacuum
Difficulté: moyenne.
Impact potentiel: faible à moyen.
Le contrôle d'accès concurrentiel multi-version est l'un des principes fondamentaux qui font de PostgreSQL une solution de base de données si populaire. Cependant, l'un des problèmes ennuyeux est que pour chaque enregistrement modifié ou supprimé, des copies inutilisées sont créées, qui doivent finalement être éliminées. Un processus d'autovacuum mal configuré peut dégrader les performances. De plus, plus le serveur est chargé, plus le problème se manifeste.
Les paramètres suivants sont utilisés pour contrôler le démon autovacuum:
- autovacuum_max_workers. ( ). , . . . .
- maintenance_work_mem. , . , . , .
- autovacuum_freeze_max_age TXID WRAPAROUND. , , . , , , . , txid, . / txid pg_stat_activity WRAPAROUND.
Méfiez-vous de la surcharge de la RAM et du processeur. Plus la valeur initialement définie est élevée, plus le risque d'épuisement des ressources est grand lorsque la charge sur le système augmente. Si la valeur est trop élevée, les performances peuvent chuter considérablement lorsqu'un certain niveau de charge est dépassé.
Similaire au calcul de work_mem , cette valeur peut être calculée de manière arithmétique ou comparée pour obtenir des valeurs optimales .
Réglage supplémentaire de l'autovacuum
Difficulté: élevée.
Impact potentiel: élevé.
Cette méthode, en raison de sa complexité, ne doit être utilisée que lorsque les performances du système sont déjà à la limite des limites physiques de l'hôte et que cela est vraiment devenu un problème.
Les options d'exécution de l'autovacuum sont configurées dans
postgresql.conf. Malheureusement, il n’existe pas de solution universelle qui fonctionnera dans n’importe quel système à forte charge.
Options de stockage pour les tables . Souvent, dans une base de données, une partie importante de la charge ne concerne que quelques tables. La personnalisation des paramètres d'autovacuum pour une table est un excellent moyen d'éviter d'avoir à démarrer manuellement VACUUM, ce qui peut affecter considérablement le système.
Vous pouvez personnaliser les tableaux à l'aide de la commande :
ALTER TABLE .. SET STORAGE_PARAMETERTables de ballonnements (ballonnement)
Difficulté: faible.
Impact potentiel: moyen-élevé.
Au fil du temps, les performances du système peuvent se dégrader en raison de politiques de nettoyage inappropriées en raison d'une surcharge excessive des tables. Ainsi, même la configuration du démon autovacuum et le démarrage manuel de VACUUM ne résout pas le problème. Dans ces cas, l'extension pg_repack vient à la rescousse .
En utilisant l'extension pg_repack , vous pouvez reconstruire et réorganiser les tables et les index en production
Points chauds dans les données
Difficulté: élevée.
Impact potentiel: faible-moyen-élevé.
Comme avec MySQL , PostgreSQL s'appuie sur vos flux de données pour se débarrasser des hotspots et peut même changer l'architecture de votre système.
Tout d'abord, vous devez faire attention aux points suivants:
- Indices . Assurez-vous qu'il existe des index sur les colonnes recherchées. Vous pouvez utiliser des catalogues et des vues système pour surveiller et vérifier que les requêtes utilisent des index. Utilisez les extensions pg_stat_statement et pgbadger pour analyser les performances des requêtes.
- Heap Only Tuples (HOT) . Il peut y avoir trop d'index. Vous pouvez réduire le gonflement potentiel et réduire la taille de la table en supprimant les index inutilisés.
- . , , . , , , . , . , , .
- . postgres. , .
- . , . . , !
Difficulté: faible.
Impact potentiel: élevé.
Évitez d'exécuter des applications (PHP, Java et Python) et postgres sur le même hôte. Soyez prudent avec les applications dans ces langages, car elles peuvent consommer de grandes quantités de RAM, en particulier le garbage collector, ce qui peut entraîner une concurrence avec les systèmes de base de données pour les ressources et réduire les performances globales.
Réplication
Difficulté: faible.
Impact potentiel: élevé.
Réplication synchrone et asynchrone. Les versions récentes de postgres prennent en charge la réplication logique et en continu en modes synchrone et asynchrone. Alors que le mode de réplication par défaut est asynchrone, vous devez tenir compte des implications de l'utilisation de la réplication synchrone, en particulier sur les réseaux avec une latence importante.
Environnement serveur
Dernier point mais non le moindre, il s'agit d'une simple augmentation de la capacité de l'hôte. Jetons un coup d'œil à ce que chacune des ressources affecte en termes de performances PostgreSQL:
- . , . . , , -.
- . , , . .
- . .
- -, ,
- .
- . , .
- .
- . .
- WAL-, , , . , (log shipping) , , .
: