Re: [HACKERS] Slowness of extended protocol

Just a note from me - I also agree this thread evolved (or rather devolved) in a rather unproductive and strange way. One important note that came out, though, is that adding a new client message does have a backwards compatibility issue - intelligent proxies such as pgbouncer/pgpool will

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

On Tue, Aug 16, 2016 at 4:48 PM, Andres Freund 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 >

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

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:

Re: [HACKERS] Slowness of extended protocol

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)

Re: [HACKERS] Slowness of extended protocol

On 2016-08-16 21:40:32 -0400, Tom Lane wrote: > Andres Freund 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

Re: [HACKERS] Slowness of extended protocol

Andres Freund 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

Re: [HACKERS] Slowness of extended protocol

On 2016-07-31 17:57:12 -0400, Tom Lane wrote: > Andres Freund 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 -

Re: [HACKERS] Slowness of extended protocol

Halfway through this mail I suddenly understood something, please read all the way down before responding... On Tue, Aug 16, 2016 at 2:16 PM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > Shay> your analogy breaks down. Of course L2 was invented to improve > performance, > Shay> but

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

> > I'm not going to respond to the part about dealing with prepared >> statements errors, since I think we've already covered that and there's >> nothing new being said. I don't find automatic savepointing acceptable, and >> a significant change of the PostgreSQL protocol to support this doesn't

Re: [HACKERS] Slowness of extended protocol

> > > I'm not going to respond to the part about dealing with prepared > statements errors, since I think we've already covered that and there's > nothing new being said. I don't find automatic savepointing acceptable, and > a significant change of the PostgreSQL protocol to support this doesn't >

Re: [HACKERS] Slowness of extended protocol

On Mon, Aug 15, 2016 at 3:16 PM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > 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

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

On Sat, Aug 13, 2016 at 11:20 PM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > 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. > The idea is not to

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

Apologies, I accidentally replied off-list, here's the response I sent. Vladimir, I suggest you reply to this message with your own response... On Sat, Aug 13, 2016 at 6:32 PM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > Shay>To be honest, the mere idea of having an SQL parser

Re: [HACKERS] Slowness of extended protocol

> 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

Re: [HACKERS] Slowness of extended protocol

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. The idea is not to mess with gucs. Tatsuo>There's nothing wrong with DICARD ALL Tatsuo>"DISCARD ALL" is perfect for this

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

Shay>To be honest, the mere idea of having an SQL parser inside my driver makes me shiver. Same for me. However I cannot wait for PostgreSQL 18 that does not need client-side parsing. Shay>We did, you just dismissed or ignored them Please prove me wrong, but I did provide a justified answer to

Re: [HACKERS] Slowness of extended protocol

Vladimir wrote: 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 <=>

Re: [HACKERS] Slowness of extended protocol

On 11 August 2016 at 10:18, Shay Rojansky wrote: > > > On Thu, Aug 11, 2016 at 1:22 PM, Vladimir Sitnikov < > sitnikov.vladi...@gmail.com> wrote: > > 2) The driver can use safepoints and autorollback to the good "right >> before failure" state in case of a known failure. Here's

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

On Thu, Aug 11, 2016 at 1:22 PM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: 2) The driver can use safepoints and autorollback to the good "right before > failure" state in case of a known failure. Here's the implementation: > https://github.com/pgjdbc/pgjdbc/pull/477 > As far as I

Re: [HACKERS] Slowness of extended protocol

Shay>As I said, an error is going to kill the ongoing transaction, how can this be solved without application logic? 1) At least, some well-defined error code should be created for that kind of matter. 2) The driver can use safepoints and autorollback to the good "right before failure" state in

Re: [HACKERS] Slowness of extended protocol

On Thu, Aug 11, 2016 at 8:39 AM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > Shay: > >> Prepared statements can have very visible effects apart from the speedup >> they provide (e.g. failure because of schema changes) It's not that >> these effects can't be worked around - they can

Re: [HACKERS] Slowness of extended protocol

Shay: > Prepared statements can have very visible effects apart from the speedup > they provide (e.g. failure because of schema changes) It's not that these > effects can't be worked around - they can be - but programmers can be > surprised by these effects, which can cause difficult-to-diagnose

Re: [HACKERS] Slowness of extended protocol

Vladimir wrote: Shay> As Tom said, if an application can benefit from preparing, the > developer has the responsibility (and also the best knowledge) to manage > preparation, not the driver. Magical behavior under the hood causes > surprises, hard-to-diagnose bugs etc. > > Why do you do C# then?

Re: [HACKERS] Slowness of extended protocol

Shay> it's important to note that query parsing and rewriting isn't an "inevitable evil". Ok, I stay corrected. ADO.NET have raw mode in the API. That's interesting. Let's say "lots of heavily used languages do have their own notion of bind placeholders". And for the reset, it is still not that

Re: [HACKERS] Slowness of extended protocol

Some comments... For the record, I do find implicit/transparent driver-level query preparation interesting and potentially useful, and have opened https://github.com/npgsql/npgsql/issues/1237 to think about it - mostly based on arguments on this thread. One big question mark I have is whether

Re: [HACKERS] Slowness of extended protocol

Stephen> 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. There's no silver bullet. <-- that is accurate, but it is useless for end-user applications I've never claimed that "server prepared statement" is a

Re: [HACKERS] Slowness of extended protocol

