<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 5.00.2919.6307" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=031560000-18022004>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.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>max_connections = 1500</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>shared_buffers = 10000</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>sort_mem = 1024</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>vacuum_mem = 8192</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>max_fsm_pages = 20000</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>max_fsm_relations = 500</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=031560000-18022004>#
these next two mostly are useful when doing loads of lots of
data</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>wal_buffers = 16</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>checkpoint_segments = 10</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>effective_cache_size = 131072</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>random_page_cost = 2</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=031560000-18022004>Shared
memory is:</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>kernel.shmall = 134217728<BR>kernel.shmmax =
134217728</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>Typical top might show:</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=031560000-18022004>top -
15:56:56 up 19 days, 20:45, 1 user, load average: 0.07, 0.12,
0.08<BR>Tasks: 343 total, 1 running, 342 sleeping, 0
stopped, 0 zombie<BR>Cpu(s): 3.5% user, 5.1%
system, 0.0% nice, 91.4% idle<BR>Mem: 2068764k
total, 1990108k used, 78656k free,
44252k buffers<BR>Swap: 2097136k total, 6508k
used, 2090628k free, 1425888k cached</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=031560000-18022004>
PID USER PR NI VIRT RES
SHR S %CPU %MEM TIME+ Command<BR>16057 postgres
18 0 1132 1132 772 R 9.9 0.1
0:01.36 top<BR>15202 postgres 9 0 20880 20m
18m S 0.9 1.0 0:00.59 postmaster<BR>13971
postgres 9 0 86184 84m 82m S 0.6
4.2 0:01.71 postmaster<BR>15064 postgres 9 0
86168 84m 82m S 0.6 4.2 0:01.50
postmaster<BR>19271 postgres 10 0 892
712 676 S 0.3 0.0 110:02.12 postmaster<BR> 6992
postgres 10 0 86244 84m 82m S 0.3
4.2 0:05.46 postmaster<BR>12448 postgres 9 0
86100 84m 82m S 0.3 4.2 0:02.05
postmaster<BR>12482 postgres 9 0 37448 36m
35m S 0.3 1.8 0:01.77 postmaster<BR>12508
postgres 9 0 34408 33m 32m S 0.3
1.7 0:01.74 postmaster<BR>12934 postgres 9 0
86308 84m 82m S 0.3 4.2 0:02.77
postmaster<BR>12959 postgres 9 0 39216 38m
36m S 0.3 1.9 0:02.23 postmaster<BR>13970
postgres 9 0 86160 84m 82m S 0.3
4.2 0:02.02 postmaster<BR>13972 postgres 9 0
86176 84m 82m S 0.3 4.2 0:02.62
postmaster<BR>13973 postgres 9 0 33420 32m
30m S 0.3 1.6 0:01.73 postmaster<BR>14974
postgres 9 0 86112 84m 82m S 0.3
4.2 0:01.73 postmaster<BR>14975 postgres 9 0
19980 19m 17m S 0.3 1.0 0:00.66
postmaster<BR>14984 postgres 9 0 86112 84m
82m S 0.3 4.2 0:01.00 postmaster</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=031560000-18022004>This
is on a 2 processor Dell box with 2 gigs of RAM:</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=031560000-18022004>Linux
ourdbname2.4.21-0.13mdkenterprise #1 SMP Fri Mar 14 14:40:17 EST 2003 i686
unknown unknown GNU/Linux</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=031560000-18022004>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.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=031560000-18022004>Have
no idea offhand what the drives are -- not RAIDed, but like I say, we have a
wierd usage.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=031560000-18022004>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.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>HTH,</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=031560000-18022004>Greg
Williamson</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>DBA</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=031560000-18022004>GlobeXplorer LLC</SPAN></FONT></DIV>
<BLOCKQUOTE>
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Tyler Mitchell
[mailto:TMitchell@lignum.com]<BR><B>Sent:</B> Tuesday, February 17, 2004 3:36
PM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Subject:</B>
[postgis-users] Optimizing postgresql.conf for large
postgisdatasets<BR><BR></DIV></FONT><BR><FONT face=sans-serif size=2>Hi
gang,</FONT> <BR><FONT face=sans-serif size=2>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.</FONT> <BR><BR><FONT face=sans-serif
size=2>Tyler</FONT></BLOCKQUOTE></BODY></HTML>