Wait wait, currently I'm playing the "stress scenario", so there are
only 256 sessions max, but thing time is zero (full stress). Scenario
with 1600 users is to test how database is solid just to keep a huge
amount of users, but doing only one transaction per second (very low
global TPS comparing to what database is able to do, but it's testing
how well its internals working to manage the user sessions).
I did not plan to do 1600 users test this time (all depends on time :-))
So, do I need to increase WAL buffers for 256 users?
My LOG and DATA are placed on separated storage LUNs and controllers
from the beginning.
I've changed the default 0.5 checkpoint_completion_target to 0.8 now,
should I go until 0.95 ?..
Also, to avoid TPS "waves" and bring stability on Read+Write workload
I followed advices from a parallel thread:
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0
shared_buffers = 1024MB
I've also tried shared_buffers=256MB as it was advised, but then
Read-Only workload decreasing performance as PG self caching helps
anyway.
Also, checkpoint_timeout is 30s now, and of course a huge difference
came with moving default_statistics_target to 5 ! -but this one I
found myself :-))
Probably checkpoint_timeout may be bigger now with the current
settings? - the goal here is to keep Read+Write TPS as stable as
possible and also avoid a long recovery in case of
system/database/other crash (in theory).
Rgds,
-Dimitri
On 5/12/09, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> On Tue, 2009-05-12 at 12:19 +0200, Dimitri wrote:
>
>> What I'm trying to do now is to understand what exactly is the
>> problem.
>
> You're running with 1600 users, which is above the scalability limit
> uncovered (by Sun...) during earlier benchmarking. The scalability
> issues are understood but currently considered above the
> reasonable-setting limit and so nobody has been inclined to improve
> matters.
>
> You should use a connection concentrator to reduce the number of
> sessions down to say 400.
>
> You're WAL buffers setting is also too low and you will be experiencing
> contention on the WALWriteLock. Increase wal_buffers to about x8 where
> you have it now.
>
> You can move pg_xlog to its own set of drives.
>
> Set checkpoint_completion_target to 0.95.
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>