Обсуждение: 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@pi2:/opt/postgres/master/bin $ ./createdb pgbench
robins@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@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@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@gmail.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@mail.gmail.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@pi2:/opt/postgres/master/bin $ ./createdb pgbench > robins@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@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@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@postgresql.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@cri.ensmp.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@sss.pgh.pa.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.paquier@gmail.com> wrote: > On Thu, Mar 17, 2016 at 2:14 PM, Tom Lane <tgl@sss.pgh.pa.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