Changes between Version 80 and Version 81 of InstallationGuidelines/PostgreSQL


Ignore:
Timestamp:
02/13/18 14:30:35 (4 years ago)
Author:
Fran Boon
Comment:

Update defaults for PG 9.6 + recommendations from there

Legend:

Unmodified
Added
Removed
Modified
  • InstallationGuidelines/PostgreSQL

    v80 v81  
    4747== Tuning ==
    4848Adjust postgresql.conf for performance relative to resources available
     49
     50For small/medium sized servers (the majority of Sahana Eden installs) then we provide 2 scripts to configure appropriately depending on the amount of RAM available:
     51* https://github.com/sahana/eden_deploy/blob/master/manual/install-eden-cherokee-postgis.sh#L745
     52{{{
     53pg512
     54pg1024
     55}}}
     56
     57For larger servers, then more manual tuning is useful to be able to fully utilise the hardware.
     58
     59Some relevant URLs:
     60 * http://pgtune.leopard.in.ua (But the RAM we have available is normally shared by UWSGI, so we should be a little more conservative)
    4961 * http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
     62 * https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_Postgres_Advanced_Server_Guide.1.12.html
    5063 * http://linuxfinances.info/info/quickstart.html
    51 
     64 * https://blog.2ndquadrant.com/basics-of-tuning-checkpoints/
     65 * https://www.depesz.com/2010/11/03/checkpoint_completion_target/
     66
     67Manual adjustment of available settings is discussed here:
    5268{{{
    5369vim /etc/sysctl.conf
     
    6278sysctl -w kernel.shmall=2097152
    6379
    64 vim /etc/postgresql/8.4/main/postgresql.conf
    65 
    66 # This default is probably ok
    67 # adjust upward _or_ add a connection pooling layer like pgpool if running out of connections for clients
    68 max_connections = 100
    69 
    70 # shared_buffers = 24MB
     80vim /etc/postgresql/9.6/main/postgresql.conf
     81
     82# max_connections = 100
     83# This default is high for typical Sahana deployments with 4 UWSGI workers for each of production, test and demo
     84# - NB Typically we want workers limited to nproc + 1 (Number of CPUs: nproc or lscpu)
     85# If running out of connections for clients, adjust upward _or_ add a connection pooling layer like pgpool
     86max_connections = 12
     87
     88# shared_buffers = 128MB
    7189# Ideal is if all frequently accessed indexes and table rows can fit in here - this is often unrealistic
    7290# setting too high relative to system resources can be detrimental to the system.
     
    7492# Values suggested are quite conservative, so you can push them up a bit if there are resources available for it
    7593# but absolutely no more than 1/3 available memory.
    76 # 512Mb system suggest starting at:
    77 shared_buffers = 56MB
    78 # 1024Mb system suggest starting at:
    79 #shared_buffers = 160MB
    80 
    81 # effective_cache_size = 128MB
     94# 512Mb/1024 Mb system suggest starting at default:
     95shared_buffers = 128MB
     96# 1024 Mb system suggest starting at:
     97shared_buffers = 256MB
     98# 8096Mb system suggest starting at:
     99#shared_buffers = 1024MB
     100
     101# effective_cache_size = 4GB
    82102# 75% of available memory
    83103# 512Mb system suggest starting at:
     
    85105# 1024Mb system suggest starting at:
    86106#effective_cache_size = 512MB
    87 
    88 #work_mem = 1MB
     107# 8096Mb system suggest starting at default:
     108#effective_cache_size = 4GB
     109
     110#work_mem = 4MB
    89111# This is a per sort setting, so setting this too high with a high number of max_connections can be bad.
    90112# If the database load turns out to have a lot of sorting and not a lot of separate connections, you should increase this
    91113# and drop the max_connections.
    92 # 512Mb system suggest starting at:
    93 work_mem = 2MB
     114# 512Mb system suggest starting at the default:
     115work_mem = 4MB
    94116# 1024Mb system suggest starting at:
    95 #work_mem = 4MB
    96 
     117#work_mem = 8MB
     118# 8096Mb system suggest starting at:
     119#work_mem = 32MB
     120
     121# maintenance_work_mem = 64MB
    97122# As you get more memory in the system, moving this up can be quite useful if you notice vacuum processes taking forever.
    98 maintenance_work_mem = 16MB
    99 
    100 /etc/init.d/postgresql restart
    101 }}}
    102 
     123# 512Mb/1024 Mb system suggest starting at the default
     124# 8096Mb system suggest starting at:
     125#maintenance_work_mem = 128MB
     126
     127/etc/init.d/postgresql restart
     128}}}
     129
     130To check the impact of your settings:
     131 * https://github.com/jfcoz/postgresqltuner
     132 * https://www.postgresql.org/docs/9.3/static/monitoring-stats.html
     133 * pgtop:
     134{{{
     135apt-get install -y pgtop
     136su postgres
     137pg_top
     138}}}
    103139== Install Support Tools ==
    104140(Optional) Install the Admin Pack for enhanced intrumentation: