Changes between Version 5 and Version 6 of GIS/InstallationGuidelines/Linux

Timestamp:

08/31/10 14:42:40 (14 years ago)

Author:

Fran Boon

Comment:

--

GIS/InstallationGuidelines/Linux

@@ -44 +44 @@
 (Don't do remote access as 'postgres' - to do so means can't simply access psql via 'su postgres')
 
- * Adjust postgresql.conf for performance relative to resources available
-{{{
-
-max_connections -- default is 100, this is probably ok, adjust upward _or_ add a connection pooling layer like pgpool if running out of connections for clients
- 
-shared_buffers -- default is 24MB, amount of shared memory that is held across all connections for caching frequently requested data.  The ideal is if all frequently accessed indexes and table rows can fit in here -- this is often unrealistic and setting too high relative to system resources can be detrimental to the system. At the high end this should be no more than 1/4 to 1/3 the available memory (ie that which is not being used by the OS or other processes). For a 512MB memory system, start at 56MB. For a 1G memory system start at 160MB. Those should both be quite conservative, so if you notice the database is slow and there are plenty of system resources at full load move them up a bit, but absolutely no more than 1/3 available memory.
-
-effective_cache_size -- default 128MB, this is used by the query optimizer to decide when to use an index vs. a sequence scan -- this does not control how much memory is used it is simply used to help optimize how the database is going to execute queries. 1/2 total memory is a conservative starting point.
-
-work_mem -- default 1MB, this is a per sort setting, so setting this too high with a high number of max_connections can be bad.  If the database load turns out to have a lot of sorting and not a lot of separate connections, you should increase this and drop the max_connections. Bump this up to 2MB to start with on a 512M system, 4MB on a 1G system.
-
-maintenance_work_mem -- default 16MB, memory available to the vacuum process. As you get more memory in the system, moving this up can be quite useful if you notice vacuum processes taking forever.
-
-References:
-http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
+ * Adjust postgresql.conf for performance relative to resources available (http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server)
+{{{
+vim /etc/postgresql/8.3/main/postgresql.conf
+
+
+# This default is probably ok
+# adjust upward _or_ add a connection pooling layer like pgpool if running out of connections for clients
+max_connections = 100
+
+# shared_buffers = 24MB
+# Ideal is if all frequently accessed indexes and table rows can fit in here - this is often unrealistic
+# setting too high relative to system resources can be detrimental to the system.
+# At the high end this should be no more than 1/4 to 1/3 the available memory (ie that which is not being used by the OS or other processes).
+# Values suggested are quite conservative, so you can push them up a bit if there are resources available for it
+# but absolutely no more than 1/3 available memory.
+# 512Mb system suggest starting at:
+shared_buffers = 56MB
+# 1024Mb system suggest starting at:
+#shared_buffers = 160MB
+
+# effective_cache_size = 128MB
+# 512Mb system suggest starting at:
+effective_cache_size = 256MB
+# 1024Mb system suggest starting at:
+#effective_cache_size = 512MB
+
+
+#work_mem = 1MB
+# This is a per sort setting, so setting this too high with a high number of max_connections can be bad.
+# If the database load turns out to have a lot of sorting and not a lot of separate connections, you should increase this
+# and drop the max_connections.
+# 512Mb system suggest starting at:
+work_mem = 2MB
+# 1024Mb system suggest starting at:
+#work_mem = 4MB
+
+# As you get more memory in the system, moving this up can be quite useful if you notice vacuum processes taking forever.
+maintenance_work_mem = 16MB
+
+/etc/init.d/postgresql-8.3 restart
 }}}