Re: track_planning causing performance regression

On Wed, Jul 7, 2021 at 8:57 PM Fujii Masao wrote: > > Pushed. Thanks! Thanks!

Re: track_planning causing performance regression

On 2021/07/07 18:09, Julien Rouhaud wrote: On Thu, Jul 1, 2021 at 4:28 PM Fujii Masao wrote: I'm fine with this. So what about the following diff? I added tag. pg_stat_statements.track_planning controls whether planning operations and duration are tracked by the module.

Re: track_planning causing performance regression

On Thu, Jul 1, 2021 at 4:28 PM Fujii Masao wrote: > > I'm fine with this. So what about the following diff? I added > tag. > > pg_stat_statements.track_planning controls whether > planning operations and duration are tracked by the module. > Enabling this parameter may in

Re: track_planning causing performance regression

On 2021/06/30 0:12, Julien Rouhaud wrote: Enabling this parameter may incur a noticeable performance penalty, - especially when a fewer kinds of queries are executed on many - concurrent connections. + especially when queries with identical structure are executed by many

Re: track_planning causing performance regression

On Tue, Jun 29, 2021 at 10:45 AM Justin Pryzby wrote: > > We borrowed that language from the previous text: > > | Plannable queries (that is, SELECT, INSERT, UPDATE, and DELETE) are > combined into a single pg_stat_statements entry whenever they have identical > query structures according to an

Re: track_planning causing performance regression

On Tue, Jun 29, 2021 at 10:29:43AM +0800, Julien Rouhaud wrote: > On Tue, Jun 29, 2021 at 10:09 AM Justin Pryzby wrote: > > Is "identical structure" really accurate here? For instance a multi > tenant application could rely on the search_path and only use > unqualified relation name. So while t

Re: track_planning causing performance regression

On Tue, Jun 29, 2021 at 10:09 AM Justin Pryzby wrote: > > Checking back - here's the latest patch. > > diff --git a/doc/src/sgml/pgstatstatements.sgml > b/doc/src/sgml/pgstatstatements.sgml > index 930081c429..9e98472c5c 100644 > --- a/doc/src/sgml/pgstatstatements.sgml > +++ b/doc/src/sgml/pgsta

Re: track_planning causing performance regression

On Wed, Apr 21, 2021 at 10:40:07AM -0500, Justin Pryzby wrote: > On Thu, Apr 22, 2021 at 12:13:17AM +0900, Fujii Masao wrote: > > On 2021/04/21 23:53, Justin Pryzby wrote: > > > Or: > > > > > > Enabling this parameter may incur a noticeable performance > > > penalty, > > > especia

Re: track_planning causing performance regression

On Thu, Apr 22, 2021 at 12:13:17AM +0900, Fujii Masao wrote: > On 2021/04/21 23:53, Justin Pryzby wrote: > > Or: > > > > Enabling this parameter may incur a noticeable performance penalty, > > especially similar queries are executed by many concurrent > > connections > > a

Re: track_planning causing performance regression

On 2021/04/21 23:53, Justin Pryzby wrote: Or: Enabling this parameter may incur a noticeable performance penalty, especially similar queries are executed by many concurrent connections and compete to update a small number of pg_stat_statements entries. I prefer this.

Re: track_planning causing performance regression

On Wed, Apr 21, 2021 at 11:38:52PM +0900, Fujii Masao wrote: > On 2021/04/19 23:55, Justin Pryzby wrote: > > What does "kind" mean ? I think it means a "normalized" query or a "query > > structure". > > > > "a fewer kinds" is wrong, so I think the docs should say "a small number of > > queries" o

Re: track_planning causing performance regression

On 2021/04/19 23:55, Justin Pryzby wrote: What does "kind" mean ? I think it means a "normalized" query or a "query structure". "a fewer kinds" is wrong, so I think the docs should say "a small number of queries" or maybe: Okay, I agree to update the description. Enabling this para

Re: track_planning causing performance regression

On Mon, Apr 19, 2021 at 11:44:05PM +0900, Fujii Masao wrote: > On 2021/04/19 8:36, Justin Pryzby wrote: > > Reviewing this change which was committed last year as > > 321fa6a4a26c9b649a0fbec9fc8b019f19e62289 > > > > On Fri, Jul 03, 2020 at 03:57:38PM +0900, Fujii Masao wrote: > > > On 2020/07/03 1

Re: track_planning causing performance regression

On 2021/04/19 8:36, Justin Pryzby wrote: Reviewing this change which was committed last year as 321fa6a4a26c9b649a0fbec9fc8b019f19e62289 On Fri, Jul 03, 2020 at 03:57:38PM +0900, Fujii Masao wrote: On 2020/07/03 13:05, Pavel Stehule wrote: pá 3. 7. 2020 v 4:39 odesílatel Fujii Masao napsa

Re: track_planning causing performance regression

Reviewing this change which was committed last year as 321fa6a4a26c9b649a0fbec9fc8b019f19e62289 On Fri, Jul 03, 2020 at 03:57:38PM +0900, Fujii Masao wrote: > On 2020/07/03 13:05, Pavel Stehule wrote: > > pá 3. 7. 2020 v 4:39 odesílatel Fujii Masao > > napsal: > > > > Maybe there can be documen

Re: track_planning causing performance regression

On Fri, Sep 11, 2020 at 03:32:54PM -0700, Andres Freund wrote: > The piece about a single shared lwlocks is/was about protecting the set > of entries that are currently in-memory - which can't easily be > implemented just using atomics (at least without the risk of increasing > the counters of an e

Re: track_planning causing performance regression

"Tharakan, Robins" writes: >> During fully-cached SELECT-only test using pgbench, Postgres v13Beta1 >> shows ~45% performance drop [2] at high DB connection counts (when >> compared with v12.3) > It'd be great if we could also give credit to "Sean Massey" for this find. I poked through my Postgr

Re: track_planning causing performance regression

Hi, On 2020-09-11 19:10:05 -0300, Alvaro Herrera wrote: > Andres suggested in [1] to use atomics for the counters together with a > single lwlock to be used in shared mode only. I didn't quite understand > what the lwlock is *for*, but maybe you do. > > [1] https://postgr.es/m/20200629231015.qle

Re: track_planning causing performance regression

On 2020-Sep-11, Fujii Masao wrote: > Ok, so my proposed patch degrated the performance in this case :( > This means that replacing spinlock with lwlock in pgss is not proper > approach for the lock contention issue on pgss... > > I proposed to split the spinlock for each pgss entry into two > to

Re: track_planning causing performance regression

On Fri, Sep 11, 2020 at 4:04 PM Fujii Masao wrote: > > On 2020/09/11 16:23, bttanakahbk wrote: > > > > pgbench: > > initialization: pgbench -i -s 100 > > benchmarking : pgbench -j16 -c128 -T180 -r -n -f

Re: track_planning causing performance regression

On 2020/09/11 16:23, bttanakahbk wrote: Hi, On 2020-08-19 00:43, Fujii Masao wrote: Yes, I pushed the document_overhead_by_track_planning.patch, but this CF entry is for pgss_lwlock_v1.patch which replaces spinlocks with lwlocks in pg_stat_statements. The latter patch has not been committed

Re: track_planning causing performance regression

Hi, On 2020-08-19 00:43, Fujii Masao wrote: Yes, I pushed the document_overhead_by_track_planning.patch, but this CF entry is for pgss_lwlock_v1.patch which replaces spinlocks with lwlocks in pg_stat_statements. The latter patch has not been committed yet. Probably attachding the different pat

Re: track_planning causing performance regression

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: not tested Spec compliant: not tested Documentation:not tested Overall, the patch works fine. However, I have a few observations: (1) C

Re: track_planning causing performance regression

On Tue, Aug 18, 2020 at 8:43 PM Fujii Masao wrote: > > > Yes, I pushed the document_overhead_by_track_planning.patch, but this > > CF entry is for pgss_lwlock_v1.patch which replaces spinlocks with > lwlocks > > in pg_stat_statements. The latter patch has not been committed yet. > > Probably atta

Re: track_planning causing performance regression

Yes, I pushed the document_overhead_by_track_planning.patch, but this CF entry is for pgss_lwlock_v1.patch which replaces spinlocks with lwlocks in pg_stat_statements. The latter patch has not been committed yet. Probably attachding the different patches in the same thread would cause this confu

Re: track_planning causing performance regression

On 2020/08/17 18:34, Hamid Akhtar wrote: On Mon, Aug 17, 2020 at 2:21 PM Fujii Masao mailto:masao.fu...@oss.nttdata.com>> wrote: On 2020/07/31 21:40, Hamid Akhtar wrote: > > > On Mon, Jul 6, 2020 at 10:29 AM Fujii Masao mailto

Re: track_planning causing performance regression

On Mon, Aug 17, 2020 at 2:21 PM Fujii Masao wrote: > > > On 2020/07/31 21:40, Hamid Akhtar wrote: > > > > > > On Mon, Jul 6, 2020 at 10:29 AM Fujii Masao > wrote: > > > > > > > > On 2020/07/04 12:22, Pavel Stehu

Re: track_planning causing performance regression

On 2020/07/31 21:40, Hamid Akhtar wrote: On Mon, Jul 6, 2020 at 10:29 AM Fujii Masao mailto:masao.fu...@oss.nttdata.com>> wrote: On 2020/07/04 12:22, Pavel Stehule wrote: > > > pá 3. 7. 2020 v 13:02 odesílatel Fujii Masao mail

Re: track_planning causing performance regression

On Mon, Jul 6, 2020 at 10:29 AM Fujii Masao wrote: > > > On 2020/07/04 12:22, Pavel Stehule wrote: > > > > > > pá 3. 7. 2020 v 13:02 odesílatel Fujii Masao < > masao.fu...@oss.nttdata.com > napsal: > > > > > > > >

Re: track_planning causing performance regression

On 2020/07/04 12:22, Pavel Stehule wrote: pá 3. 7. 2020 v 13:02 odesílatel Fujii Masao mailto:masao.fu...@oss.nttdata.com>> napsal: On 2020/07/03 16:02, Pavel Stehule wrote: > > > pá 3. 7. 2020 v 8:57 odesílatel Fujii Masao mailto:masao.fu...@oss.nttdata.com>

Re: track_planning causing performance regression

pá 3. 7. 2020 v 13:02 odesílatel Fujii Masao napsal: > > > On 2020/07/03 16:02, Pavel Stehule wrote: > > > > > > pá 3. 7. 2020 v 8:57 odesílatel Fujii Masao > napsal: > > > > > > > > On 2020/07/03 13:05, Pavel Stehule wrote: > > > Hi > > > > >

Re: track_planning causing performance regression

On 2020/07/03 16:02, Pavel Stehule wrote: pá 3. 7. 2020 v 8:57 odesílatel Fujii Masao mailto:masao.fu...@oss.nttdata.com>> napsal: On 2020/07/03 13:05, Pavel Stehule wrote: > Hi > > pá 3. 7. 2020 v 4:39 odesílatel Fujii Masao mailto:masao.fu...@oss.nttdata.com>

Re: track_planning causing performance regression

pá 3. 7. 2020 v 8:57 odesílatel Fujii Masao napsal: > > > On 2020/07/03 13:05, Pavel Stehule wrote: > > Hi > > > > pá 3. 7. 2020 v 4:39 odesílatel Fujii Masao > napsal: > > > > > > > > On 2020/07/01 7:37, Peter Geoghegan wrote: > > > On Tue, Jun 30, 2

Re: track_planning causing performance regression

On 2020/07/03 13:05, Pavel Stehule wrote: Hi pá 3. 7. 2020 v 4:39 odesílatel Fujii Masao mailto:masao.fu...@oss.nttdata.com>> napsal: On 2020/07/01 7:37, Peter Geoghegan wrote: > On Tue, Jun 30, 2020 at 6:40 AM Fujii Masao mailto:masao.fu...@oss.nttdata.com>> wrote: >> Ants a

Re: track_planning causing performance regression

Hi pá 3. 7. 2020 v 4:39 odesílatel Fujii Masao napsal: > > > On 2020/07/01 7:37, Peter Geoghegan wrote: > > On Tue, Jun 30, 2020 at 6:40 AM Fujii Masao > wrote: > >> Ants and Andres suggested to replace the spinlock used in pgss_store() > with > >> LWLock. I agreed with them and posted the POC

Re: track_planning causing performance regression

On 2020/07/03 11:43, Peter Geoghegan wrote: On Thu, Jul 2, 2020 at 7:39 PM Fujii Masao wrote: So I pushed the patch and changed default of track_planning to off. I have closed out the open item I created for this. Thanks!! I added the patch that replaces spinlock with lwlock in pgss, in

Re: track_planning causing performance regression

On Thu, Jul 2, 2020 at 7:39 PM Fujii Masao wrote: > So I pushed the patch and changed default of track_planning to off. I have closed out the open item I created for this. Thanks! -- Peter Geoghegan

Re: track_planning causing performance regression

On 2020/07/01 7:37, Peter Geoghegan wrote: On Tue, Jun 30, 2020 at 6:40 AM Fujii Masao wrote: Ants and Andres suggested to replace the spinlock used in pgss_store() with LWLock. I agreed with them and posted the POC patch doing that. But I think the patch is an item for v14. The patch may ad

Re: track_planning causing performance regression

On 2020/07/02 1:54, Andres Freund wrote: Hi, On 2020-07-01 22:20:50 +0900, Fujii Masao wrote: On 2020/07/01 4:03, Andres Freund wrote: Why did you add the hashing here? It seems a lot better to just add an lwlock in-place instead of the spinlock? The added size is neglegible compared to the

Re: track_planning causing performance regression

Hi, On 2020-07-01 22:20:50 +0900, Fujii Masao wrote: > On 2020/07/01 4:03, Andres Freund wrote: > > Why did you add the hashing here? It seems a lot better to just add an > > lwlock in-place instead of the spinlock? The added size is neglegible > > compared to the size of pgssEntry. > > Because p

Re: track_planning causing performance regression

On 2020/07/01 4:03, Andres Freund wrote: Hi, On 2020-06-30 14:43:39 +0900, Fujii Masao wrote: I did a prototype patch that replaces spinlocks with futexes, but was not able to find a workload where it mattered. I'm not familiar with futex, but could you tell me why you used futex instead

Re: track_planning causing performance regression

On Tue, Jun 30, 2020 at 6:40 AM Fujii Masao wrote: > Ants and Andres suggested to replace the spinlock used in pgss_store() with > LWLock. I agreed with them and posted the POC patch doing that. But I think > the patch is an item for v14. The patch may address the reported performance > issue, but

Re: track_planning causing performance regression

Hi, On 2020-06-30 14:30:03 +0300, Ants Aasma wrote: > Futex is a Linux kernel call that allows to build a lock that has > uncontended cases work fully in user space almost exactly like a spinlock, > while falling back to syscalls that wait for wakeup in case of contention. > It's not portable, but

Re: track_planning causing performance regression

Hi, On 2020-06-30 14:43:39 +0900, Fujii Masao wrote: > > I did a prototype patch that replaces spinlocks with futexes, but was not > > able to find a workload where it mattered. > > I'm not familiar with futex, but could you tell me why you used futex instead > of LWLock that we already have? Is

Re: track_planning causing performance regression

On 2020/06/30 7:29, Peter Geoghegan wrote: On Mon, Jun 29, 2020 at 3:23 PM Peter Geoghegan wrote: +1 -- this regression seems unacceptable to me. I added an open item to track this. Thanks! I'm thinking to change the default value of track_planning to off for v13. Ants and Andres sugges

Re: track_planning causing performance regression

On 2020/06/30 20:30, Ants Aasma wrote: On Tue, 30 Jun 2020 at 08:43, Fujii Masao mailto:masao.fu...@oss.nttdata.com>> wrote: > The problem looks to be that spinlocks are terrible with overloaded CPU  and a contended spinlock. A process holding the spinlock might easily get scheduled out

Re: track_planning causing performance regression

On Tue, 30 Jun 2020 at 08:43, Fujii Masao wrote: > > The problem looks to be that spinlocks are terrible with overloaded > CPU and a contended spinlock. A process holding the spinlock might easily > get scheduled out leading to excessive spinning by everybody. I think a > simple thing to try woul

Re: track_planning causing performance regression

On 2020/06/29 22:23, Ants Aasma wrote: On Mon, 29 Jun 2020 at 12:17, Julien Rouhaud mailto:rjuju...@gmail.com>> wrote: On Mon, Jun 29, 2020 at 10:55 AM Fujii Masao mailto:masao.fu...@oss.nttdata.com>> wrote: > > On 2020/06/29 16:05, Julien Rouhaud wrote: > > On Mon, Jun

Re: track_planning causing performance regression

Hi, On 2020-06-29 17:55:28 +0900, Fujii Masao wrote: > One idea to reduce that lock contention is to separate per-query spinlock > into two; one is for planning, and the other is for execution. pgss_store() > determines which lock to use based on the given "kind" argument. > To make this idea work

Re: track_planning causing performance regression

Hi, On 2020-06-29 09:05:18 +0200, Julien Rouhaud wrote: > I can't reproduce this on my laptop, but I can certainly believe that > running the same 3 queries using more connections than available cores > will lead to extra overhead. > I disagree with the conclusion though. It seems to me that if

Re: track_planning causing performance regression

On Mon, Jun 29, 2020 at 3:23 PM Peter Geoghegan wrote: > +1 -- this regression seems unacceptable to me. I added an open item to track this. Thanks -- Peter Geoghegan

Re: track_planning causing performance regression

On Mon, Jun 29, 2020 at 1:55 AM Fujii Masao wrote: > > I disagree with the conclusion though. It seems to me that if you > > really have this workload that consists in these few queries and want > > to get better performance, you'll anyway use a connection pooler > > and/or use prepared statement

Re: track_planning causing performance regression

On Mon, 29 Jun 2020 at 12:17, Julien Rouhaud wrote: > On Mon, Jun 29, 2020 at 10:55 AM Fujii Masao > wrote: > > > > On 2020/06/29 16:05, Julien Rouhaud wrote: > > > On Mon, Jun 29, 2020 at 7:49 AM Tharakan, Robins > wrote: > > >> > > >> During fully-cached SELECT-only test using pgbench, Postgr

Re: track_planning causing performance regression

On Mon, Jun 29, 2020 at 1:14 PM Fujii Masao wrote: > > On 2020/06/29 18:56, Fujii Masao wrote: > > > > > > On 2020/06/29 18:53, Julien Rouhaud wrote: > >> On Mon, Jun 29, 2020 at 11:38 AM Fujii Masao > >> wrote: > >>> > > Your benchmark result seems to suggest that the cause of the problem is

Re: track_planning causing performance regression

On 2020/06/29 18:56, Fujii Masao wrote: On 2020/06/29 18:53, Julien Rouhaud wrote: On Mon, Jun 29, 2020 at 11:38 AM Fujii Masao wrote: Your benchmark result seems to suggest that the cause of the problem is the contention of per-query spinlock in pgss_store(). Right? This lock contention

Re: track_planning causing performance regression

On 2020/06/29 18:53, Julien Rouhaud wrote: On Mon, Jun 29, 2020 at 11:38 AM Fujii Masao wrote: Your benchmark result seems to suggest that the cause of the problem is the contention of per-query spinlock in pgss_store(). Right? This lock contention is likely to happen when multiple session

Re: track_planning causing performance regression

On Mon, Jun 29, 2020 at 11:38 AM Fujii Masao wrote: > > >> Your benchmark result seems to suggest that the cause of the problem is > >> the contention of per-query spinlock in pgss_store(). Right? > >> This lock contention is likely to happen when multiple sessions run > >> the same queries. > >>

Re: track_planning causing performance regression

On 2020/06/29 18:17, Julien Rouhaud wrote: On Mon, Jun 29, 2020 at 10:55 AM Fujii Masao wrote: On 2020/06/29 16:05, Julien Rouhaud wrote: On Mon, Jun 29, 2020 at 7:49 AM Tharakan, Robins wrote: During fully-cached SELECT-only test using pgbench, Postgres v13Beta1 shows Thanks for the

Re: track_planning causing performance regression

On Mon, Jun 29, 2020 at 10:55 AM Fujii Masao wrote: > > On 2020/06/29 16:05, Julien Rouhaud wrote: > > On Mon, Jun 29, 2020 at 7:49 AM Tharakan, Robins wrote: > >> > >> During fully-cached SELECT-only test using pgbench, Postgres v13Beta1 shows > > Thanks for the benchmark! > > > >> ~45% performa

Re: track_planning causing performance regression

On 2020/06/29 16:05, Julien Rouhaud wrote: Hi, On Mon, Jun 29, 2020 at 7:49 AM Tharakan, Robins wrote: During fully-cached SELECT-only test using pgbench, Postgres v13Beta1 shows Thanks for the benchmark! ~45% performance drop [2] at high DB connection counts (when compared with v12.

Re: track_planning causing performance regression

Hi, On Mon, Jun 29, 2020 at 7:49 AM Tharakan, Robins wrote: > > During fully-cached SELECT-only test using pgbench, Postgres v13Beta1 shows > ~45% performance drop [2] at high DB connection counts (when compared with > v12.3) > > Disabling pg_stat_statements.track_planning (which is 'On' by defa