Re: About the tps explanation of pgbench, please help - Mailing list pgsql-general

On 11/18/2014 05:53 PM, Yanrui Hu wrote:

Adrian,
I saw that in two case, one is 54502 transactions and the other is 13966
but that is caused by capacity decrease.
And fps is transaction per second, so it's not the transactions but
transaction per second, so I don't think the total transactions
different has any problem.
Please point if my understanding is not correct.

Alright

If:

kph = kilometer per hour = kilometer/hour

100 km/1 hr = 100 km/hr

200 km/1 hr = 200 km/hr

If you cover 100 km in 1 hour you have an average rate of speed of 100 km/hr if you cover 200 km in 1 hour your average rate of speed is 200 km/hr

then

tps = transactions per second = transactions/sec

54502 transactions/600 sec = 90.84 transactions/sec

13966 transactions/600 sec = 23.28 transactions/sec

The numbers are not exactly the same as the below, but that is probably down to rounding error. They pass the close enough rule though:) Any way you look at it, if run a two tests over the same time period and one does less transactions then the other you will have different transactions rates(tps) You where asking about the why behind the different tps rates, the answer is above. In other words you cannot ignore the raw numbers for the transactions.

My initial plan is to know the impact if I move that db client (also a
server runs web server with restful api) out side to internet.

On Tue, Nov 18, 2014 at 10:35 PM, Adrian Klaver

<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

    On 11/18/2014 12:33 AM, Yanrui Hu wrote:

        I am working on a evaluation to put db client outside the
        datacenter and
        to know how the network impact on the business.
        After several round of testing, I have a question regarding to
        the two
        tps result in stress output.

        Test A:
        Client and DB server exist in same AWS datacenter.
        transaction type: Custom query
        scaling factor: 500
        query mode: simple
        number of clients: 25
        number of threads: 25
        duration: 600 s
        number of transactions actually processed: 54502
        tps = 90.814930 (including connections establishing)
        tps = 204.574432 (excluding connections establishing)

        Test B:
        Client and DB server exist in different AWS datacenter (west and
        east).
        transaction type: Custom query
        scaling factor: 500
        query mode: simple
        number of clients: 25
        number of threads: 25
        duration: 600 s
        number of transactions actually processed: 13966
        tps = 23.235705 (including connections establishing)
        tps = 42.915990 (excluding connections establishing)

        Its obviously that both tps become lower if client and server do not
        exist in same datacetner since the network connection have more
        latency.
        But I can not explain why the tps that excluding connections
        establishing is changed so much.
        For my understanding, tps excluding connections establishing get
        rid of
        the time that create socket cost. That means in above two test
        cases(only network different), the tps excluding connections
        establishing should be very close, right?


    Not that I can see from the numbers. In the non-network case you
    processed 54,502 transactions over 600s and in the network case
    13,966 transactions over 600s. Even if you factor out the connection
    establishment you have fewer transactions over the same time period
    for the network case. So there is no way the tps can be equivalent.
    As others have pointed out this due to the effect of network latency
    on the processing of the queries.

    You might want to take a look at the Notes section of here:

    http://www.postgresql.org/__docs/9.3/static/pgbench.html
    <http://www.postgresql.org/docs/9.3/static/pgbench.html>

    In particular the different logging options that are available. They
    may make it easier to see what is going on.


    Because the database is same

        and capability is same only network latency is different.


        --
        Best Regards,

        Yanrui Hu (Ray)



    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>




--
Best Regards,

Yanrui Hu (Ray)

--
Adrian Klaver
adrian.klaver@aklaver.com