Re: [HACKERS] pgbench -C -M prepared gives an error

Hi,

When trying pgbench with -C -M prepared gives an error (see log below).

It probably doesn't make sense (to have both options together), but
shouldn't it still PREPARE per connection, or exit gracefully / document
this?

robins(at)pi2:/opt/postgres/master/bin $ ./createdb pgbench
robins(at)pi2:/opt/postgres/master/bin $ ./pgbench -i pgbench
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.93 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
robins(at)pi2:/opt/postgres/master/bin $ ./pgbench -M prepared -C pgbench
starting vacuum...end.
client 0 aborted in state 7: ERROR: prepared statement "P0_7" does not
exist
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 1/10
latency average: 0.000 ms
tps = 22.399928 (including connections establishing)
tps = 52.598359 (excluding connections establishing)
robins(at)pi2:/opt/postgres/master/bin $ ./psql -U postgres -c "select
version();"
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6devel on armv7l-unknown-linux-gnueabihf, compiled by gcc
(Raspbian 4.9.2-10) 4.9.2, 32-bit
(1 row)

-
robins

--

-
robins

Sounds like a bug. We should either fix pgbench so that -M and -C can
be used together (I don't see any technical reason why we can't do
this) or modify pgbench to not allow using -M and -C (less desirable).

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

From: Robins Tharakan <tharakan(at)gmail(dot)com>
Subject: [BUGS] pgbench -C -M prepared gives an error
Date: Tue, 15 Mar 2016 19:18:47 +0000
Message-ID: <CAEP4nAwqG-XufE95gpCs2dpxmV7579c3AWjgEpW=xAE7+44_cw(at)mail(dot)gmail(dot)com>

> Hi,
>
> When trying pgbench with -C -M prepared gives an error (see log below).
>
> It probably doesn't make sense (to have both options together), but
> shouldn't it still PREPARE per connection, or exit gracefully / document
> this?
>
> robins(at)pi2:/opt/postgres/master/bin $ ./createdb pgbench
> robins(at)pi2:/opt/postgres/master/bin $ ./pgbench -i pgbench
> NOTICE: table "pgbench_history" does not exist, skipping
> NOTICE: table "pgbench_tellers" does not exist, skipping
> NOTICE: table "pgbench_accounts" does not exist, skipping
> NOTICE: table "pgbench_branches" does not exist, skipping
> creating tables...
> 100000 of 100000 tuples (100%) done (elapsed 0.93 s, remaining 0.00 s)
> vacuum...
> set primary keys...
> done.
> robins(at)pi2:/opt/postgres/master/bin $ ./pgbench -M prepared -C pgbench
> starting vacuum...end.
> client 0 aborted in state 7: ERROR: prepared statement "P0_7" does not
> exist
> transaction type: <builtin: TPC-B (sort of)>
> scaling factor: 1
> query mode: prepared
> number of clients: 1
> number of threads: 1
> number of transactions per client: 10
> number of transactions actually processed: 1/10
> latency average: 0.000 ms
> tps = 22.399928 (including connections establishing)
> tps = 52.598359 (excluding connections establishing)
> robins(at)pi2:/opt/postgres/master/bin $ ./psql -U postgres -c "select
> version();"
> version
> ----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6devel on armv7l-unknown-linux-gnueabihf, compiled by gcc
> (Raspbian 4.9.2-10) 4.9.2, 32-bit
> (1 row)
>
>
> -
> robins
>
> --
>
> -
> robins

Tatsuo Ishii <ishii(at)postgresql(dot)org> writes:
> Sounds like a bug. We should either fix pgbench so that -M and -C can
> be used together (I don't see any technical reason why we can't do
> this) or modify pgbench to not allow using -M and -C (less desirable).

We're not resetting the prepared[] array when we pull the plug on an
existing connection.

Is a connection per transaction really a sane case to consider?
I can certainly understand why bugs in that code path might go
undetected for years.

regards, tom lane

> We're not resetting the prepared[] array when we pull the plug on an
> existing connection.
>
> Is a connection per transaction really a sane case to consider?

Yes, I would think. This case reveals the connection overhead. We
already are able to handle the simple query cases. Why not for
extended query cases?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Hello Tatsuo,

>> We're not resetting the prepared[] array when we pull the plug on an
>> existing connection.
>>
>> Is a connection per transaction really a sane case to consider?
>
> Yes, I would think. This case reveals the connection overhead. We
> already are able to handle the simple query cases. Why not for
> extended query cases?

Probably it can be made to work, but it is much less useful to prepare a
statement which is known to be needed just once, so I think it would be
fine to simply forbid "-M prepared" and "-C" together.

--
Fabien.

Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> writes:
>>> Is a connection per transaction really a sane case to consider?

>> Yes, I would think. This case reveals the connection overhead. We
>> already are able to handle the simple query cases. Why not for
>> extended query cases?

> Probably it can be made to work, but it is much less useful to prepare a
> statement which is known to be needed just once, so I think it would be
> fine to simply forbid "-M prepared" and "-C" together.

It's certainly a bug that the combination of the switches doesn't work,
and I already fixed it (47211af17a). My question was more towards
whether -C is a useful benchmarking option at all. I cannot imagine
a situation in which, if someone said "I'm doing only one transaction per
session, and I have a performance problem", I would not answer "yes,
and you just explained why".

What I found out when I looked into it was that pgbench had simply failed
to consider *at all* whether it needed to reset any state when dropping a
connection and replacing it with a new one. That's a really fundamental
problem, even if the only symptom we've found so far is "-M prepared" not
working. And it's been there since -C was invented, AFAICT. The fact
that the bug went undetected this long says a lot about the amount of
real-world use the switch gets. So I think it's fair to consider whether
we should not eliminate a whole class of future bugs by removing a switch
that gets no use.

regards, tom lane

On Thu, Mar 17, 2016 at 2:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> It's certainly a bug that the combination of the switches doesn't work,
> and I already fixed it (47211af17a). My question was more towards
> whether -C is a useful benchmarking option at all. I cannot imagine
> a situation in which, if someone said "I'm doing only one transaction per
> session, and I have a performance problem", I would not answer "yes,
> and you just explained why".

-1 for removing it. I found myself in need of it just a couple of days
back when testing the GSSAPI encryption patch with a read-only quick
load to test if the patch was robust enough to handle a mountain of
connection attempts.
--
Michael

On Thu, Mar 17, 2016 at 9:20 AM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> On Thu, Mar 17, 2016 at 2:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> It's certainly a bug that the combination of the switches doesn't work,
>> and I already fixed it (47211af17a). My question was more towards
>> whether -C is a useful benchmarking option at all. I cannot imagine
>> a situation in which, if someone said "I'm doing only one transaction per
>> session, and I have a performance problem", I would not answer "yes,
>> and you just explained why".
>
> -1 for removing it. I found myself in need of it just a couple of days
> back when testing the GSSAPI encryption patch with a read-only quick
> load to test if the patch was robust enough to handle a mountain of
> connection attempts.

I've used it, too.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> It's certainly a bug that the combination of the switches doesn't work,
> and I already fixed it (47211af17a). My question was more towards
> whether -C is a useful benchmarking option at all. I cannot imagine
> a situation in which, if someone said "I'm doing only one transaction per
> session, and I have a performance problem", I would not answer "yes,
> and you just explained why".

You could use -f option to execute multiple transactions in a session
using a custom script file.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp