Date: Sun, 3 Feb 2008 16:17:55 -0500 (EST)
From: Greg Smith <gsmith@gregsmith.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: slow 8.2.6 with 50 connections
In-Reply-To: <162867790802030802h322ed3b8ja4c27a3fa57dc2bd@mail.gmail.com>
Message-ID: <Pine.GSO.4.64.0802031557230.7860@westnet.com>
References: <162867790802030802h322ed3b8ja4c27a3fa57dc2bd@mail.gmail.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

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