DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=W0Jtg7FRibttXsVeRY2cs/Dpf7+nbdzDY/vz9zfnrtDO+R8Al3JZXzw65UguN34dG0DeGm3rcLHftgetIwBxOBaXHipKA/KvqJRh3PbZcnRcaid8AScZjSqAoG7cMKoJgCBpI5dbEOVAZTdmfwnKnENJIQHoInpJIx3bRsx49us=
Message-ID: <162867790802030802h322ed3b8ja4c27a3fa57dc2bd@mail.gmail.com>
Date: Sun, 3 Feb 2008 17:02:13 +0100
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Postgresql Performance" <pgsql-performance@postgresql.org>
Subject: slow 8.2.6 with 50 connections
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hello

I am testing one server and I found strange behave of 8.2.6. My
configuration is:

Linux Orbisek 2.6.18-xeonsmp #1 SMP Thu Jan 31 14:09:15 CET 2008 i686
GNU/Linux, 4 x Intel(R) Xeon(R) CPU E5335  @ 2.00GHz, 6G RAM

pgbench on 8.3 puts 1600-1700tps without dependency on number of
connections or transactions.

pgbench on 8.2 is similar only for 10 connections and doesn't depend
on number of transactions:
postgres@Orbisek:/root$ /usr/local/pgsql/bin/pgbench -c10 -t 50000 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 10
number of transactions per client: 50000
number of transactions actually processed: 500000/500000
tps = 1747.662768 (including connections establishing)
tps = 1747.758538 (excluding connections establishing)

but is half with 50 connections:
10 (1780), 20 (1545), 30 (1400), 40 (1145) 50c (987tps)

postgres@Orbisek:/root$ /usr/local/pgsql/bin/pgbench -c50 -t 100 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 50
number of transactions per client: 100
number of transactions actually processed: 5000/5000
tps = 1106.484286 (including connections establishing)
tps = 1126.062214 (excluding connections establishing)
postgres@Orbisek:/root$ /usr/local/pgsql/bin/pgbench -c50 -t 1000 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 50
number of transactions per client: 1000
number of transactions actually processed: 50000/50000
tps = 975.009227 (including connections establishing)
tps = 976.521036 (excluding connections establishing)

all time load is less than 3 and cpu us 16%, cpu sys 5% (8.3 used
procs about 18%us and 7% sy)

shared_buffers = 160MB
work_mem = 10MB
maintenance_work_mem = 256MB
wal_buffers = 128kB
checkpoint_segments = 100
bgwriter_lru_percent = 20.0
bgwriter_lru_maxpages = 200
bgwriter_all_percent = 10
bgwriter_all_maxpages = 600
autovacuum_vacuum_cost_delay = 20

postgres82=# select mode, count(*) from pg_locks group by mode;
           mode           | count
--------------------------+-------
 ShareLock                |    40
 ShareUpdateExclusiveLock |     1
 AccessShareLock          |    99
 ExclusiveLock            |    62
 RowExclusiveLock         |   215
(5 rows)

postgres83=# select mode, count(*) from pg_locks group by mode;
           mode           | count
--------------------------+-------
 ShareLock                |    43
 ShareUpdateExclusiveLock |     2
 AccessShareLock          |   101
 ExclusiveLock            |   116
 RowExclusiveLock         |   218
(5 rows)
postgres@Orbisek:/root/postgresql-8.2.6/src/tools/fsync$ ./test_fsync
-f /usr/local/pgsql/data/aa
Simple write timing:
        write                    0.005241

Compare fsync times on write() and non-write() descriptor:
(If the times are similar, fsync() can sync data written
 on a different descriptor.)
        write, fsync, close      0.152853
        write, close, fsync      0.152203

Compare one o_sync write to two:
        one 16k o_sync write     0.298571
        two 8k o_sync writes     0.295349

Compare file sync methods with one 8k write:

        (o_dsync unavailable)
        write, fdatasync         0.151626
        write, fsync,            0.150524

Compare file sync methods with 2 8k writes:
        (o_dsync unavailable)
        open o_sync, write       0.340511
        write, fdatasync         0.182257
        write, fsync,            0.177968

any ideas are welcome

Regards
Pavel Stehule