Re: Analysis of ganged WAL writes

Tom Lane kirjutas E, 07.10.2002 kell 01:07:
>
> To test this, I made a modified version of pgbench in which each
> transaction consists of a simple
> insert into table_NNN values(0);
> where each client thread has a separate insertion target table.
> This is about the simplest transaction I could think of that would
> generate a WAL record each time.
>
> Running this modified pgbench with postmaster parameters
> postmaster -i -N 120 -B 1000 --wal_buffers=250
> and all other configuration settings at default, CVS tip code gives me
> a pretty consistent 115-118 transactions per second for anywhere from
> 1 to 100 pgbench client threads. This is exactly what I expected,
> since the database (including WAL file) is on a 7200 RPM SCSI drive.
> The theoretical maximum rate of sync'd writes to the WAL file is
> therefore 120 per second (one per disk revolution), but we lose a little
> because once in awhile the disk has to seek to a data file.
>
> Inserting the above patch, and keeping all else the same, I get:
>
> $ mybench -c 1 -t 10000 bench1
> number of clients: 1
> number of transactions per client: 10000
> number of transactions actually processed: 10000/10000
> tps = 116.694205 (including connections establishing)
> tps = 116.722648 (excluding connections establishing)
>
> $ mybench -c 5 -t 2000 -S -n bench1
> number of clients: 5
> number of transactions per client: 2000
> number of transactions actually processed: 10000/10000
> tps = 282.808341 (including connections establishing)
> tps = 283.656898 (excluding connections establishing)

in an ideal world this would be 5*120=600 tps.

Have you any good any ideas what holds it back for the other 300 tps ?

If it has CPU utilisation of only 50% then there must be still some
moderate lock contention.

btw, what is the number for 1-5-10 clients with fsync off ?

> $ mybench -c 10 -t 1000 bench1
> number of clients: 10
> number of transactions per client: 1000
> number of transactions actually processed: 10000/10000
> tps = 443.131083 (including connections establishing)
> tps = 447.406534 (excluding connections establishing)
>
> CPU loading goes from 80% idle at 1 client to 50% idle at 5 clients
> to <10% idle at 10 or more.
>
> So this does seem to be a nice win, and unless I hear objections
> I will apply it ...

3x speedup is not just nice, it's great ;)

--------------
Hannu