Looking for bottleneck during load test

We have a PostgreSQL 8.2.7 database (~230 GB) running on a machine with 8
I=
ntel Xeon Cores and 32 GB RAM (64-bit Linux 2.6.18). Data is stored on an
E=
MC=B2 CLARiiON on RAID 1/0 (8 x 146 GB 15k rpm).=20
When we do random I/O with a small test tool (reading random 8k blocks
from=
 big files in 200 threads) on the disk we retrieve data with about 25
MB/s.

For testing purpose a test set of about 700.000 queries (those were logged
=
during a problem situation) are executed against the database in 180
concur=
rent threads.
Some of the queries are very small and fast - other ones read more than
500=
00 blocks. All queries are selects (using cursors) - there is only read
act=
ivity on the database.

By setting tuple_fraction for cursors to 0.0 instead of 0.1
(http://archive=
s.postgresql.org/pgsql-performance/2008-04/msg00018.php) we reduced reads
d=
uring the test (pg_statio_all_tables):
- 8.2.7
  reads from disk: 4.395.276, reads from cache: 471.575.925
- 8.2.7 cursor_tuple_fraction=3D0.0
  Reads from disk: 3.406.164, reads from cache: 37.924.625

But the duration of the test was only reduced by 18 % (from 110 minutes to
=
90 minutes).

When running the test with tuple_fraction=3D0.0 we observe the following
on=
 the server:
- avg read from disk is at 7 MB/s
- when we start the random I/O tool during the test we again read data
with=
 about 25 MB/s from disk (for me it seems that disk isn't the bottleneck)
- cpu time is divided between idle and iowait - user and system cpu are
pra=
ctically zero
- there are from 5000 to 10000 context switches per second

I can't see a bottleneck here. Does anyone has an explanation for that
beha=
vior?

Regards,
Robert


--=20
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance