Annonce
Pages: 1
- Sujet précédent - Postgresql/postgis: créer un graphique de l'historique des connections - Sujet suivant
#1 Thu 20 February 2014 16:03
- Anaïs Just
- Participant actif
- Lieu: Montreuil
- Date d'inscription: 10 Dec 2007
- Messages: 131
Postgresql/postgis: créer un graphique de l'historique des connections
Bonjour à tous,
Je cherche à créer un graphique de l'historique des connections à une base de données postgresql qui est couplée à une application carto en ligne.
L'appli pointe directement sur la base (quand on clique sur un bouton, une requête est envoyée à la base).
Je sais qu'il est possible d'accéder à des tables résumées de statistiques de connections à postgresql via la commande
pour une base particulière:
select * from pg_stat_activity;
J'ai l'impression que que cette vue ne garde en mémoire que les connections des 2 dernières heures (environ) ou alors des connections instantanées (je n'ai pas compris).
Je ne sais pas comment cette table est générée.
En réutilisant la fonction qui sert à la générer il devrait bien y avoir moyen de paramétrer ce temps?
Mon idée c'est de pouvoir par exemple de produire une table avec le nombre de connections de la dernière semaine ou du dernier mois.
Ensuite ce sera facile de faire un graphe avec le nombre de connections par jour pour une base de donnée précise.
Quelqu'un sait-il quelle procédure je peux utiliser pour faire une table pg_stat_activity qui remonte un peu plus loin dans le temps?
Ou alors existe-t-il des utilitaires pour ça?
Sous linux on peut utiliser top, il existe aussi pgtop mais dans tous les cas j'ai l'impression que c'est de l'instantané.
Tout ceci m'aidera à mieux paramétrer mon postgresql.conf (max_connection, work_mem etc) en ayant une meilleure connaissance de l'activité réelle sur la base.
Merci beaucoup pour l'aide que vous pourrez m'apporter 
Hors ligne
#2 Thu 20 February 2014 16:45
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: Postgresql/postgis: créer un graphique de l'historique des connections
Bonjour,
pg_stat_activity est une vue qui donne des informations sur l'activité en cours sur la base: les requetes qui sont actives, les connexions qui ont lancé ces requetes. Ca ne garde pas la trace des anciennes connexions. Ca permet entre autre d'avoir l'identifiant du processus d'une requete, pour l'annuler si elle prend trop de temps ou ne finit jamais.
Pour pister les connexions, le plus simple est d'analyser les logs de Postgresql, en parametrant le log les connexions (http://www.postgresql.org/docs/current/ … GGING-WHAT)
Il y a des outils qui peuvent analyser ces logs pour vous et générer les graphes, comme PgBadger par exemple.
Sinon, une commande linux pour parser le fichier de log et ne garder que les connexions avec leur date devrait permettre de générer un graphe.
Concernant la configuration de Postgresql pour le nombre de connexions et le work_mem:
Votre application ne devrait pas créer une connexion a chaque requete, mais plutot utiliser un pool de connexion: un programme qui gère un nombre limité de connexions et les réutilise d'une demande à l'autre.
Sinon, vous risquez de planter la base si beaucoup de clients se connectent en meme temps. Il n'est pas conseillé non plus d'autoriser un très grand nombre de connexions dans postgresql: ce n'est pas efficace et risque de ralentir l'ordinateur.
Suivant le langage utilisé pour votre application, il existe des pools efficaces pour postgresql (pour java, python, js, php, .net, etc.).
Le paramètre work_mem se règle aussi (et surtout) en fonction des requètes: cette mémoire est utilisée pour les opérations de tri et de classement (ORDER BY, par exemple).
Si les données sont très volumineuses et que les requetes effectuent des tris, il faut augmenter un peu ce paramètre, en faisant attention que cette mémoire est prise pour chaque requete et chaque connexion (d'ou l'intéret encore de limiter le nombre de connexion au niveau de PG et d'utiliser un pool pour etre sur que l'application n'ouvrira jamais plus de connexions que la valeur configurée.
Nicolas
Hors ligne
#3 Fri 21 February 2014 11:58
- Anaïs Just
- Participant actif
- Lieu: Montreuil
- Date d'inscription: 10 Dec 2007
- Messages: 131
Re: Postgresql/postgis: créer un graphique de l'historique des connections
haaaa merci.... c'est top ça comme retour.
Je vais tester tout ça.
L'appli a été développée sous symfony. Du coup avec doctrine si j'utilise les options persistent: true et pooled: true dans le fichier de configuration de la base de donnée config.yml, ça devrait le faire.
Connaissez-vous la différence entre ces deux options?
"Il n'est pas conseillé non plus d'autoriser un très grand nombre de connexions dans postgresql: ce n'est pas efficace et risque de ralentir l'ordinateur."
Passer de 100 à 1000 connections ralenti de beaucoup ma machines: j'ai vu que c'était 400bytes par connexion supplémentaire donc 36000 octets de plus par connexion= 0.34MB en plus. Je n'ai aucune idée si ça représente beaucoup de différence en temps d'exécution.
Le serveur que j'utilise est un serveur dédié OVH avec 32GB de RAM (mais il n'est pas uniquement dédié à postgresql) du coup j'ai aussi augmenté le shared_buffer (à 3072MB) en augmentant au passage le kernel.shmmax dans le sysctl.conf(l'appli interroge quelques millions de données; les opérations sur les données ne sont pas très compliquées mais il y a quand même des jointures).
J'ai augmenté la work_mem à 1024MB, la maintenance_workmem à 2048MB, le wal buffer à 32MB,etc
Cependant en utilisant l'outil pgtune avec un
Code:
sudo pgtune -i /etc/postgresql/9.1/main/postgresql.conf -o /etc/postgresql/9.1/main/postgresql2.conf
je vois que l'outil me suggère les paramétrages suivants:
Code:
default_statistics_target = 50 # pgtune wizard 2014-02-20 maintenance_work_mem = 1GB # pgtune wizard 2014-02-20 constraint_exclusion = on # pgtune wizard 2014-02-20 effective_cache_size = 22GB # pgtune wizard 2014-02-20 work_mem = 192MB # pgtune wizard 2014-02-20 wal_buffers = 8MB # pgtune wizard 2014-02-20 checkpoint_segments = 16 # pgtune wizard 2014-02-20 shared_buffers = 7680MB # pgtune wizard 2014-02-20 max_connections = 80 # pgtune wizard 2014-02-20
quelle confiance puis-je avoir en cet outil?
Ha oui je précise que je n'ai pas moi-même développé l'outil, j'ai plutôt un profil de géomaticien, admin de bases de données donc,pour le moment, je ne comprends pas encore toutes les subtilités des paramétrages de mémoire.
Merci
Dernière modification par Anaïs Just (Fri 21 February 2014 12:00)
Hors ligne
#4 Fri 21 February 2014 13:06
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: Postgresql/postgis: créer un graphique de l'historique des connections
Bonjour,
Oui, ces options dans Symfony sont faites pour ca. Attention cependant, ce n'est pas vraiment symfony qui gère cela, mais le framework ORM (Object-Relational Mapping) utilisé par Symfony: soit Doctrine, soit Propel.
pooled=true: l'ORM va utiliser un pool de connexions (le nombre max de connexions du pool est à configurer)
persistent=true: les connexions vont rester ouvertes et ne seront pas fermées si elles ne sont pas utilisées. Surtout utile pour des bases où le temps de connexion est tres long (donc couteux), comme Oracle. Dans PG, la connexion est tres rapide a initier.
Concernant le nombre de connexions: non seulement une connexion prend de la mémoire (peu, cependant), mais surtout elle crée un nouveau processus. Encore une fois, tout dépend de l'application et du serveur: avec 32 Gb de ram, ca doit pouvoir le faire 
Avec un si gros serveur, vous pouvez donner de la mémoire a postgresql. 3Gb sur 32, ca parait peu, sauf s'il y a d'autres applications gourmandes en mémoire sur le serveur.
Pour le coup, 1024 de work_mem, c'est beaucoup il me semble: cette mémoire est prise pour chaque requete, et meme chaque opération de classement dans une requete: Ainsi, si une requete utilise 3 classements, et que 10 personnes exécutent cette requete, 30 x 1024 Mb seront pris par le système: ca va le faire planter.
PgTune est bien pratique pour estimer les parametres de reglages sur une machine données.
Il vous "conseille", dans votre cas, d'avoir un shared_buffer plus élevé, mais surtout un work_mem plus faible (192 Mo).
Ce que ne sait pas PgTune, c'est le type de requetes qui vont etre faites et ca peut faire varier un peu les paramètres qu'il indique.
Nicolas
Hors ligne
#5 Mon 24 February 2014 10:00
- Anaïs Just
- Participant actif
- Lieu: Montreuil
- Date d'inscription: 10 Dec 2007
- Messages: 131
Re: Postgresql/postgis: créer un graphique de l'historique des connections
D'accord, merci pour tous ces éclaircissements.
En effet c'est avec Doctrine que je travaille (d'ailleurs les options de pool étaient légèrement différentes entre propel et doctrine) Je vais re-paramétrer la config en fonction des "conseils" de pgtunes et des vôtres.
Pour le paramétrage du nombre max de connexions du pool qu'est ce qui est préconisé?
Hors ligne
Pages: 1
- Sujet précédent - Postgresql/postgis: créer un graphique de l'historique des connections - Sujet suivant
