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@xxxxxxxxxxxxxxx> 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 > > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
