Skip to content

Latest commit

 

History

History
175 lines (153 loc) · 6.79 KB

README.fr.md

File metadata and controls

175 lines (153 loc) · 6.79 KB
Raw

postgresqltuner.pl

Build Status

postgresqltuner.pl est un script Perl pour vous aider à analyser la configuration de votre serveur PostgreSQL. Il est inspiré par mysqltuner.pl

Démonstration

Voici un exemple de son exécution :

postgresqltuner.pl version 0.0.8
Connecting to /var/run/postgresql:5432 database testdb with user postgres...
[OK]      User used for report have super rights
=====  OS information  =====
[INFO]    OS: Debian GNU/Linux 8 \n \l
[INFO]    OS total memory: 15.52 GB
[OK]      vm.overcommit_memory is good : no memory overcommitment
[INFO]    Running on physical machine
[INFO]    Currently used I/O scheduler(s) : cfq
=====  General instance informations  =====
-----  Version  -----
[WARN]    You are using version 9.4.8 which is not the latest version
-----  Uptime  -----
[INFO]    Service uptime : 101d 21h 53m 03s
-----  Databases  -----
[INFO]    Database count (except templates): 2
[INFO]    Database list (except templates): postgres testdb
-----  Extensions  -----
[INFO]    Number of activated extensions : 1
[INFO]    Activated extensions : plpgsql
[WARN]    Extensions pg_stat_statements is disabled
-----  Users  -----
[OK]      No user account will expire in less than 7 days
[OK]      No user with password=username
[OK]      Password encryption is enabled
-----  Connection information  -----
[INFO]    max_connections: 100
[INFO]    current used connections: 6 (6.00%)
[INFO]    3 are reserved for super user (3.00%)
[INFO]    Average connection age : 1d 11h 31m 18s
-----  Memory usage  -----
[INFO]    configured work_mem: 4.00 MB
[INFO]    Using an average ratio of work_mem buffers by connection of 150% (use --wmp to change it)
[INFO]    total work_mem (per connection): 6.00 MB
[INFO]    shared_buffers: 128.00 MB
[INFO]    Track activity reserved size : 111.00 KB
[WARN]    maintenance_work_mem is less or equal default value. Increase it to reduce maintenance tasks time
[INFO]    Max memory usage :
                  shared_buffers (128.00 MB)
                + max_connections * work_mem * average_work_mem_buffers_per_connection (100 * 4.00 MB * 150 / 100 = 600.00 MB)
                + autovacuum_max_workers * maintenance_work_mem (3 * 64.00 MB = 192.00 MB)
                + track activity size (111.00 KB)
                = 920.11 MB
[INFO]    effective_cache_size: 4.00 GB
[INFO]    Size of all databases : 41.87 GB
[INFO]    PostgreSQL maximum memory usage: 5.79% of system RAM
[WARN]    Max possible memory usage for PostgreSQL is less than 60% of system total RAM. On a dedicated host you can increase PostgreSQL buffers to optimize performances.
[INFO]    max memory+effective_cache_size is 31.57% of total RAM
[WARN]    Increase shared_buffers and/or effective_cache_size to use more memory
-----  Logs  -----
[OK]      log_hostname is off : no reverse DNS lookup latency
[WARN]    log of long queries is desactivated. It will be more difficult to optimize query performances
[OK]      log_statement=none
-----  Two phase commit  -----
[OK]      Currently no two phase commit transactions
-----  Autovacuum  -----
[OK]      autovacuum is activated.
[INFO]    autovacuum_max_workers: 3
-----  Checkpoint  -----
[WARN]    checkpoint_completion_target(0.5) is low
-----  Disk access  -----
[OK]      fsync is on
[OK]      synchronize_seqscans is on
-----  WAL  -----
[BAD]     The wal_level minimal does not allow PITR backup and recovery
-----  Planner  -----
[OK]      costs settings are defaults
[OK]      all plan features are enabled
=====  Database information for database testdb  =====
-----  Database size  -----
[INFO]    Database testdb total size : 41.86 GB
[INFO]    Database testdb tables size : 13.07 GB (31.22%)
[INFO]    Database testdb indexes size : 28.79 GB (68.78%)
-----  Shared buffer hit rate  -----
[INFO]    shared_buffer_heap_hit_rate: 94.11%
[INFO]    shared_buffer_toast_hit_rate: 23.73%
[INFO]    shared_buffer_tidx_hit_rate: 97.41%
[INFO]    shared_buffer_idx_hit_rate: 96.33%
[WARN]    Shared buffer idx hit rate is quite good. Increase shared_buffer memory to increase hit rate
-----  Indexes  -----
[OK]      No invalid indexes
[WARN]    Some indexes are unused since last statistics: hosts_owner_idx
-----  Procedures  -----
[OK]      No procedures with default costs

=====  Configuration advices  =====
-----  backup  -----
Configure your wal_level to a level which allow PITR backup and recovery
-----  checkpoint  -----
Your checkpoint completion target is too low. Put something nearest from 0.8/0.9 to balance your writes better during the checkpoint interval
-----  extension  -----
Enable pg_stat_statements to collect statistics on all queries (not only queries longer than log_min_duration_statement in logs)
-----  index  -----
You have unused indexes in the database since last statistics. Please remove them if they are never use

Utilisation

Installation

Pré-requis : module Perl DBD::Pg

  • Sur Debian et dérivées :
apt-get install libdbd-pg-perl
  • Sur Fedora et dérivées :
yum install perl-DBD-Pg

Téléchargement :

wget -O postgresqltuner.pl postgresqltuner.pl
wget -O postgresqltuner.pl https://postgresqltuner.pl
curl -o postgresqltuner.pl postgresqltuner.pl
curl -o postgresqltuner.pl https://postgresqltuner.pl

Droits :

chmod +x postgresqltuner.pl

Lancement :

  • Connexion par le réseau :
postgresqltuner.pl --host=dbhost --database=testdb --user=username --password=qwerty
  • Connexion via le socket unix en tant qu'utilisateur système postgres :
postgres$ postgresqltuner.pl --host=/var/run/postgresql  # PostgreSQL socket directory

Avec Docker

  • Connexion par le réseau :
docker run -it --rm jfcoz/postgresqltuner --host=dbhost --user=username --password=pass --database=testdb
  • Connexion par le réseau avec accès SSH :
docker run -it --rm -v $HOME/.ssh:/root/.ssh jfcoz/postgresqltuner --host=dbhost --user=username --password=pass --database=testdb
  • Connexion via un lien Docker :
docker run -it --rm --link your-postgresql-container:dbhost jfcoz/postgresqltuner --host=dbhost --user=username --password=pass --database=testdb

SSH

En cas d'utilisation à distance, postgresqltuner.pl utilise SSH pour se connecter au serveur et collecter quelques informations sur l'OS. Il faut pour ça configurer SSH pour que postgresqltuner.pl puisse se connecter au serveur avec une authentification par clef.

Options

  • Nombre moyen de buffers work_mem par connection :

Une requête peut utiliser plusieurs fois simultanément des buffers work_mem, en fonction de la complexité de la requête. Vous pouvez configurer le nombre moyen de buffers work_mem par connection (en pourcentage) avec l'option :

--wmp 300

La valeur par défaut est 150%