Skip site navigation (1) Skip section navigation (2)

Re: testing HS/SR - 1 vs 2 performance

From: "Erik Rijkers" <er(at)xs4all(dot)nl>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: testing HS/SR - 1 vs 2 performance
Date: 2010-04-12 12:22:41
Message-ID: a6054370c007d55873fb9339255c6614.squirrel@webmail.xs4all.nl (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sat, April 10, 2010 01:23, Erik Rijkers wrote:
> Using 9.0devel cvs HEAD, 2010.04.08.
>
> I am trying to understand the performance difference
> between primary and standby under a standard pgbench
> read-only test.
>
> server has 32 GB, 2 quadcores.
>
> primary:
>   tps = 34606.747930 (including connections establishing)
>   tps = 34527.078068 (including connections establishing)
>   tps = 34654.297319 (including connections establishing)
>
> standby:
>   tps = 700.346283 (including connections establishing)
>   tps = 717.576886 (including connections establishing)
>   tps = 740.522472 (including connections establishing)
>
> transaction type: SELECT only
> scaling factor: 1000
> query mode: simple
> number of clients: 20
> number of threads: 1
> duration: 900 s
>
> both instances have
>   max_connections = 100
>   shared_buffers = 256MB
>   checkpoint_segments = 50
>   effective_cache_size= 16GB
>
> See also:
>
> http://archives.postgresql.org/pgsql-testers/2010-04/msg00005.php
>      (differences with scale 10_000)
>

To my surprise, I have later seen the opposite behaviour with the standby giving fast runs, and
the primary slow.

FWIW, I've overnight run a larget set of tests. (against same 9.0devel
instances as the ones from the earlier email).

These results are generally more balanced.

for scale in
    for clients in 1 5 10 20
        for port in 6565 6566 --> primaryport standbyport
            for run in `seq 1 3`
                pgbench ...
                sleep ((scale / 10) * 60)
            done
        done
    done
done

(so below, alternating 3 primary, followed by 3 standby runs)

scale: 10      clients:  1  tps = 15219.019272  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 10      clients:  1  tps = 15301.847615  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 10      clients:  1  tps = 15238.907436  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 10      clients:  1  tps = 12129.928289  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 10      clients:  1  tps = 12151.711589  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 10      clients:  1  tps = 12203.494512  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 10      clients:  5  tps = 60248.120599  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 10      clients:  5  tps = 60827.949875  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 10      clients:  5  tps = 61167.447476  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 10      clients:  5  tps = 50750.385403  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 10      clients:  5  tps = 50600.891436  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 10      clients:  5  tps = 50486.857610  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 10      clients: 10  tps = 60307.739327  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 10      clients: 10  tps = 60264.230349  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 10      clients: 10  tps = 60146.370598  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 10      clients: 10  tps = 50455.537671  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 10      clients: 10  tps = 49877.000813  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 10      clients: 10  tps = 50097.949766  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 10      clients: 20  tps = 43355.220657  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 10      clients: 20  tps = 43352.725422  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 10      clients: 20  tps = 43496.085623  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 10      clients: 20  tps = 37169.126299  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1
scale: 10      clients: 20  tps = 37100.260450  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1
scale: 10      clients: 20  tps = 37342.758507  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1
scale: 100     clients:  1  tps = 12514.185089  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 100     clients:  1  tps = 12542.842198  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 100     clients:  1  tps = 12595.688640  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 100     clients:  1  tps = 10435.681851  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 100     clients:  1  tps = 10456.983353  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 100     clients:  1  tps = 10434.213044  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 100     clients:  5  tps = 48682.166988  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 100     clients:  5  tps = 48656.883485  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 100     clients:  5  tps = 48687.894655  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 100     clients:  5  tps = 41901.629933  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 100     clients:  5  tps = 41953.386791  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 100     clients:  5  tps = 41787.962712  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 100     clients: 10  tps = 48704.247239  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 100     clients: 10  tps = 48941.190050  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 100     clients: 10  tps = 48603.077936  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 100     clients: 10  tps = 42948.666272  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 100     clients: 10  tps = 42767.793899  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 100     clients: 10  tps = 42612.670983  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 100     clients: 20  tps = 36350.454258  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 100     clients: 20  tps = 36373.088111  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 100     clients: 20  tps = 36490.886781  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 100     clients: 20  tps = 32235.811228  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1
scale: 100     clients: 20  tps = 32253.837906  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1
scale: 100     clients: 20  tps = 32144.189047  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1
scale: 500     clients:  1  tps = 11733.254970  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 500     clients:  1  tps = 11726.665739  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 500     clients:  1  tps = 11617.622548  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 500     clients:  1  tps =  9769.861175  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 500     clients:  1  tps =  9878.465752  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 500     clients:  1  tps =  9808.236216  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 500     clients:  5  tps = 45185.900553  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 500     clients:  5  tps = 45170.334037  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 500     clients:  5  tps = 45136.596374  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 500     clients:  5  tps = 39231.863815  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 500     clients:  5  tps = 39336.889619  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 500     clients:  5  tps = 39269.483772  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 500     clients: 10  tps = 45468.080680  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 500     clients: 10  tps = 45727.159963  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 500     clients: 10  tps = 45399.241367  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 500     clients: 10  tps = 40759.108042  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 500     clients: 10  tps = 40783.287718  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 500     clients: 10  tps = 40858.007847  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 500     clients: 20  tps = 34729.742313  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 500     clients: 20  tps = 34705.119029  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 500     clients: 20  tps = 34617.517224  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 500     clients: 20  tps = 31252.355034  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1
scale: 500     clients: 20  tps = 31234.885791  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1
scale: 500     clients: 20  tps = 31273.307637  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1
scale: 1000    clients:  1  tps =   220.024691  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 1000    clients:  1  tps =   294.855794  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 1000    clients:  1  tps =   375.152757  pgbench -h /tmp -p 6565 -n -S -c 1 -T 900 -j 1
scale: 1000    clients:  1  tps =   295.965959  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 1000    clients:  1  tps =  1036.517110  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 1000    clients:  1  tps =  9167.012603  pgbench -h /tmp -p 6566 -n -S -c 1 -T 900 -j 1
scale: 1000    clients:  5  tps =  1241.224282  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 1000    clients:  5  tps =  1894.806301  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 1000    clients:  5  tps = 18532.885549  pgbench -h /tmp -p 6565 -n -S -c 5 -T 900 -j 1
scale: 1000    clients:  5  tps =  1497.491279  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 1000    clients:  5  tps =  1480.164166  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 1000    clients:  5  tps =  3470.769236  pgbench -h /tmp -p 6566 -n -S -c 5 -T 900 -j 1
scale: 1000    clients: 10  tps =  2414.552333  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 1000    clients: 10  tps = 19248.609443  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 1000    clients: 10  tps = 45059.231609  pgbench -h /tmp -p 6565 -n -S -c 10 -T 900 -j 1
scale: 1000    clients: 10  tps =  1648.526373  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 1000    clients: 10  tps =  3659.800008  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 1000    clients: 10  tps = 35900.769857  pgbench -h /tmp -p 6566 -n -S -c 10 -T 900 -j 1
scale: 1000    clients: 20  tps =  2462.855864  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 1000    clients: 20  tps = 27168.407568  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 1000    clients: 20  tps = 34438.802096  pgbench -h /tmp -p 6565 -n -S -c 20 -T 900 -j 1
scale: 1000    clients: 20  tps =  2933.220489  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1
scale: 1000    clients: 20  tps = 25586.972428  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1
scale: 1000    clients: 20  tps = 30926.189621  pgbench -h /tmp -p 6566 -n -S -c 20 -T 900 -j 1





In response to

Responses

pgsql-hackers by date

Next:From: Jim MlodgenskiDate: 2010-04-12 12:32:39
Subject: Re: testing HS/SR - 1 vs 2 performance
Previous:From: Fujii MasaoDate: 2010-04-12 11:54:07
Subject: Re: walreceiver is uninterruptible on win32

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group