Обсуждение: Slowness of extended protocol

> Hi all. I know this has been discussed before, I'd like to know what's the
> current position on this.
> 
> Comparing the performance of the simple vs. extended protocols with pgbench
> yields some extreme results:
> 
> $ ./pgbench -T 10 -S -M simple -f /tmp/pgbench.sql pgbench
> tps = 14739.803253 (excluding connections establishing)
> 
> $ ./pgbench -T 10 -S -M extended -f /tmp/pgbench.sql pgbench
> tps = 11407.012679 (excluding connections establishing)
> 
> (pgbench.sql contains a minimal SELECT 1, I'm running against localhost)
> 
> I was aware that there's some overhead associated with the extended
> protocol, but not that it was a 30% difference... My question is whether
> there are good reasons why this should be so, or rather that this simply
> hasn't been optimized yet. If it's the latter, are there plans to do so?
> 
> To give some context, I maintain Npgsql, the open-source .NET driver for
> PostgreSQL. Since recent version Npgsql uses the extended protocol almost
> exclusively, mainly because it does binary data rather than text. Even if
> that weren't the case, imposing such a performance penalty on extended-only
> features (parameters, prepared statements) seems problematic.
> 
> I'm aware that testing against localhost inflates the performance issue -
> taking into account the latency of a remote server, the simple/extended
> difference would be much less significant. But the issue still seems to be
> relevant.

Without re-using prepared statements or portals, extended protocol is
always slow because it requires more messages exchanges than simple
protocol. In pgbench case, it always sends parse, bind, describe,
execute and sync message in each transaction even if each transaction
involves identical statement ("SELECT 1" in your case).

See the manual for the protocol details.

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

On Sun, Jul 31, 2016 at 4:05 PM, Shay Rojansky <roji@roji.org> wrote:
> I'm well aware of how the extended protocol works, but it seems odd for a
> 30% increase in processing time to be the result exclusively of processing 5
> messages instead of just 1 - it doesn't seem like that big a deal (although
> I may be mistaken). I was imagining that there's something more fundamental
> in how the protocol or PostgreSQL state is managed internally, that would be
> responsible for the slowdown.

I think you're looking at this the wrong way around. 30% of what?
You're doing these simple read-only selects on a database that
obviously is entirely in RAM. If you do the math on the numbers you
gave above the simple protocol took 678 microseconds per transaction
and the extended protocol took 876 microseconds. The difference is 198
microseconds. I'm not sure exactly where those 200us are going and
perhaps it could be lower but in what real-world query is it going to
have a measurable impact on the total time?

The other danger in unrealistic test cases is that you're probably
measuring work that doesn't scale and in fact optimizing based on it
could impose a cost that *does* scale. For example if 150us of that
time is being spent in the prepare and we cut that down by a factor of
10 to 15us then it would be only a 10% penalty over the simple
protocol in your test. But if that optimization added any overhead to
the execute stage then when it's executed thousands of times that
could add milliseconds to the total runtime.

-- 
greg

On 2016-07-31 22:26:00 +0100, Greg Stark wrote:
> I think you're looking at this the wrong way around. 30% of what?
> You're doing these simple read-only selects on a database that
> obviously is entirely in RAM. If you do the math on the numbers you
> gave above the simple protocol took 678 microseconds per transaction
> and the extended protocol took 876 microseconds. The difference is 198
> microseconds. I'm not sure exactly where those 200us are going and
> perhaps it could be lower but in what real-world query is it going to
> have a measurable impact on the total time?

FWIW, I've observed the same with (a bit) more complicated queries. A
part of this is that the extended protocol simply does
more. PQsendQueryGuts() sends Parse/Bind/Describe/Execute/Sync - that's
simply more work and data over the wire than a single Q message.

Whether that matters for a given workload or not, is a different
question, but I think it's pretty clear that it can for some.

Shay, are you using unnamed or named portals? There's already a shortcut
path for the former in some places.

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> FWIW, I've observed the same with (a bit) more complicated queries. A
> part of this is that the extended protocol simply does
> more. PQsendQueryGuts() sends Parse/Bind/Describe/Execute/Sync - that's
> simply more work and data over the wire than a single Q message.

Yeah.  The extended query protocol was designed to offer a lot of
functionality that people had asked for, like plan re-use and
introspection of the data types assigned to query parameters, but that
doesn't come at zero cost.  I think the tie-in to the plan cache is a
significant part of the added overhead, and so is the fact that we have to
iterate the per-message loop in PostgresMain five times not once, with
overheads like updating the process title incurred several times in that.

In hindsight it seems clear that what a lot of apps want out of extended
protocol is only the ability to send parameter values out-of-line instead
of having to quote/escape them into SQL literals.  Maybe an idea for the
fabled V4 protocol update is some compromise query type that corresponds
precisely to PQexecParams's feature set: you can send parameter values
out-of-line, and you can specify text or binary results, but there's no
notion of any persistent state being created and no feedback about
parameter data types.
        regards, tom lane

Doesn't this patch break an existing behavior of unnamed statements?
That is, an unnamed statement shall exist until next parse message
using unnamed statement received. It is possible to use the same
unnamed statement multiple times in a transaction.

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

On Sun, Jul 31, 2016 at 05:57:12PM -0400, Tom Lane wrote:
> In hindsight it seems clear that what a lot of apps want out of extended
> protocol is only the ability to send parameter values out-of-line instead
> of having to quote/escape them into SQL literals.  Maybe an idea for the
> fabled V4 protocol update is some compromise query type that corresponds
> precisely to PQexecParams's feature set: you can send parameter values
> out-of-line, and you can specify text or binary results, but there's no
> notion of any persistent state being created and no feedback about
> parameter data types.

Do you want this on the TODO list?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +

Bruce Momjian <bruce@momjian.us> writes:
> On Sun, Jul 31, 2016 at 05:57:12PM -0400, Tom Lane wrote:
>> In hindsight it seems clear that what a lot of apps want out of extended
>> protocol is only the ability to send parameter values out-of-line instead
>> of having to quote/escape them into SQL literals.  Maybe an idea for the
>> fabled V4 protocol update is some compromise query type that corresponds
>> precisely to PQexecParams's feature set: you can send parameter values
>> out-of-line, and you can specify text or binary results, but there's no
>> notion of any persistent state being created and no feedback about
>> parameter data types.

> Do you want this on the TODO list?

I didn't hear anyone say it was a silly idea, so sure.
        regards, tom lane

On Wed, Aug  3, 2016 at 10:02:39AM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > On Sun, Jul 31, 2016 at 05:57:12PM -0400, Tom Lane wrote:
> >> In hindsight it seems clear that what a lot of apps want out of extended
> >> protocol is only the ability to send parameter values out-of-line instead
> >> of having to quote/escape them into SQL literals.  Maybe an idea for the
> >> fabled V4 protocol update is some compromise query type that corresponds
> >> precisely to PQexecParams's feature set: you can send parameter values
> >> out-of-line, and you can specify text or binary results, but there's no
> >> notion of any persistent state being created and no feedback about
> >> parameter data types.
> 
> > Do you want this on the TODO list?
> 
> I didn't hear anyone say it was a silly idea, so sure.

Done:
Create a more efficient way to handle out-of-line parameters 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +

On Wed, Aug 3, 2016 at 7:35 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, Aug  3, 2016 at 10:02:39AM -0400, Tom Lane wrote:
>> Bruce Momjian <bruce@momjian.us> writes:
>> > On Sun, Jul 31, 2016 at 05:57:12PM -0400, Tom Lane wrote:
>> >> In hindsight it seems clear that what a lot of apps want out of extended
>> >> protocol is only the ability to send parameter values out-of-line instead
>> >> of having to quote/escape them into SQL literals.  Maybe an idea for the
>> >> fabled V4 protocol update is some compromise query type that corresponds
>> >> precisely to PQexecParams's feature set: you can send parameter values
>> >> out-of-line, and you can specify text or binary results, but there's no
>> >> notion of any persistent state being created and no feedback about
>> >> parameter data types.
>>
>> > Do you want this on the TODO list?
>>
>> I didn't hear anyone say it was a silly idea, so sure.
>
> Done:
>
>         Create a more efficient way to handle out-of-line parameters

FWIW, I agree with this idea.

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

On Tue, Aug 2, 2016 at 2:00 PM, Shay Rojansky <roji@roji.org> wrote:
>> Shay, why don't you use a profiler? Seriously.
>> I'm afraid "iterate the per-message loop in PostgresMain five times not
>> once" /"just discussing what may or may not be a problem..."  is just
>> hand-waving.
>>
>> Come on, it is not that hard.
>
> I really don't get what's problematic with posting a message on a mailing
> list about a potential performance issue, to try to get people's reactions,
> without diving into profiling right away. I'm not a PostgreSQL developer,
> have other urgent things to do and don't even spend most of my programming
> time in C.

There's absolutely nothing wrong with that.  I find your questions
helpful and interesting and I hope you will keep asking them.  I think
that they are a valuable contribution to the discussion on this list.

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

On Fri, Aug 5, 2016 at 8:07 PM, Shay Rojansky <roji@roji.org> wrote:
>> > I really don't get what's problematic with posting a message on a
>> > mailing
>> > list about a potential performance issue, to try to get people's
>> > reactions,
>> > without diving into profiling right away. I'm not a PostgreSQL
>> > developer,
>> > have other urgent things to do and don't even spend most of my
>> > programming
>> > time in C.
>>
>> There's absolutely nothing wrong with that.  I find your questions
>> helpful and interesting and I hope you will keep asking them.  I think
>> that they are a valuable contribution to the discussion on this list.
>
> Thanks for the positive feedback Robert.
>
> I'm glad reducing the overhead of out-of-line parameters seems like an
> important goal. FWIW, if as Vladimir seems to suggest, it's possible to
> bring down the overhead of the v3 extended protocol to somewhere near the
> simple protocol, that would obviously be a better solution - it would mean
> things work faster here and now, rather than waiting for the v4 protocol. I
> have no idea if that's possible though, I'll see if I can spend some time on
> understand where the slowdown comes from.

I think that's a fine thing to work on, but I don't hold out a lot of
hope.  If we ask the question "can you reduce the overhead vs. the
status quo?" I will wager that the answer is "yes".  But if you ask
the question "can you make it as efficient as we could be given a
protocol change?" I will wager that the answer is "no".

That having been said, I don't really see a reason why we couldn't
introduce a new protocol message for this without bumping the protocol
version.  Clients who don't know about the new message type just won't
use it; nothing breaks.  Clients who do know about the new message
need to be careful not to send it to older servers, but since the
server reports its version to the client before the first opportunity
to send queries, that shouldn't be too hard.  We could add a new
interface to libpq that uses the new protocol message on new servers
and falls back to the existing extended protocol on older servers.  In
general, it seems to me that we only need to bump the protocol version
if there will be server-initiated communication that is incompatible
with existing clients.  Anything that the client can choose to
initiate (or not) based on the server version should be OK.

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

On Sun, Aug 7, 2016 at 7:46 PM, Shay Rojansky <roji@roji.org> wrote:
> We could call this "protocol 3.1" since it doesn't break backwards
> compatibility (no incompatible server-initiated message changes, but it does
> include a feature that won't be supported by servers which only support 3.0.
> This could be a sort of "semantic versioning" for the protocol - optional
> new client-initiated features are a minor version bump, others are a major
> version bump...

I wouldn't try to do that; we've done nothing similar in past
instances where we've added new protocol or sub-protocol messages,
which has happened at least for COPY BOTH mode within recent memory.
See d3d414696f39e2b57072fab3dd4fa11e465be4ed.

> This new client-initiated message would be similar to query, except that it
> would include the parameter and result-related fields from Bind. The
> responses would be identical to the responses for the Query message.
>
> Does this make sense?

I think so.

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

Vladimir Sitnikov <sitnikov.vladimir@gmail.com> writes:
> The point is "adding a message to current v3 protocol is not a backward
> compatible change".
> The problem with adding new message types is not only "client support", but
> deployment issues as well: new message would require simultaneous upgrade
> of both backend, client, and pgbouncer.

Right ...

> It could make sense to add some kind of transparent extensibility to the
> protocol, so clients can just ignore unknown message types.

I'm not really sure what use that particular behavior would be.  We
certainly want to try to have some incremental extensibility in there
when we do v4, but I think it would more likely take the form of a way
for client and server to agree on which extensions they support.

> On the other hand, usage of some well-defined statement name to trigger
> the special case would be fine: all pgbouncer versions would pass those
> parse/bind/exec message as if it were regular messages.

I do not accept this idea that retroactively defining special semantics
for certain statement names is not a protocol break.  If it causes any
change in what the server's response would be, then it is a protocol
break.

> Note: it is quite easy to invent a name that is not yet used in the wild,
> so it is safe.

Sir, that is utter nonsense.  And even if it were true, why is it that
this way would safely pass through existing releases of pgbouncer when
other ways would not?  Either pgbouncer needs to understand what it's
passing through, or it doesn't.
        regards, tom lane

Shay Rojansky <roji@roji.org> writes:
> Ah, I understand the proposal better now - you're not proposing encoding a
> new message type in an old one, but rather a magic statement name in Parse
> which triggers an optimized processing path in PostgreSQL, that wouldn't go
> through the query cache etc.

> If so, isn't that what the empty statement is already supposed to do? I
> know there's already some optimizations in place around the scenario of
> empty statement name (and empty portal).

Right, but the problem is that per the current protocol spec, those
optimizations are not allowed to change any protocol-visible behavior.
We might be able to do a bit more optimization than we're doing now, but
we still have to be able to cache the statement in case it's executed more
than once, we still have to do planning in response to a separate 'bind'
message, etc.  AFAICT most of the added overhead comes from having to
allow for the possibility of re-execution: that forces at least one extra
copy of the parse tree to be made, as well as adding manipulations of the
plan cache.  We can't get out from under that without a protocol change.

> Again, if it's possible to make "Parse/Describe/Bind/Execute/Sync" perform
> close to Query, e.g. when specifying empty statement/portal, that's
> obviously the best thing here. But people seem to be suggesting that a
> significant part of the overhead comes from the fact that there are 5
> messages, meaning there's no way to optimize this without a new message
> type.

The rather crude measurements I've done put most of the blame on the
cacheing part, although the number of messages might matter more on
platforms with slow process-title-update support (I'm looking at you,
Windows).
        regards, tom lane

>> On the other hand, usage of some well-defined statement name to trigger
>> the special case would be fine: all pgbouncer versions would pass those
>> parse/bind/exec message as if it were regular messages.
> 
> I do not accept this idea that retroactively defining special semantics
> for certain statement names is not a protocol break.  If it causes any
> change in what the server's response would be, then it is a protocol
> break.

+1. It definitely is a protocol break.

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

On Tue, Aug 9, 2016 at 4:50 AM, Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:
> I've tried pgbench -M prepared, and it is way faster than pgbench -M simple.

That's true, but it's also testing something completely different from
what Shay is concerned about.  -M prepared creates a prepared
statement once, and then executes it many times.  That is, as you say,
faster.  But what Shay is concerned about is the case where you are
using the extended query protocol to send out-of-line parameters but
for some reason you can't use prepared statements, for example because
the queries are dynamically generated and you have to keep sending a
different query text every time.  That case is analogous to -M
extended, not -M prepared.  And -M extended is well-known to be SLOWER
than -M simple.  Here's a quick test on my laptop demonstrating this:

[rhaas ~]$ pgbench -M simple -S -T 10
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 119244
latency average: 0.084 ms
tps = 11919.440321 (including connections establishing)
tps = 11923.229139 (excluding connections establishing)
[rhaas ~]$ pgbench -M prepared -S -T 10
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 192100
latency average: 0.052 ms
tps = 19210.341944 (including connections establishing)
tps = 19214.820999 (excluding connections establishing)
[rhaas ~]$ pgbench -M extended -S -T 10
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 104275
latency average: 0.096 ms
tps = 10425.510813 (including connections establishing)
tps = 10427.725239 (excluding connections establishing)

Shay is not worried about the people who are getting 19.2k TPS instead
of 11.9k TPS.  Those people are already happy.  He is worried about
the people who are getting 10.4k TPS instead of 11.9k TPS.  People who
can't use prepared statements because their query text varies - and I
have personally written multiple web applications that have exactly
that profile - suffer a big penalty if they choose to use the extended
query protocol to pass parameters.  Here, it's about a 13% performance
loss.

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

On Tue, Aug 9, 2016 at 5:07 PM, Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:
> I do not buy that "dynamically generated queries defeat server-prepared
> usage" argument. It is just not true (see below).
>
> Do you mean "in language X, where X != Java it is impossible to implement a
> query cache"?
> That is just ridiculus.

Well, multiple experienced users are telling you that this is a real
problem.  You certainly don't have to agree, but when a bunch of other
people who are smart and experienced think that something is a
problem, I think you should consider the possibility that those people
know what they are talking about.

> Do you agree that the major part would be some hot queries, the rest will be
> much less frequently used ones (e.g. one time queries)?

Sure, but I don't want the application to have to know about that, and
I don't really think the driver should need to know about that either.
Your point, as I understand it, is that sufficiently good query
caching in the driver can ameliorate the problem, and I agree with
that.  But that makes it the job of every driver to implement some
sort of cache, which IMHO isn't a very reasonable position.  When
there's a common need that affects users of many different programming
languages, the server should make it easy to meet that need, not
require every driver to implement query caching.

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

Robert Haas <robertmhaas@gmail.com> writes:
> Sure, but I don't want the application to have to know about that, and
> I don't really think the driver should need to know about that either.
> Your point, as I understand it, is that sufficiently good query
> caching in the driver can ameliorate the problem, and I agree with
> that.

I don't, actually.  If a particular application has a query mix that gets
a good win from caching query plans, it should already be using prepared
statements.  The fact that that's not a particularly popular thing to do
isn't simply because people are lazy, it's because they've found out that
it isn't worth the trouble for them.  Putting query caching logic into
drivers isn't going to improve performance for such cases, and it could
very possibly make things worse.  The driver is the place with the
absolute least leverage for implementing caching; it has no visibility
into either the application or the server.
        regards, tom lane

On Wed, Aug 10, 2016 at 11:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Sure, but I don't want the application to have to know about that, and
>> I don't really think the driver should need to know about that either.
>> Your point, as I understand it, is that sufficiently good query
>> caching in the driver can ameliorate the problem, and I agree with
>> that.
>
> I don't, actually.  If a particular application has a query mix that gets
> a good win from caching query plans, it should already be using prepared
> statements.  The fact that that's not a particularly popular thing to do
> isn't simply because people are lazy, it's because they've found out that
> it isn't worth the trouble for them.  Putting query caching logic into
> drivers isn't going to improve performance for such cases, and it could
> very possibly make things worse.  The driver is the place with the
> absolute least leverage for implementing caching; it has no visibility
> into either the application or the server.

I didn't mean to imply, in any way, that it is an ideal solution -
just that people use it successfully.

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

* Vladimir Sitnikov (sitnikov.vladimir@gmail.com) wrote:
> It works completely transparent to the application, and it does use
> server-prepared statements even though application builds "brand new" sql
> text every time.

And is the source of frequent complaints on various mailing lists along
the lines of "why did my query suddently get slow the 6th time it was
run?!".

I encourage you to look through the archives and read up on how and why
that can happen.

Thanks!

Stephen

* Vladimir Sitnikov (sitnikov.vladimir@gmail.com) wrote:
> 3) "suddently get slow the 6th time" is a PostgreSQL bug that both fails to
> estimate cardinality properly, and it does not provide administrator a way
> to disable the feature (generic vs specific plan).

Dropping and recreating the prepared statement is how that particular
issue is addressed.

> Query cache does have very good results for the overall web page times, and
> problems like "6th execution" are not that often.

While it may have good results in many cases, it's not accurate to say
that using prepared statements will always be faster than not.

Thanks!

Stephen

> Shay>I don't know much about the Java world, but both pgbouncer and pgpool
> (the major pools?)
> 
> In Java world, https://github.com/brettwooldridge/HikariCP is a very good
> connection pool.
> Neither pgbouncer nor pgpool is required.
> The architecture is:  application <=> HikariCP <=> pgjdbc <=> PostgreSQL
> 
> The idea is HikariCP pools pgjdbc connections, and server-prepared
> statements are per-pgjdbc connection, so there is no reason to "discard
> all" or "deallocate all" or whatever.

Interesting. What would happen if a user changes some of GUC
parameters? Subsequent session accidentally inherits the changed GUC
parameter?

> Shay>send DISCARD ALL by default. That is a fact, and it has nothing to do
> with any bugs or issues pgbouncer may have.

That is correct for pgpool as well.

> That is configurable. If pgbouncer/pgpool defaults to "wrong
> configuration", why should we (driver developers, backend developers) try
> to accommodate that?

There's nothing wrong with DICARD ALL. It's just not suitable for your
program (and HikariCP?).

I don't know about pgbouncer but pgpool has been created for a general
purpose connection pooler, which means it must behave as much as
similarly to PostgreSQL backend from frontend's point of
view. "DISCARD ALL" is needed to simulate the behavior of backend: it
discards all properties set by a frontend including prepared
statements when a session terminates. "DISCARD ALL" is perfect for
this goal.

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

> Tatsuo>Interesting. What would happen if a user changes some of GUC
> parameters? Subsequent session accidentally inherits the changed GUC
> parameter?
> 
> Yes, that is what happens.

Ouch.

> The idea is not to mess with gucs.
> 
> Tatsuo>There's nothing wrong with DICARD ALL
> Tatsuo>"DISCARD ALL" is perfect for this goal.
> 
> It looks like you mean: "let's reset the connection state just in case".
> I see where it might help: e.g. when providing database access to random
> people who might screw a connection in every possible way.

Yes, that's what clients of pgpool is expecting. Clients do not want
to change their applications which were working with PostgreSQL
without pgpool.

> Just in case: do you think one should reset CPU caches, OS filesystem
> caches, DNS caches, bounce the application, and bounce the OS in addition
> to "discard all"?

Aparently no, because that is different from what PostgreSQL is doing
when backend exits.

> Why reset only "connection properties" when we can reset everything to its
> pristine state?

You can propose that kind of variants of DISCARD command. PostgreSQL
is an open source project.

> Just in case: PostgreSQL does not execute "discard all" on its own.
> If you mean "pgpool is exactly like reconnect to postgresql but faster
> since connection is already established", then OK, that might work in some
> cases (e.g. when response times/throughput are not important), however why
> forcing "you must always start from scratch" execution model?

I'm not doing that. I do not ask all the people to use pgpool. People
can choose whatever tools he/she thinks suitable for their purpose.

> Developers are not that dumb, and they can understand that "changing gucs
> at random is bad".
> 
> When a connection pool is dedicated to a particular application (or a set
> of applications), then it makes sense to reuse statement cache for
> performance reasons.
> Of course, it requires some discipline like "don't mess with gucs", however
> that is acceptable and it is easily understandable by developers.

I'm not against such a developer's way. If you like it, you can do
it. Nobody disturbs you. I just want to say that not all the client
want that way.

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

Shay> What? I really didn't understand your point here. All the doc is saying is
Shay> that if the driver doesn't support prepared statements, then using them

Please read again. PreparedStatement is the only way to execute statements
in JDBC API. There's no API that allows user to specify "use
server-prepared here".
Well, there's non-prepared API in JDBC, however it misses "bind
variables" support,
so if bind variables required, developer would use PreparedStatement.

Java's PreparedStatement does not have an option to distinguish which statements
should be server-prepared and which should not.

Vladimir>> My experience shows, that people are very bad at predicting where the
Vladimir>> performance problem would be.
Vladimir>> For 80% (or even 99%) of the cases, they just do not care
thinking if a
Vladimir>> particular statement should be server-prepared or not.
Vladimir>> They have absolutely no idea how much resources it would
take and so on.
Shay> Maybe you're even right saying these things, I don't know. But
that doesn't
Shay> mean I as a driver should solve their problems for them. And I also get
Shay> that you have an additional argument here besides programmer
Shay> laziness/stupidity - the ORM argument - which makes more sense.

Suppose backend can handle 20 server-prepared statements at most (if using more
it would run out of memory).
Suppose an application has 100 statements with ".prepare()" call.
I think it is reasonable for the DB driver to figure out which
statements are most important
and server-prepare just "20 most important ones", and leave the rest
80 as regular
non-prepared statements.

Do you think the DB driver should just follow developer's advice and
server-prepare
all the 100 statements causing backend crash?
Do you think application developer should have a single list of all
the statements ever
used in the application and make sure there's no more than 20 queries in it?

My main point is not "developers are stupid", but "people often have
wrong guess when
it comes to performance". There are too many moving parts, so it is
hard to predict
performance implications.

Often it is much easier to execute a series of benchmarks that
validate certain hypothesis.
For instance, as Tatsuo says, savepoint overhead for DML is higher
than savepoint
overhead for SELECT, so I plan to have that benchmark as well.


Shay> First, there's nothing stopping an ORM from optimizing multiple inserts
Shay> into a single multivalue insert. I do admit I'm not aware of any who do
Shay> this, but it's a good idea for an optimization - I happen to maintain the
Shay> Entity Framework Core provider for Npgsql, I might take a look at this
Shay> optimization (so again thanks for the idea).

Nothings stops, but M framework times N database drivers results in M*N effort
for each feature.
As you say: application should just use batch API, and it's driver's
job to convert
that into suitable for the database sequence of bytes.

Same for Npgsql: if you implement rewrite at Npgsql level, that would
automatically
improve all the framework/applications running on top of Npgsql.


Shay> I'm going to repeat what I said
Shay> before and it would be good to get some reaction to this. Every software
Shay> component in the stack has a role, and maintaining those separations is
Shay> what keeps things simple and sane

You might be missing my comments on CPU, x86, etc.
My reaction is: almost every existing component is extremely hard to
reason about.

For instance: CPU has certain number of registers, it has certain
amount of L1/L2/...
caches and so on.
Do you mean each and every developer should explicitly specify which
program variable should use register and which one should go into L2 cache?

This is a counter-example to your "sane" "separation". CPU is free to reorder
instruction stream as long as the net result complies to the specification.
In the same way, CPU is free to use L1/L2 caches in whatever way it
thinks is the best.
Note: typical DB driver developer does not try to maintain a set of
"optimal assembly
instructions".
Driver developer relies on the compiler and the CPU so they would optimize
driver's code into the best machine code.

Of course driver might have inline assembly, but that is not how
mainstream drivers are written.

Another example: TCP stack. When DB driver sends some data, kernel is
free to reorder
packets, it is free to interleave, delay them, or even send even use
multiple network cards
to send a single TCP stream.
Windows 10 includes several performance improvements to the TCP stack,
and it is nowhere near
to "kernel is doing exactly what application/driver coded".
Once again: application/driver developer does not optimize for a
specific hardware (e.g. network card).
Developers just use common API and it is kernel's job to use best
optimizations for the particular HW.

The same goes to ORM-DB combo. ORM uses DB driver's API, and it's
drivers job to use
optimal command sequence for the specific database.
Note: of course ORM might use vendor specific features for a good
reason, however
simple cases like "batch insert" are both common, and they are not top
priority from "ORM developer"
perspective.

The same goes for application-driver-DB. Application just uses
driver's API, and it is not application's
business which bytes to send over the wire.
Neither application should specify which particular packets to use.

Note: I do not suggest to rewite SELECTs at the driver level yet. I do
not know a common pattern there.
For "insert multivalues" the pattern is simple: user sends insert
statements via batch API. It happens
quite often as it is the standard way of inserting data in bulks. It
is recommended in all the books and
articles on JDBC API. Not all the databases support multivaleus, so a
cross-database application
would likely just use "insert single values through batch API" and
rely on the DB driver and database to
do the right thing.


Of course having that fix at network stack level would automatically
improve all the postgresql drivers, however
1) It is NOT possible since multivalues have different SEMANTICS. When
sending indivisual inserts, each
individual response would have "number of rows inserted" field. When
using multivalues insert, there will be
just one insert with the total number of rows inserted.

If application logic did expect separate row counts, then kernel stack
is in trouble: it cannot recover
individual counts out of a combined response.

That is why multivalues rewrite at TCP level is impossible.

So, PostgreSQL network protocol v3 prohibits kernel from doing
automatic multivalues optimization.

Let me show you how JDBC API enables PgJDBC to do that optimization:
when application is using JDBC
batch API, it does receive "per statement row count" as a batch
result. However, there's a special result
code that says "statement did succeed, but the row count is not
known". Developers are prepared that
DB driver can return "not known" for all the rows, so PgJDBC is free o
use multivalues or copy or whatever
makes most sense for a particular batch.

2) There are other implications of doing that optimization at kernel
level (e.g. overhead for mysql users), but
since "the optimization is not possible due to semantics", other
reasons are not important.

Shay> Or if the magic is implemented at the
Shay> driver leve, it should be opt-in, or at least easy to disable entirely.

"Optimizations in the CPU should be opt-in or at least easy to
disable". Does that sound right to you?
For debugging purposes, it could make sense. However, for regular
production usage, optimizations
should be enabled by default. Regular Intel CPUs come with
optimizations enabled by default.

Multivalues rewrite does comply with JDBC batch API, so it makes no
surprise even if the optimization is
enabled by default.

Shay> So the
Shay> several-microsecond difference translates to a 6% performance degradation.
Shay> This is probably due to the slowness of processing the extra Bind/Execute
Shay> pair for the savepoint, which maybe can be improved in PostgreSQL.
Shay> Regardless, you may say there's no business case or that SELECT 1 against
Shay> localhost means nothing, or that posting data is somehow wrong,
but I don't
Shay> find this very acceptable.

In this particular case the purpose is to measure the overhead. In
your particular case
savepoint seems to cost 15us. It costs 3us for me, but that does not
matter much.
If each and every query is prefixed with a savepoint, then it will be
like 15ms overhead
for 1000 queries.

I think if application issues 1000 queries, it would anyway spend much more time
on processing the results and figuring out "which query to issue/which
values to pass".

However, that voltmeter is broken as Tatsuo have pointed out. He
chimed in a pgdbc issue tracker
and pointed out that "savepoint-DML-savepoint-DML-..." scenario would
allocate extra xid for each
savepoint. The more the xid allocation rate, the harder for vacuum the case is.

So we need to measure
1) "savepoint-DML-savepoint-DML-..." in order to find "savepoint
overhead in case of DML".
Note: pgjdbc's autosave=conservative would not issue savepoint before
regular DMLs
as DML cannot fail with "cached plan ... result type" error. So
"DML-select-DML-select"
scenario is required.
2) "real life applications" in order to find how extra savepoints
affect xid allocation rate.

Vladimir

Vladimir>> Yes, that is what happens.
Vladimir>> The idea is not to mess with gucs.

Shay:> Wow... That is... insane...

Someone might say that "programming languages that enable side-effects
are insane".
Lots of connection pools work by sharing the connections and it is up
to developer
if he can behave well (see "It is not" below)

Shay> it's entirely reasonable for
Shay> more than one app to use use the same pool

Sharing connections between different applications might be not that good idea.
However, I would not agree that "having out-of-process connection
pool" is the only sane
way to go.
I do admit there might be valid cases for out of process pooling,
however I do not agree
it is the only way to go. Not only "inprocess" is one of the ways,
"in-process" way is wildly used
in at least one of the top enterprise languages.

If you agree with that, you might agree that "in-process connection
pool that serves
exactly one application" might work in a reasonable fashion even
without DISCARD ALL.

Shay> Even with in-process pools it's standard practice (and a good idea) to
Shay> reset state.

It is not. Can you quote where did you get that "standard practice is
to reset state" from?

Oracle Weblogic application server does not reset connections.
JBoss WildFly application server does not reset connections.
HikariCP connection pool does not reset connections.

I can easily continue the list.
The above are heavily used java servers (Hikari is a pool to be exact).

Shay> If some part of a big complex
Shay> application modified state, and then some other part happens to get that
Shay> physical connection, it's extremely hard to make sense of things.

Let's not go into "functional programming" vs "imperative programming"
discussion?
Of course you might argue that "programming in Haskell or OCaml or F#" makes
"extremely easy to make sense of things", but that's completely
another discussion.

Shay> One note - in Npgsql's implementation of persistent prepared statements,
Shay> instead of sending DISCARD ALL Npgsql sends the commands listed in
Shay> https://www.postgresql.org/docs/current/static/sql-discard.html,
except for
Shay> DEALLOCATE ALL. This cleans up state changes but leaves prepared
statements
Shay> in place.

Ok. At least you consider that "always discarding all the state" might be bad.


Shay> This is somewhat similar to the CPU reordering you
Shay> keep coming back to - it's totally invisible

I would disagree. CPU reordering is easily visible if you are dealing
with multithreaded case.
It can easily result in application bugs if application misses some
synchronization.

CPU reordering is very visible to regular programmers, and it is a compromise:
1) Developers enable compiler and CPU do certain "reorderings"
2) Developers agree to follow the rules like "missing synchronization
might screw things up"
3) In the result, the code gets executed faster.


Vladimir> Just in case: PostgreSQL does not execute "discard all" on its own.

Shay> Of course it doesn't - it doesn't know anything about connection pooling,
Shay> it only knows about physical connections. When would it execute "discard
Shay> all" on its own?

That my point was for "pgpool aiming to look like a regular postgresql
connection".
The point was: "postgresql does not discard on its own, so pgpool
should not discard".


Shay> To enforce isolation, which is maybe the most important way for
programs to
Shay> be reliable - but this is a general theme which you don't seem to agree
Shay> with.

If you want to isolate something, you might better have a
per-application connection pool.
That way, if a particular application consumes all the connections, it
would not impact
other applications. If all the applications use the same
out-of-process pool, there might
be trouble of resource hogging.

Shay> Regardless, resetting state doesn't have to have a necessary effect
Shay> on response times/throughput.

Even if you do not reset prepared statements, "reset query" takes time.
For instance: there's a common problem to "validate connections before use".
That is the pooler should ensure the connection is working before handling it
to the application.
Both Weblogic server, and HikariCP have those connection validation built in
and the validation is enabled by default.

However, it turns out that "connection validation" takes too much time,
it is visible in the application response times, etc, so they both implement a
grace period. That is "if the connection was recently used, it is
assumed to be fine".
Weblogic trusts 15 seconds by default, so if you borrow connections
each 10 seconds, then
they will not be tested.
Well, there's additional background validation, but my main point is
"even select 1"
is visible on the application response times.



Shay> This is something new - maybe it's part of the misunderstanding here. To
Shay> me, the term "prepared statements" always means "server-prepared
Shay> statements"; this seems to be supported by the documentation you
quote: "If
Shay> the driver supports precompilation, the method prepareStatement will send
Shay> the statement to the database for precompilation". I don't understand the
Shay> concept of a prepared statements which isn't server-prepared - do you have
Shay> some sort of driver-only prepared statements, which

The concept is "the implementation of PreparedStatement interface is
free to chose
how it will execute the queries". It can go with "server-prepare on
each execution",
it can go with "cache server-prepared statements", etc, etc.
The whole purpose of having that "vague API" is to enable database vendors to
make most sense of their databases.

Shay> Regardless of any optimizations you may be doing, in every database driver
Shay> I've ever seen in my life, "prepared" simply means "server-prepared". And
Shay> in every driver I've ever seen, there's an explicit API for
that. Therefore
Shay> server-prepare is something that's exposed to the developer, rather than
Shay> some internal detail left to the driver.

Please, take CPU example (or TCP example) seriously. Seriously.
CPU did not always had a L2 cache. L2 was invented to improve the performance
of existing and future applications.

The same applies to "prepared statement cache at the database driver level".
It is implemented in pgjdbc to improve the performance of existing and
future applications.


Vladimir>> Suppose backend can handle 20 server-prepared statements at most (if
Vladimir> using more it would run out of memory).
Vladimir>> Suppose an application has 100 statements with ".prepare()" call.
Vladimir>> I think it is reasonable for the DB driver to figure out
which statements
Vladimir> are most important and server-prepare just "20 most
important ones", > and
Vladimir> leave the rest 80 as regular non-prepared statements.

Shay> I seriously don't find it reasonable at all.

Would you please answer to "should db driver silently server-prepare
all the 100 statements
and crash the DB" question?


Shay> Each connection fills
Shay> the cache, ejecting the other component's prepared statements from the
Shay> cache. As we switch between the two components, statements have to be
Shay> reprepared.

This is exactly what happens when several applications use the same CPU.
L2/L3 can be shared between cores, so if one cares on the performance of
a particular application, he should isolate the critical task to its
own CPU (or set of cores).

The same principle applies to connection pool.
Either multiple pools should be used, or cache size increased, or more
sophisticated algorithms
should be used to provide better hit rate.


Shay> Manual work here would allow picking truly hottest statements from each
Shay> app/component, and preparing those - allowing the hottest
statements across
Shay> both components to always remain live.
Shay> This is exactly what is meant by
Shay> "programmer knowledge" which the driver doesn't have - it only has its
Shay> recently-used logic which sometimes breaks down.

Let me exaggerate a bit.
Compiler optimizations sometimes break down (they produce not that
well performing code),
so all the optimizations should be disabled by default, and every
developer should
manually examine each line of code, identify hottest statements from each
app/component and assign variables to CPU registers.
This is exactly "programmer knowledge" which the compiler doesn't have.

Does it sound good to you?

Come on. Suppose you are writing a framework (ORM or whatever) that
happens to execute queries into the database.
Should that API have a special parameter "should_use_server_prepared"
for each and every method?
How should framework developer tell which statements should be
server-prepared and which should not?
Should framework use server-prepared statements at all?

What if framework should support PostgreSQL, MySQL, and Oracle?
Should there be three parameters?

Just the same in another direction: can you show some github library
that allows user (the developer) to control which statements should be
server-prepared and which should not?

Shay> You keep making this mistaken analogy... Caches (and CPU reordering) are
Shay> invisible, prepared statements aren't - they can trigger bugs (because of
Shay> DDL),

Note: "the cache of server-prepared statements" is invisible to the end user.
Well, when it comes to DDL it might trigger "PostgreSQL's
not_implemented" issues,
however those cases are not often, and those cases can be solved (in
no particular order):
1) For instance, it can be solved by manual intervention: reset the
connection pool
after DDL.

2) It can be solved by "automatic savepointing" at the driver level.

3) It can be solved by fixing "not_implemented" at the database level.
For instance, if "cached plan must not change state" error did not
kill transaction,
driver would be able just resend the full parse/describe/bind sequence and avoid
throwing "cached plan" at user.

4) I'm sure there are other solutions. They might be even better than
all the three above,
but the point is reusing server-prepared statements is a way to go, so let's try
fixing the database/protocol instead of reverting to "each connection
should be taken through DISCARD ALL"


Shay> and much more importantly, they take up resources on the PostgreSQL
Shay> side. Period. The L2 cache is already there and takes no additional
Shay> resources whether you use it or not. It's invisible.

CPU caches take resources as well.
L2 cache drains power, it costs money to produce. CPUs without L2/L3
would be cheaper.

"it takes resources" does not justify that "the feature must not exist".

"statement cache" is not completely invisible partially due to current
database limitations,
and partially due to "strange maintenance techniques end-users are using".

Vladimir

Shay> your analogy breaks down. Of course L2 was invented to improve
performance,
Shay> but that doesn't mean that all caches are the same. More precisely, what I
Shay> find objectionable about your approach is not any caching - it's the
Shay> implicit or automatic preparation of statements. This practice isn't
Shay> invisible in that a) it may cause errors that wouldn't have been there
Shay> otherwise (e.g. because of DDL),

Long-lived named server-prepared statements cause problems even if
server-prepared statements are created manually by developers.

Could you please stop saying "automatic preparation causes ~DDL issues"?

Those errors are not inherent to "automatic preparation of statements"
Those are just database defects that need to be cured.

Automatic savepointing is just a workaround for current DB limitation, and
it provides users with a simplified migration path.


Please, don't try to tell me that "IDbCommand.Prepare()" documentation says
that "prepared statement might fail for no reason just because it is prepared".


Shay> As I said above, I think this is a critical point of misunderstand between
Shay> us. The developers tells the driver which statements should be
Shay> server-prepared by calling .prepareStatement(). I'm guessing you have a
Shay> totally different understanding here.

Please, quote the document you got that "developers tell the driver which
statements should be server-prepared by calling ..." from. It never
works like that.
Neither in Java, nor in C#. I would admit I've no C# experience, but I did
find documentation on IDbCommand.Prepare() and examined it.

The proper way to say is "by calling .Prepare() developer passes the
intention that
he might be using the same query multiple times".
That is it. It never means "driver must absolutely use server-prepare
in the response
to .Prepare() call".

The same goes for Java's PreparedStatement.
It never means "the driver must use server-prepared features".

As Microsoft lists in the .Prepare() documentation, modern versions of
MSSQL just ignore .Prepare() and cache statements automatically.

It is not a developer's business which statements should be in the
database cache.
Neither developer should care which statements reside in the driver cache.


Shay> What exactly does "server-prepare on each execution" means? Sending Parse
Shay> on each execution? How can that be considered prepared at all?

Remember, java.sql.PreparedStatement interface is NOT bound to PostgreSQL in any
manner. It is a generic database API.
Thus the word "prepared" does not mean anything specific there.
It gives no promise whether the statement will use "parseOnce,
execMany" PostgreSQL's
feature or not.

A compliant implementation (that is a driver) could just assemble full SQL
by concatenating the parameters on each execution and send it via 'Q' simple
execute message.


Shay> Does pgjdbc consider
Shay> something "prepared" without it being the 2nd option above? Note that I'm
Shay> genuinely interested in case I'm missing something.

Currently the first 5 executions of PreparedStatement use unnamed
statements (Parse/Bind/Exec).
Then pgjdbc assigns a name and uses just Bind/Exec.

So if a particular SQL is rare, then it would not get its own
server-prepared name
even though it is "java.sql.PreparedStatement".

What pgjdbc does is it picks the most used queries and enables them to be cached
at the database level.


Vladimir>> app/component and assign variables to CPU registers.
Vladimir>> This is exactly "programmer knowledge" which the compiler
doesn't have.
Vladimir>> Does it sound good to you?

Shay> Of course not. But I don't think it's a very valid analogy.

The analogy was not supposed to play in a way
you twisted it with ORM=Java, driver=C, etc.

Here's more detailed explanation:

1) You claim that programmers should manually examine all the SQL statements,
and put ".prepare()" call if and only if the specific SQL should be
server-prepared.

2) My analogy: programmers should manually examine all the variables
(think of C#
variables, or Java variables, or C variables, it does not matter),
and assign which variables should use CPU registers, and which ones should
go into the memory.

Named server-prepared statements == CPU registers
SQL statements in the code == variables in the code (e.g. C# variables)

That is very valid analogy. What you say is "programmer has full visibility over
the meaning of the code, thus it knows better which statements should be
server-prepared and which should not".

Well, register allocation is a bit harder problem that "statement name
allocation",
but the essence is the same: there's limited number of registers/named
statements,
so someone (or something) should decide which statements deserve a name.

Just in case: you definitely know what CPU registers are and what is
"register allocation" problem, don't you?

You seem to pick up that "application developer != compiler engineer", however
then you slipped into "nevertheless application developer should
decide if each SQL
should be server-prepared on a case-by-case basis".

In ideal world that might work. However, that does not work in practice:

0) Microsoft's documentation on ".Prepare()" says "prepare call does not have
any effect since sql server optimizes the statements automatically".
So applications ported from MSSQL might miss ".Prepare()" altogether,
thus it will
under-perform and blame PostgreSQL for no reason.
Does it sound fair? I do not think so.

Do you see lots of complaints for "MSSQL doing automatic statement caching"?
I'm not into MSSQL, but I know Oracle does automatic statement caching as well,
and no one ever raises questions like "my precious statement was evicted".

1) What if the database RAM is increased, so the application can
".prepare()" not 20, but
2000 statements?
Should developer add more ".prepare()" calls and recompile the application
just to accommodate RAM increase? That sounds crazy. I think the way
to go should be
just increase "statement cache size".

2) Do you really expect each project to have a list of all the used SQL along
with "should server-prepare" flags?

3) What if the application is composed of 5 components? How should one split
the budget of server-prepared statements? (remember, we cannot prepare
all of them
otherwise the DB would crash)

4) Database driver does see all the SQLs executed within a connection.
Of course, driver's automatic cache cannot beat "ideal pick", however
that automatic statement can easily be "just fine" and
automatic caching saves enormous amount of development time.

Remember, C# compiler often is "just fine" at CPU register allocation,
and you don't need to control how the code is actually executed.

That spare time would better be spent on SQL tuning (that is "explain
analyze...")
rather than deciding which statements should be server-prepared and
which should not.

5) When porting from other database, the application might already be full of
"con.prepareStatement(String sql)" calls. As I said, there is NO
NonPreparedStatement
in Java.
Does that mean an application ported from say Oracle should crash when running
against PostgreSQL? That does not sound right.



Shay> Whereas it's totally
Shay> unreasonable to expect programmers to do the work of the compiler
Shay> optimizer, it seems really reasonable to ask them to think about server
Shay> preparation, and even about multivalue inserts (at least if performance is
Shay> important in the application).

a) They don't even know that multivalues in PostgreSQL gives such a boost
b) By the time all the ORMs implement that feature, PostgreSQL might
fix that defect
so the optimization will become obsolete.

Once again: I do not suggest rewriting SELECTs on the fly yet.
What I suggest is to decide "which are the most important SQLs and which of them
deserve their own server-prepared name" at the driver level.

Remember: it is very close to what compiler is doing. It assigns
registers automatically
based on some heuristics. For typical applications it is just fine.
No-one is trying to


Shay> and b) it imposes a resource drain on the
Shay> server. The second point is very important: the L2 cache doesn't impose a
Shay> resource drain on anyone - it's just there, speeding up your
application. I
Shay> hope that point makes it across - it's why I don't accept your analogy.

Oh. You might be not into a chip design either, that is why that
analogy plays so bad.
Just in case: if L2 cache was just there, and if L2 cache didn't
impose a resource
drain, why don't we have 1GiB L2 caches?

Of course L2 drains lots of resources:
1) It drains money, as L2 costs valuable on-die space
2) L2 drains power, that is naturally drains your battery
Of course there are good reasons to have L2 even though it drains resources, but
the statement of "it is just there for free" is plain wrong.


Shay> I'm still
Shay> not sure if it should be opt-in or default, although I admit I'm leaning
Shay> towards default. But that feature has very little to do with *implicit*
Shay> preparation.



Shay> The point is that AFAIK the same bugs that can result from reordering can
Shay> also result from other basic conditions as well. If you're writing
Shay> multithreaded code then you must handle synchronization - this is not a
Shay> reordering-specific problem. Therefore if your program is
multithreaded but
Shay> doesn't do proper synchronization you have a bug - regardless of whether
Shay> its manifestation is triggered by CPU reordering or not. I admit
I'm not an
Shay> expert on this and may be wrong (it would be interesting to know).


Regarding "regardless...by CPU or not", you are off.
I've listed a link to a concurrency bug in Linux kernel.
It manifested itself only on some recent Intel CPUs.
In other words, the kernel was fine otherwise, but if running modern
CPU, it might hang.
Of course it was bug in the kernel, but the trigger condition was
"smarter CPU" that
was able to do more "reorderings" that were possible with older ones.


There are good articles by Alexey Shipilev where he describes what are
the typical
concurrency problems, and why do compiler engineers allow them to appear.
The articles are java-related, however I think they are good read anyway:
https://shipilev.net/blog/2014/jmm-pragmatics
https://shipilev.net/blog/2016/close-encounters-of-jmm-kind


Shay> It does only if you do it in a roundtrip of its own. When you close a
Shay> pooled connection in Npgsql, the reset query is written to an internal
Shay> buffer but not sent. The first query that actually gets sent by the user
Shay> after opening will therefore have the reset query prepended to it
Shay> (basically the reset query is batched)

