Changes between Version 80 and Version 81 of InstallationGuidelines/PostgreSQL
- Timestamp:
- 02/13/18 14:30:35 (7 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
InstallationGuidelines/PostgreSQL
v80 v81 47 47 == Tuning == 48 48 Adjust postgresql.conf for performance relative to resources available 49 50 For 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 {{{ 53 pg512 54 pg1024 55 }}} 56 57 For larger servers, then more manual tuning is useful to be able to fully utilise the hardware. 58 59 Some 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) 49 61 * 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 50 63 * 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 67 Manual adjustment of available settings is discussed here: 52 68 {{{ 53 69 vim /etc/sysctl.conf … … 62 78 sysctl -w kernel.shmall=2097152 63 79 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 80 vim /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 86 max_connections = 12 87 88 # shared_buffers = 128MB 71 89 # Ideal is if all frequently accessed indexes and table rows can fit in here - this is often unrealistic 72 90 # setting too high relative to system resources can be detrimental to the system. … … 74 92 # Values suggested are quite conservative, so you can push them up a bit if there are resources available for it 75 93 # 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: 95 shared_buffers = 128MB 96 # 1024 Mb system suggest starting at: 97 shared_buffers = 256MB 98 # 8096Mb system suggest starting at: 99 #shared_buffers = 1024MB 100 101 # effective_cache_size = 4GB 82 102 # 75% of available memory 83 103 # 512Mb system suggest starting at: … … 85 105 # 1024Mb system suggest starting at: 86 106 #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 89 111 # This is a per sort setting, so setting this too high with a high number of max_connections can be bad. 90 112 # If the database load turns out to have a lot of sorting and not a lot of separate connections, you should increase this 91 113 # and drop the max_connections. 92 # 512Mb system suggest starting at :93 work_mem = 2MB114 # 512Mb system suggest starting at the default: 115 work_mem = 4MB 94 116 # 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 97 122 # 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 130 To 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 {{{ 135 apt-get install -y pgtop 136 su postgres 137 pg_top 138 }}} 103 139 == Install Support Tools == 104 140 (Optional) Install the Admin Pack for enhanced intrumentation: