Horton

Re: track_planning causing performance regression

Alvaro Herrera <alvherre@2ndquadrant.com>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Fujii Masao <masao.fujii@oss.nttdata.com>
Cc: bttanakahbk <bttanakahbk@oss.nttdata.com>, Hamid Akhtar <hamid.akhtar@gmail.com>, Pavel Stehule <pavel.stehule@gmail.com>, Peter Geoghegan <pg@bowt.ie>, Julien Rouhaud <rjuju123@gmail.com>, "Tharakan, Robins" <tharar@amazon.com>, pgsql-hackers@postgresql.org
Date: 2020-09-11T22:10:05Z
Lists: pgsql-hackers 
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 reduce the lock contention, upthread. One is for planner stats,
> and the other is for executor stats. Is it worth working on
> this approach as an alternative idea? Or does anyone have any better idea?

It does seem that the excl-locked section in pgss_store is rather large.
(I admit I don't understand why would a LWLock decrease performance.)

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.qlej5b3qpfe4uijo@alap3.anarazel.de

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Commits

  1. doc: Fix description about pg_stat_statements.track_planning.

  2. doc: Add note about possible performance overhead by enabling track_planning.

  3. Change default of pg_stat_statements.track_planning to off.