Hi,
On 2019-07-23 16:28:52 +0000, Alexander Pyhalov wrote:
> I was asked to bring up this topic on maling lists after asking question on IRC.
>
> The issue is the following.
> On one of our servers (10.5), I see a lot of queries with wait_event_type='LWLock', wait_event='lock_manager'
Could you qualify this a bit more? What does "a lot" mean, in
comparison to the total number of queries/session?
> This is a stat gathering/analyzing application with tables which have
> > 100 partitions.
Hm, what kind of partitioning scheme is this? The "new"-ish partitioning
support isn't yet always that good to only perform the minimal amount of
metadata lookups (which in turn require locks). Especially in 10.
Are you using prepared statements?
> The queries itself are mostly simple (select by pkey, update by primary key and so on).
>
> select count(*) from pg_locks shows about 40K records (all granted) and
>
> select count(*) from (select distinct
locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubidfrom pg_locks)
>
> is varying from about 1K to 25K (I haven't stored this value in monitoring system, just observed manually).
I think to know what exactly is going on, we'd need a cpu profile, and
then perhaps a followup profile for also getting the callsites for lock
waits. Could you take a perf profile?
https://wiki.postgresql.org/wiki/Profiling_with_perf
> Currently I was adviced to increase LOG2_NUM_LOCK_PARTITIONS at
> compile time. If it bothers us enough, we would likely do it, but I
> have a question, if this (or NUM_LOCK_PARTITIONS) value should be
> increased by default or set tunable?
I'm not quite sure what you're asking precisely here. Are you wondering
whether postgres should increase the default value for
LOG2_NUM_LOCK_PARTITIONS should be increased? And whether it should be
runtime configurable? I'm fairly sure we don't want to make it runtime
configurable, that'd add overhead to some pretty central code.
Increasing the default would need a lot of benchmarks, to prove it
doesn't cause regressions in other common scenarios.
Greetings,
Andres Freund