That is clever.
Is it something specific to Npgsql pool? Does it work with all poolers
that operate
on top of Npgsql connections?


Vladimir

On 2016-07-31 17:57:12 -0400, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > FWIW, I've observed the same with (a bit) more complicated queries. A
> > part of this is that the extended protocol simply does
> > more. PQsendQueryGuts() sends Parse/Bind/Describe/Execute/Sync - that's
> > simply more work and data over the wire than a single Q message.
> 
> Yeah.  The extended query protocol was designed to offer a lot of
> functionality that people had asked for, like plan re-use and
> introspection of the data types assigned to query parameters, but that
> doesn't come at zero cost.  I think the tie-in to the plan cache is a
> significant part of the added overhead, and so is the fact that we have to
> iterate the per-message loop in PostgresMain five times not once, with
> overheads like updating the process title incurred several times in that.

One approach to solving this, without changing the protocol, would be to
"fuse" parse/bind/execute/sync together, by peeking ahead in the
protocol stream. When that combination is seen looking ahead (without
blocking), optimize it by handing it to something closer to
exec_simple_query() which also handles parameters.  Even if we don't
recognize that pattern everytime, e.g. because later messages are in
different, not yet arrived, tcp packets, that'd speed up the common
case.  As our client socket is nearly always is in non-blocking mode
these days, that shouldn't be too expensive.


Not that that analogy is fitting perfectl;y, but the above approach
seems to work quite well on the CPU level ("macro op fusion"), to
increase execution throughput...


Andres

Andres Freund <andres@anarazel.de> writes:
> On 2016-07-31 17:57:12 -0400, Tom Lane wrote:
>> Yeah.  The extended query protocol was designed to offer a lot of
>> functionality that people had asked for, like plan re-use and
>> introspection of the data types assigned to query parameters, but that
>> doesn't come at zero cost.  I think the tie-in to the plan cache is a
>> significant part of the added overhead, and so is the fact that we have to
>> iterate the per-message loop in PostgresMain five times not once, with
>> overheads like updating the process title incurred several times in that.

> One approach to solving this, without changing the protocol, would be to
> "fuse" parse/bind/execute/sync together, by peeking ahead in the
> protocol stream.

I do not think that would move the needle noticeably, because we'd still
have to do basically all the same work, due to not knowing whether the
statement is going to be used over again.  If we'd specified that the
unnamed statement could be used only once, and that the unnamed portal
had to be executed to completion on first use, there would be more room
for optimization.  The joys of hindsight :-(
        regards, tom lane

On 2016-08-16 21:40:32 -0400, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > On 2016-07-31 17:57:12 -0400, Tom Lane wrote:
> >> Yeah.  The extended query protocol was designed to offer a lot of
> >> functionality that people had asked for, like plan re-use and
> >> introspection of the data types assigned to query parameters, but that
> >> doesn't come at zero cost.  I think the tie-in to the plan cache is a
> >> significant part of the added overhead, and so is the fact that we have to
> >> iterate the per-message loop in PostgresMain five times not once, with
> >> overheads like updating the process title incurred several times in that.
> 
> > One approach to solving this, without changing the protocol, would be to
> > "fuse" parse/bind/execute/sync together, by peeking ahead in the
> > protocol stream.
> 
> I do not think that would move the needle noticeably, because we'd still
> have to do basically all the same work, due to not knowing whether the
> statement is going to be used over again.  If we'd specified that the
> unnamed statement could be used only once, and that the unnamed portal
> had to be executed to completion on first use, there would be more room
> for optimization.  The joys of hindsight :-(

ISTM that with the current prepared statement search path behaviour
(i.e. we replan on relevant changes anyway), we can store the unnamed
statement's sql for that case.

BTW, there seem to be a room to enhance JDBC driver performance. In my
understanding it always uses unnamed portal even if the SQL is like
"BEGIN" or "COMMIT" (no parameters). They are too often used. Why not
doing like this?

Prepare(stmt=S1,query="BEGIN")
Bind(stmt=S1,portal=P1)
Execute(portal=P1)
:
:
Execute(portal=P1)
:
:
Execute(portal=P1)

Instead of:

Prepare(stmt=S1,query="BEGIN")
Bind(stmt=S1,portal=null)
Execute(portal=null)
:
:
Bind(stmt=s1,portal=null)
Execute(portal=null)
:
:
Bind(stmt=s1,portal=null)
Execute(portal=null)

This way, we could save bunch of Bind messages.

I don't know what other drivers do though.

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

> Tatsuo>understanding it always uses unnamed portal even if the SQL is like
> "BEGIN" or "COMMIT" (no parameters). They are too often used. Why not
> doing like this?
> 
> Does it actually work?
> 
> The documentation says named portals last till the end of the transaction:
> 
> https://www.postgresql.org/docs/9.5/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
> 
> 
> doc>If successfully created, a named portal object lasts till the end of
> the current transaction, unless explicitly destroyed

Oh, ok. I misunderstood that named portals survive beyond transaction
boundary.

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

On Tue, Aug 16, 2016 at 4:48 PM, Andres Freund <andres@anarazel.de> wrote:
> One approach to solving this, without changing the protocol, would be to
> "fuse" parse/bind/execute/sync together, by peeking ahead in the
> protocol stream. When that combination is seen looking ahead (without
> blocking), optimize it by handing it to something closer to
> exec_simple_query() which also handles parameters.  Even if we don't
> recognize that pattern everytime, e.g. because later messages are in
> different, not yet arrived, tcp packets, that'd speed up the common
> case.  As our client socket is nearly always is in non-blocking mode
> these days, that shouldn't be too expensive.

I think this could possibly be done, but it seems a lot better to me
to just bite the bullet and add a new protocol message.  That was
proposed by Tom Lane on July 31st and I think it's still by far the
best and easiest idea proposed, except I think we could introduce it
without waiting for a bigger rework of the protocol if we design the
libpq APIs carefully.  Most of the rest of this thread seems to have
devolved into an argument about whether this is really necessary,
which IMHO is a pretty silly argument, instead of focusing on how it
might be done, which I think would be a much more productive
conversation.

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

On 2016-08-23 11:42:53 -0400, Robert Haas wrote:
> I think this could possibly be done, but it seems a lot better to me
> to just bite the bullet and add a new protocol message.  That was
> proposed by Tom Lane on July 31st and I think it's still by far the
> best and easiest idea proposed, except I think we could introduce it
> without waiting for a bigger rework of the protocol if we design the
> libpq APIs carefully.  Most of the rest of this thread seems to have
> devolved into an argument about whether this is really necessary,
> which IMHO is a pretty silly argument, instead of focusing on how it
> might be done, which I think would be a much more productive
> conversation.

I agree about the odd course of the further discussion, especially the
tone was rather odd.  But I do think it's valuable to think about a path
that fixes the issue without requiring version-dependant adaptions in
all client drivers.

Greetings,

Andres Freund