Performance with Large Volumes of Data - Mailing list pgsql-admin

Hi,

I am non a "real" sysadmin or dba, but "got stuck" doing it and am trying to learn via a fairly
difficult problem that my group must deal with: a LARGE volume of data.  I have been working
from scratch on this for about 3 weeks and have runs lots of tests.

I am running postgres7.1 on a Solaris5.7 with 1GB RAM and 2 300MHZ processors and a 6GB partition.

The application I am using it for is to COPY a LARGE amount of data (avg of 15k rows
of 3 ints every 15 minutes or so avg from 170 machines day/250 at night) into a db
and then do a query after the fact.  The COPIES are done via the libq PQputline()
subroutine.  The after-the-fact query will postprocess the data and reduce the
amount and granularity of data then load it into a new table.  Ultimately, I will have
1 db with a table of about 250M rows and several other dbs each with 10's of millions...
Multiple GBs of data.

Here are the options I run with postmaster:

postmaster -D /evsx/aus16/coers -o "-S 32768" -i -B 8192 -N 2

Here are my IPC params:
set shmsys:shminfo_shmmax=524288000
set shmsys:shminfo_shmmin=16
set shmsys:shminfo_shmmni=200
set shmsys:shminfo_shmseg=200
set semsys:seminfo_semmni=500
set semsys:seminfo_semmns=500
set semsys:seminfo_semmsl=500
set semsys:seminfo_semume=100


Here are my questions:

1)  NUM OF CONNECTIONS: I use -N 2 because that seems to optimize performance.  If I allow more connections,
the server bogs down, ultimately to a near-stand still if I allow too many connections.  I assume
this is because all of the competing connections are all trying to COPY to the same database
and they block all but one and all the semaphore chasing slows everything down.  The weird
thing is that the CPU, iowait and swap waits on top do not elevate very much.  What is slowing
things down in this case?  Currently, I have each client try for a connection and if it fails,
wait for rand()%4+1 and then try again.  This actually works pretty well, but it seems to me
that the server should be handling this and be doing a more efficient job.  Also, am I correct
in assuming there is no way to keep multiple COPIES to the same db & table from blocking?  When
I tried to setnonblocking, data got dropped.

2)  BOTTLENECK:  I was running 2 queries on the 65M rows of data I had collected after I had finished loading.
I had not indexed the tables.  Based on the top reading below, what is the bottleneck that is
slowing the query down?  The same phenomenon occurs when COPYing data into the table.

last pid: 15973;  load averages:  0.98,  0.92,  0.78
41 processes:  38 sleeping, 1 running, 2 on cpu
CPU states: 48.5% idle, 43.8% user,  5.2% kernel,  2.6% iowait,  0.0% swap
Memory: 1024M real, 17M free, 109M swap in use, 2781M swap free

  PID USERNAME THR PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND
15919 postgres   1  40    0   71M   69M cpu0   30:58 22.12% postgres
15966 postgres   1  30    0   71M   69M run    12:02 23.63% postgres


3)  CONGIGURATION/SETTINGS:  Are my IPC params and postmaster options set right for my application?
My thinking is that I need lots of shared memory to reduce disk access.  Am I missing something?  Are
there any other configurable kernal params that I need to know about?  What exactly will the sort
mem (-o "-S") buy me?

Thanks for your patience!

--
John Coers            Intrinsity, Inc.
coers@intrinsity.com  Austin, Texas