[postgis-users] Optimizing postgresql.conf for large postgisdatasets

Tue Feb 17 15:58:26 PST 2004

Your mileage may vary as we do mostly reads from out database rather than lots of OLTP. These could perhaps be improved on but initial testing showed we were getting reasonable throughput on our heavy duty load testing -- not as good as Informix but good enough to be in runtime for several weeks now with no serious issues. Even at peak we are seeing good response.

max_connections = 1500
shared_buffers = 10000
sort_mem = 1024
vacuum_mem = 8192
max_fsm_pages = 20000
max_fsm_relations = 500
# these next two mostly are useful when doing loads of lots of data
wal_buffers = 16
checkpoint_segments = 10
effective_cache_size = 131072
random_page_cost = 2

Shared memory is:
kernel.shmall = 134217728
kernel.shmmax = 134217728

Typical top might show:
top - 15:56:56 up 19 days, 20:45,  1 user,  load average: 0.07, 0.12, 0.08
Tasks: 343 total,   1 running, 342 sleeping,   0 stopped,   0 zombie
Cpu(s):   3.5% user,   5.1% system,   0.0% nice,  91.4% idle
Mem:   2068764k total,  1990108k used,    78656k free,    44252k buffers
Swap:  2097136k total,     6508k used,  2090628k free,  1425888k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  Command
16057 postgres  18   0  1132 1132  772 R  9.9  0.1   0:01.36 top
15202 postgres   9   0 20880  20m  18m S  0.9  1.0   0:00.59 postmaster
13971 postgres   9   0 86184  84m  82m S  0.6  4.2   0:01.71 postmaster
15064 postgres   9   0 86168  84m  82m S  0.6  4.2   0:01.50 postmaster
19271 postgres  10   0   892  712  676 S  0.3  0.0 110:02.12 postmaster
 6992 postgres  10   0 86244  84m  82m S  0.3  4.2   0:05.46 postmaster
12448 postgres   9   0 86100  84m  82m S  0.3  4.2   0:02.05 postmaster
12482 postgres   9   0 37448  36m  35m S  0.3  1.8   0:01.77 postmaster
12508 postgres   9   0 34408  33m  32m S  0.3  1.7   0:01.74 postmaster
12934 postgres   9   0 86308  84m  82m S  0.3  4.2   0:02.77 postmaster
12959 postgres   9   0 39216  38m  36m S  0.3  1.9   0:02.23 postmaster
13970 postgres   9   0 86160  84m  82m S  0.3  4.2   0:02.02 postmaster
13972 postgres   9   0 86176  84m  82m S  0.3  4.2   0:02.62 postmaster
13973 postgres   9   0 33420  32m  30m S  0.3  1.6   0:01.73 postmaster
14974 postgres   9   0 86112  84m  82m S  0.3  4.2   0:01.73 postmaster
14975 postgres   9   0 19980  19m  17m S  0.3  1.0   0:00.66 postmaster
14984 postgres   9   0 86112  84m  82m S  0.3  4.2   0:01.00 postmaster

This is on a 2 processor Dell box with 2 gigs of RAM:
Linux ourdbname2.4.21-0.13mdkenterprise #1 SMP Fri Mar 14 14:40:17 EST 2003 i686 unknown unknown GNU/Linux
Almost certainly the newest version of redhat linux would help us as it seems to have improvements in the areas we need, but we haven't yet tried it.

Have no idea offhand what the drives are -- not RAIDed, but like I say, we have a wierd usage.

We are generally 85-90% idle time on processors with 300-700 connections at a time, most all of them running spatial queries using the GIS/GEOS extension.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From: Tyler Mitchell [mailto:TMitchell at lignum.com]
Sent: Tuesday, February 17, 2004 3:36 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Optimizing postgresql.conf for large postgisdatasets

Hi gang, 
I'm running postgis on a server with dual processors and 2GB of RAM.  I'm wondering if anyone can suggest settings for the .../data/postgresql.conf file (i.e. shared mem settings)?  I'm finding that some queries are chewing into my drive space using postgresql temporary files quite severely (filling my drive!)...but that's another story. 

Tyler

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20040217/343dcd1e/attachment.html>