Urgent: Tuning strategies? - Mailing list pgsql-general

Hello!

We are trying to get a new PostgreSQL-Database in production state, but
we currently experience very serious problems with performance. Data and
application (ColdFusion on four webservers, accessed via ODBC) have been
ported from an Oracle 8i database. We haven't had any experience with
PostgreSQL beforehand - that's why we're so desparate for some aid...

Here's the data I can supply:

Its a postgresql-7.2.1-installation under SuSE 7.3 on a 1GB RAM,
4xPIII550MHz Xeon machine with ~30MB storage on RAID 5 (3 disks),
filesystem is ext3.

custom-settings in postgresql.conf:
max_connections = 256
shared_buffers = 56320
wal_buffers = 32
sort_mem = 64336
wal_files = 64
fsync = false
effective_cache_size = 18200

cat /proc/sys/kernel/shmmax:
536870912

exemplary ipcs -m:
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status

0x00000000 32768      root      600        1056768    3          dest

0x00000000 98305      root      600        33554432   3          dest

0x00000000 131074     wwwrun    600        368644     3          dest

0x0052e2c1 1638403    postgres  600        472064000  33

0x07021999 229380     root      644        1104       1

A complete dump.sql of the database-installation is roughly 300MB in
size. It consists out of ten databases, one of which makes up for about
75% of all data (community-database for forums, usertables,
messaging-system), so we have a suspicion that this is where our
serverload-hog sits. This database has 33 tables, the biggest of which
as currently 237884 records and is 24 fields wide, the second biggest
has got 84512 records and is 60 fields wide.

exemplary top-output:
12:04pm  up 5 days,  1:13,  3 users,  load average: 2.78, 2.78, 2.89
143 processes: 138 sleeping, 5 running, 0 zombie, 0 stopped
CPU0 states: 44.0% user, 12.3% system,  0.0% nice, 43.2% idle
CPU1 states: 48.4% user, 12.1% system,  0.0% nice, 39.0% idle
CPU2 states: 48.5% user, 15.5% system,  0.0% nice, 35.1% idle
CPU3 states: 55.0% user, 12.5% system,  0.0% nice, 31.5% idle
Mem:  1029400K av, 1023660K used,    5740K free,       0K shrd,    2932K
buff
Swap: 2097136K av,  459800K used, 1637336K free                  699220K
cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
29565 postgres  16   0  124M 124M  118M R    85.4 12.4  15:42 postmaster
30004 postgres  15   0  305M 305M  303M R    67.4 30.3   0:13 postmaster
29647 postgres  11   0  440M 440M  438M R    56.7 43.8   6:19 postmaster
30057 postgres  17   0   980  980   732 R    17.3  0.0   0:03 top
26122 root      10   0  1000 1000   732 R    17.0  0.0  92:15 top
29726 postgres   9   0 82536  80M 81340 S     3.1  8.0   0:56 postmaster

I vacuum-analyze regularly once a day during low-traffic times (3:00
a.m.), which takes ~3.5 minutes over all databases. Everytime we do some
big updates/inserts in the process of porting the original
Oracle-DB-data over to PostgreSQL, we do a vacuum-analyze afterwards.

The whole project feeds several websites, one of which (currently still
on Oracle) will probably cause about 20x as much load as all the rest;
we need to switch this last website over to the PostgreSQL-DB, too, and
as soon as possible. What I'd need is your opinion on the given
fundamental data (server- and OS-config, shared memory, top-output) and
some hints on how to find the bottlenecks we seem to have.

I read as much documenation as I could, but there's no "guided tour to
postgres-tuning", or at least not one that I could find. We do use
EXPLAIN-output for finding out if our indexes are used or not (and it
seems they are), but there's still so much left to guess-work, like what
bit exactly is causing the high server-loads. How can we identify these
bottlenecks? How can I find the "sweet spots" for the optimizer-settings
in postgresql.conf? What else should we or can we do in order to
maximize performance? How can we reduce swap-activity further, because I
think it's quite high (see top-output)?

I know that I haven't exactly given profound info, but unfortunately I
don't know what info you'd need in order to be able to provide specific
hints. So what info can you give me now, please, and what info can I
give you, so you could give me further assistance?

Thank you very much in advance for you help, I am (almost) sure that
with your aid we'll be able to accomplish the task :)

Regards,

  Markus