I'm using PostgreSQL 7.4.2 (package from backports.org)
on a Debian (woody) box. The machine is IBM eServer 345
with two 2.8 Xeon CPUs, it has 1024MB of RAM and
two 15k RPM SCSI disks running in hardware RAID1, which
is provided by the onboard LSI Logic controller (LSI53C1030).
The database consists of two rather large tables
(currently about 15 million rows in one table
and about 5 million in the other one). Both tables
have 5 indexes (4 btree/1 hash).
Application running on the server INSERTs a lot of
stuff to the tables (which is not the target use of
the DB, it'll add data periodically, about
300 rows per 10 minutes). Queries (SELECTs) run perfectly
fine on the database, thanks to the indexes we have
Performance issue, I'm experiencing here, is somewhat
weird - server gets high average load (from 5 up to 15,
8 on average). Standard performance monitoring
utilities (like top) show that CPUs are not loaded
(below 20%, often near zero).
With kernel 2.6.x which I was using earlier,
top showed very high "wa" values (which indicate I/O waiting, AFAIK).
I've googled some issues with 2.6 kernels and LSI Logic controllers
running RAID, so I've downgraded the kernel to 2.4.26.
The machine started to behave a bit better, but still high
load states look weird. Unfortunately, top with 2.4 kernels
does not show "wa" column, so I can't be sure if the load is
caused by waiting for disks, but high idle values and high average
load would suggest it. With kernel 2.6 swap was almost always 100% free,
with 2.4.26 Linux eats below 5 megabytes of swapspace.
PostgreSQL is running with shared_mem set to 48000,
sort_mem = 4096, fsync off.
Whole config is available here:
I've also made some iostat report (using iostat 3 1000 as
suggested in one of the posts):
Any solutions I should consider?
I'd be grateful getting some hints on this.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend