Re: slow 8.2.6 with 50 connections

On Sun, 3 Feb 2008, Pavel Stehule wrote:

> postgres@Orbisek:/root$ /usr/local/pgsql/bin/pgbench -c10 -t 50000 test
> scaling factor: 1

If you're running with the number of clients much greater than the scaling
factor, it's unsurprising transactions are suffering from lock issues at
higher client loads.  It's good news that situation is much improved in
8.3 but I'm not sure how much you can conclude from that.

Increasing scale will make the database bigger and drive down your results
dramatically though, as it will get more disk-bound.  Consider running
pgbench with "-N", which removes updates to the branches/tellers table
where the worse locking issues are at, and see if that changes how 8.2 and
8.3 compare.  But try the things below first.

The other thing to try is running the pgbench client on another system
from the server itself.  I've seen resultings showing this curve before
(sharp dive at higher TPS) that flattened out considerably once that was
done.

> bgwriter_lru_percent = 20.0
> bgwriter_lru_maxpages = 200
> bgwriter_all_percent = 10
> bgwriter_all_maxpages = 600

Hmm, isn't that the set that Sun was using in their benchmarks?  Unless
you have more CPUs than your system does, these are way more aggressive
than make sense--the percentages moreso than the pages.  For pgbench
tests, you'll probably find performance improves in every way if you just
turn the background writer off in 8.2.  I suspect that part of your 8.2
vs. 8.3 difference here is that the way you're 8.2 background writer is
configured here is wasting all sorts of CPU and I/O resources doing
unproductive things.  8.3 took away most of these parameters specifically
to keep that from happening.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD