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


Ignore:
Timestamp:
08/31/10 14:42:40 (11 years ago)
Author:
Fran Boon
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • GIS/InstallationGuidelines/Linux

    v5 v6  
    4444(Don't do remote access as 'postgres' - to do so means can't simply access psql via 'su postgres')
    4545
    46  * Adjust postgresql.conf for performance relative to resources available
    47 {{{
    48 
    49 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
    50  
    51 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.
    52 
    53 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.
    54 
    55 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.
    56 
    57 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.
    58 
    59 References:
    60 http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
     46 * Adjust postgresql.conf for performance relative to resources available (http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server)
     47{{{
     48vim /etc/postgresql/8.3/main/postgresql.conf
     49
     50
     51# This default is probably ok
     52# adjust upward _or_ add a connection pooling layer like pgpool if running out of connections for clients
     53max_connections = 100
     54
     55# shared_buffers = 24MB
     56# Ideal is if all frequently accessed indexes and table rows can fit in here - this is often unrealistic
     57# setting too high relative to system resources can be detrimental to the system.
     58# 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).
     59# Values suggested are quite conservative, so you can push them up a bit if there are resources available for it
     60# but absolutely no more than 1/3 available memory.
     61# 512Mb system suggest starting at:
     62shared_buffers = 56MB
     63# 1024Mb system suggest starting at:
     64#shared_buffers = 160MB
     65
     66# effective_cache_size = 128MB
     67# 512Mb system suggest starting at:
     68effective_cache_size = 256MB
     69# 1024Mb system suggest starting at:
     70#effective_cache_size = 512MB
     71
     72
     73#work_mem = 1MB
     74# This is a per sort setting, so setting this too high with a high number of max_connections can be bad.
     75# If the database load turns out to have a lot of sorting and not a lot of separate connections, you should increase this
     76# and drop the max_connections.
     77# 512Mb system suggest starting at:
     78work_mem = 2MB
     79# 1024Mb system suggest starting at:
     80#work_mem = 4MB
     81
     82# As you get more memory in the system, moving this up can be quite useful if you notice vacuum processes taking forever.
     83maintenance_work_mem = 16MB
     84
     85/etc/init.d/postgresql-8.3 restart
    6186}}}
    6287