"Scott Buchan" <sbuchan@technicacorp.com> writes:
> I am getting poor performance from my postgresql (version 7.3.2 compiled
> with gcc 2.95.2) database when running load tests on my web application.
> The database works great until I get above 200 concurrent users.
Hmm ... that sounds kinda familiar; you might check the archives for
similar reports from Solaris users. AFAIR we didn't figure out the
problem yet, but there's some raw data available.
> When running the load tests, the cpu of the box is always above 60%
> idle. I have run iostat and I am not seeing any problems with io.
[ scratches head ... ] If the bottleneck isn't CPU, and it isn't I/O,
then what could it be? You sure about the above observations? (Does
iostat include swap activity on that platform?)
The only other idea I can think of is that there's some weird effect in
the locking code (which only shows up with lots of concurrent backends)
such that would-be lockers repeatedly fail and sleep when they should
have gotten the lock. If you can figure out how to tell the difference
between a backend waiting for disk I/O and one waiting for a semaphore
or sleeping, it'd be interesting to see what the majority of the
backends are doing.
Another way to try to gather some data is to attach to one of the
backend processes with a debugger, and just stop it to get a stack trace
every so often. If the stack traces tend to point to the same place
that would give some info about the bottleneck.
regards, tom lane