Thread: slow 8.2.6 with 50 connections

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

On Feb 3, 2008 10:02 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> I am testing one server and I found strange behave of 8.2.6. My
> configuration is:

Note that with a scaling factor that's < the number of clients, your
test isn't gonna be very useful.  scaling factor should always be >=
number of clients.

On 03/02/2008, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Feb 3, 2008 10:02 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> > Hello
> >
> > I am testing one server and I found strange behave of 8.2.6. My
> > configuration is:
>
> Note that with a scaling factor that's < the number of clients, your
> test isn't gonna be very useful.  scaling factor should always be >=
> number of clients.
>

I use it only for orientation. And reported behave signalize some problem.

Pavel

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