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.
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 |