Help tuning postgres - Mailing list pgsql-performance

Hi all,

After a long time of reading the general list it's time to subscribe to
this one...

We have adapted our application (originally written for oracle) to
postgres, and switched part of our business to a postgres data base.

The data base has in the main tables around 150 million rows, the whole
data set takes ~ 30G after the initial migration. After ~ a month of
usage that bloated to ~ 100G. We installed autovacuum after ~ 2 weeks.

The main table is heavily updated during the active periods of usage,
which is coming in bursts.

Now Oracle on the same hardware has no problems handling it (the load),
but postgres comes to a crawl. Examining the pg_stats_activity table I
see the updates on the main table as being the biggest problem, they are
very slow. The table has a few indexes on it, I wonder if they are
updated too on an update ? The index fields are not changing. In any
case, I can't explain why the updates are so much slower on postgres.

Sorry for being fuzzy a bit, I spent quite some time figuring out what I
can do and now I have to give up and ask for help.

The machine running the DB is a debian linux, details:

$ cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 11
model name      : Intel(R) Pentium(R) III CPU family      1266MHz
stepping        : 1
cpu MHz         : 1263.122
cache size      : 512 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 2
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 mmx fxsr sse
bogomips        : 2490.36

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 11
model name      : Intel(R) Pentium(R) III CPU family      1266MHz
stepping        : 1
cpu MHz         : 1263.122
cache size      : 512 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 2
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 mmx fxsr sse
bogomips        : 2514.94


$ uname -a
Linux *** 2.6.12.3 #1 SMP Tue Oct 11 13:13:00 CEST 2005 i686 GNU/Linux


$ cat /proc/meminfo
MemTotal:      4091012 kB
MemFree:        118072 kB
Buffers:         18464 kB
Cached:        3393436 kB
SwapCached:          0 kB
Active:         947508 kB
Inactive:      2875644 kB
HighTotal:     3211264 kB
HighFree:          868 kB
LowTotal:       879748 kB
LowFree:        117204 kB
SwapTotal:           0 kB
SwapFree:            0 kB
Dirty:           13252 kB
Writeback:           0 kB
Mapped:         829300 kB
Slab:            64632 kB
CommitLimit:   2045504 kB
Committed_AS:  1148064 kB
PageTables:      75916 kB
VmallocTotal:   114680 kB
VmallocUsed:        96 kB
VmallocChunk:   114568 kB


The disk used for the data is an external raid array, I don't know much
about that right now except I think is some relatively fast IDE stuff.
In any case the operations should be cache friendly, we don't scan over
and over the big tables...

The postgres server configuration is attached.

I have looked in the postgres statistics tables, looks like most of the
needed data is always cached, as in the most accessed tables the
load/hit ratio is mostly something like 1/100, or at least 1/30.


Is anything in the config I got very wrong for the given machine, or
what else should I investigate further ? If I can't make this fly, the
obvious solution will be to move back to Oracle, cash out the license
and forget about postgres forever...

TIA,
Csaba.