Changes between Version 6 and Version 7 of jazz/PostgreSQL

Timestamp:

Mar 26, 2018, 11:02:10 AM (7 years ago)

Author:

jazz

Comment:

--

jazz/PostgreSQL

@@ -112 +112 @@
 # Please install theses Perl modules
 }}}
+  * 範例輸出
+{{{
+~$ ./postgresqltuner.pl 
+postgresqltuner.pl version 1.0.0
+Connecting to /var/run/postgresql:5432 database template1 with user postgres...
+[OK]      User used for report have super rights
+=====  OS information  =====
+[INFO]    OS: \SKernel \r on an \m
+[INFO]    OS total memory: 11.73 GB
+[BAD]     Memory overcommitment is allowed on the system. This can lead to OOM Killer killing some PostgreSQL process, which will cause a PostgreSQL server restart (crash recovery)
+[INFO]    sysctl vm.overcommit_ratio=50
+[BAD]     vm.overcommit_memory is too small, you will not be able to use more than 50*RAM+SWAP for applications
+[INFO]    Running in VMware hypervisor
+[INFO]    Currently used I/O scheduler(s) : cfq
+[BAD]     CFQ scheduler is bad on virtual machines (hypervisor and/or storage is already dooing I/O scheduling)
+=====  General instance informations  =====
+-----  Version  -----
+[WARN]    You are using version 9.4.11 which is not the latest version
+-----  Uptime  -----
+[INFO]    Service uptime :  10m 42s
+[WARN]    Uptime is less than 1 day. postgresqltuner.pl result may not be accurate
+-----  Databases  -----
+[INFO]    Database count (except templates): 7
+[INFO]    Database list (except templates): postgres ssp_jenkins ssp_dev ssp_stage ads_test ads_dev ads_beta
+-----  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
+[WARN]    some users account have the username as password : ssp_jenkins,ssp_dev,ssp_stage,cmax_dev,cmax_stage,ads_test,ads_dev,ads_beta
+[OK]      Password encryption is enabled
+-----  Connection information  -----
+[INFO]    max_connections: 300
+[INFO]    current used connections: 48 (16.00%)
+[INFO]    3 are reserved for super user (1.00%)
+[INFO]    Average connection age :  06m 05s
+[WARN]    Average connection age is less than 10 minutes. Use a connection pooler to limit new connection/seconds
+-----  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 : 311.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 (300 * 4.00 MB * 150 / 100 = 1.76 GB)
+                + autovacuum_max_workers * maintenance_work_mem (3 * 64.00 MB = 192.00 MB)
+                + track activity size (311.00 KB)
+                = 2.07 GB
+[INFO]    effective_cache_size: 4.00 GB
+[INFO]    Size of all databases : 24.46 GB
+[INFO]    PostgreSQL maximum memory usage: 17.65% 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 51.74% 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  -----
+-----  Planner  -----
+[OK]      costs settings are defaults
+[OK]      all plan features are enabled
+=====  Database information for database template1  =====
+-----  Database size  -----
+[INFO]    Database template1 total size : 7.12 MB
+[INFO]    Database template1 tables size : 4.50 MB (63.16%)
+[INFO]    Database template1 indexes size : 2.62 MB (36.84%)
+-----  Tablespace location  -----
+[OK]      No tablespace in PGDATA
+-----  Shared buffer hit rate  -----
+[INFO]    shared_buffer_heap_hit_rate: 98.12%
+[INFO]    shared_buffer_toast_hit_rate: 0.00%
+[INFO]    shared_buffer_tidx_hit_rate: 0.00%
+[INFO]    shared_buffer_idx_hit_rate: 98.77%
+[OK]      Shared buffer idx hit rate is very good
+-----  Indexes  -----
+[OK]      No invalid indexes
+[OK]      No unused indexes
+-----  Procedures  -----
+[OK]      No procedures with default costs
+
+=====  Configuration advices  =====
+-----  checkpoint  -----
+[MEDIUM] 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  -----
+[LOW] Enable pg_stat_statements to collect statistics on all queries (not only queries longer than log_min_duration_statement in logs)
+-----  sysctl  -----
+[URGENT] set vm.overcommit_memory=2 in /etc/sysctl.conf and run sysctl -p to reload it. This will disable memory overcommitment and avoid postgresql killed by OOM killer.
+-----  system  -----
+[URGENT] Configure your system to use noop or deadline io scheduler when on virtual machines :
+echo deadline > /sys/block/sdX/queue/scheduler
+update your kernel parameters line with elevator=deadline to keep this parameter at next reboot
+-----  version  -----
+[LOW] Upgrade to last version
+}}}