* Vladimir Sitnikov (sitnikov.vladi...@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

Re: [HACKERS] Slowness of extended protocol

Stephen>I encourage you to look through the archives The thing is pl/pgsql suffers from exactly the same problem. pl/pgsql is not a typical language of choice (e.g. see Tiobe index and alike), so the probability of running into "prepared statement issues" was low. As more languages would use

Re: [HACKERS] Slowness of extended protocol

Robert>But that makes it the job of every driver to implement some sort of cache, which IMHO isn't a very reasonable position Let's wait what Shay decides on implementing query cache in npgsql ? Here's the issue: https://github.com/npgsql/npgsql/issues/1237 He could change his mind when it

Re: [HACKERS] Slowness of extended protocol

* Vladimir Sitnikov (sitnikov.vladi...@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

Re: [HACKERS] Slowness of extended protocol

On Wed, Aug 10, 2016 at 11:50 AM, Tom Lane wrote: > Robert Haas 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

Re: [HACKERS] Slowness of extended protocol

Robert Haas 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

Re: [HACKERS] Slowness of extended protocol

On Tue, Aug 9, 2016 at 5:07 PM, Vladimir Sitnikov 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 >

Re: [HACKERS] Slowness of extended protocol

Robert Haas: > but for some reason you can't use prepared statements, for example because > the queries are dynamically generated and . That case is analogous to -M > extended, not -M prepared. And -M extended is well-known to be SLOWER > I do not buy that "dynamically generated queries defeat

Re: [HACKERS] Slowness of extended protocol

On Tue, Aug 9, 2016 at 4:50 AM, Vladimir Sitnikov 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

Re: [HACKERS] Slowness of extended protocol

On Tue, Aug 9, 2016 at 3:42 PM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > Shay>But here's the more important general point. We're driver > developers, not application developers. I don't really know what > performance is "just fine" for each of my users, and what is not worth >

Re: [HACKERS] Slowness of extended protocol

Shay>But here's the more important general point. We're driver developers, not application developers. I don't really know what performance is "just fine" for each of my users, and what is not worth optimizing further. Users may follow best practices, or they may not for various reasons. Of

Re: [HACKERS] Slowness of extended protocol

On Tue, Aug 9, 2016 at 8:50 AM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > Shay>There are many scenarios where connections are very short-lived > (think about webapps where a pooled connection is allocated per-request and > reset in between) > > Why the connection is reset in

Re: [HACKERS] Slowness of extended protocol

Shay>There are many scenarios where connections are very short-lived (think about webapps where a pooled connection is allocated per-request and reset in between) Why the connection is reset in between in the first place? In pgjdbc we do not reset per-connection statement cache, thus we easily

Re: [HACKERS] Slowness of extended protocol

On Mon, Aug 8, 2016 at 6:44 PM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > > The problem with "empty statement name" is statements with empty name can >> be reused (for instance, for batch insert executions), so the server side >> has to do a defensive copy (it cannot predict how

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

I'm sorry, we are discussing technical details with no real-life use case to cover that. I do not want to suck time for no reason. Please accept my sincere apologies for not asking the real-life case earlier. Shay, can you come up with a real-life use case when those "I claim the statement will

Re: [HACKERS] Slowness of extended protocol

Shay Rojansky : > 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. >

Re: [HACKERS] Slowness of extended protocol

Shay Rojansky 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

Re: [HACKERS] Slowness of extended protocol

Vladimir wrote: > 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. >>> >> >> Can you elaborate on what that means exactly? Are you

Re: [HACKERS] Slowness of extended protocol

Vladimir Sitnikov 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

Re: [HACKERS] Slowness of extended protocol

Shay Rojansky : > That's definitely a valid point. But do you think it's a strong enough > argument to avoid ever adding new messages? > 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

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

Shay Rojansky : > > That sounds right to me. As you say, the server version is sent early in > the startup phase, before any queries are sent to the backend, so frontends > know which server they're communicating with. > > We could call this "protocol 3.1" since it doesn't break

Re: [HACKERS] Slowness of extended protocol

On Sun, Aug 7, 2016 at 7:46 PM, Shay Rojansky 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. >

Re: [HACKERS] Slowness of extended protocol

On Sun, Aug 7, 2016 at 6:11 PM, Robert Haas wrote: > 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

Re: [HACKERS] Slowness of extended protocol

On Fri, Aug 5, 2016 at 8:07 PM, Shay Rojansky 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

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

On Tue, Aug 2, 2016 at 2:00 PM, Shay Rojansky 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,

Re: [HACKERS] Slowness of extended protocol

On Wed, Aug 3, 2016 at 7:35 PM, Bruce Momjian wrote: > On Wed, Aug 3, 2016 at 10:02:39AM -0400, Tom Lane wrote: >> Bruce Momjian 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

Re: [HACKERS] Slowness of extended protocol

On Wed, Aug 3, 2016 at 10:02:39AM -0400, Tom Lane wrote: > Bruce Momjian 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

Re: [HACKERS] Slowness of extended protocol

Tatsuo Ishii : > 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.

Re: [HACKERS] Slowness of extended protocol

Tom Lane : > Bruce Momjian 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 >

Re: [HACKERS] Slowness of extended protocol

Bruce Momjian 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

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

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.

Re: [HACKERS] Slowness of extended protocol

> > 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 > "Benchmark data is a perfect substitute for benchmarking results. Data is easy to misinterpret,

Re: [HACKERS] Slowness of extended protocol

On Mon, Aug 1, 2016 at 12:12 PM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > The attached patch passes `make check` and it gains 31221 -> 33547 improvement for "extended pgbench of SELECT 1". > > The same version gains 35682 in "simple" mode, and "prepared" mode achieves 46367 (just

Re: [HACKERS] Slowness of extended protocol

Greg 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

Re: [HACKERS] Slowness of extended protocol

Andres Freund 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

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] Slowness of extended protocol

On Sun, Jul 31, 2016 at 4:05 PM, Shay Rojansky 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

Re: [HACKERS] Slowness of extended protocol

> > Shay Rojansky : > >> 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

Re: [HACKERS] Slowness of extended protocol

Shay Rojansky : > 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

Re: [HACKERS] Slowness of extended protocol

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

Re: [HACKERS] 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 =

[HACKERS] 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