9.5: pg_stat_statement and pgbench execution time discrepancies

From: Pavel Suderevsky <psuderevsky(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: 9.5: pg_stat_statement and pgbench execution time discrepancies
Date: 2016-07-20 17:30:14
Message-ID: CAEBTBzuUrGMbWBh9arabRpXrYWPm==31G=wQM4rEhwqmuobMiA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

During performance tests it was mentioned that pgbench common results are
being different from what pg_stat_statements provides.

PostgreSQL version 9.5.3, centos 7.2. First run after pg_stat_extension
created and first pgbench run.

*pgbench *-c 30 -T 300 -U postgres -P 10 -r -v

> statement latencies in milliseconds:
>>
>> 0.003431 \set nbranches 1 * :scale
>>
>> 0.001046 \set ntellers 10 * :scale
>>
>> 0.000891 \set naccounts 100000 * :scale
>>
>> 0.001356 \setrandom aid 1 :naccounts
>>
>> 0.000938 \setrandom bid 1 :nbranches
>>
>> 0.000879 \setrandom tid 1 :ntellers
>>
>> 0.000974 \setrandom delta -5000 5000
>>
>> 0.102258 BEGIN;
>>
>> 0.228744 UPDATE pgbench_accounts SET abalance = abalance +
>>> :delta WHERE aid = :aid;
>>
>> 0.156087 SELECT abalance FROM pgbench_accounts WHERE aid =
>>> :aid;
>>
>> 17.715645 UPDATE pgbench_tellers SET tbalance = tbalance +
>>> :delta WHERE tid = :tid;
>>
>> 6.222220 UPDATE pgbench_branches SET bbalance = bbalance +
>>> :delta WHERE bid = :bid;
>>
>> 0.294592 INSERT INTO pgbench_history (tid, bid, aid,
>>> delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
>>
>> 0.502843 END;
>>
>>
*pg_stat_statements *
Example 1

> query | UPDATE pgbench_accounts SET abalance = abalance + ?
>>> WHERE aid = ?;
>>
>> calls | 118804
>>
>> total_time | 5944.19700000145
>>
>> min_time | 0.017
>>
>> max_time | 146.501
>>
>> mean_time | 0.0500336436483624
>>
>> stddev_time | 0.860838186600729
>>
>> rows | 118804
>>
>> Example 2

> query | INSERT INTO pgbench_history (tid, bid, aid, delta,
>> mtime) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP);
>
> calls | 118804
>
> total_time | 16533.2040000006
>
> min_time | 0.075
>
> max_time | 1.583
>
> mean_time | 0.139163698191979
>
> stddev_time | 0.0342061695940176
>
> rows | 118804
>
>
Could anybody explain why results are different? Where is an inaccuracy -
in pg_stat_statements or pgbench?

Thanks in advance.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2016-07-20 17:33:59 Re: MediaWiki + PostgreSQL is not ready for production?
Previous Message Aislan Luiz Wendling 2016-07-20 17:17:01 Array value from table as parameter