Re: Postgres is not able to handle more than 4k tables!?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Postgres is not able to handle more than 4k tables!?
Date: 2020-07-08 21:35:55
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> writes:
> There are several thousand clients, most of which are executing complex
> queries.

So, that's really the core of your problem. We don't promise that
you can run several thousand backends at once. Usually it's recommended
that you stick a connection pooler in front of a server with (at most)
a few hundred backends.

> So them are not able to process all this invalidation messages and their
> invalidation message buffer is overflown.
> Size of this buffer is hardcoded (MAXNUMMESSAGES = 4096) and can not be
> changed without recompilation of Postgres.
> This is problem N1.

No, this isn't a problem. Or at least you haven't shown a reason to
think it is. Sinval overruns are somewhat routine, and we certainly
test that code path (see CLOBBER_CACHE_ALWAYS buildfarm animals).

> But then we come to the next show stopper: NUM_LOCK_PARTITIONS.
> It is also hardcoded and can't be changed without recompilation:


> Having just 16 LW-Locks greatly increase conflict probability (taken in
> account that there are 5k tables and totally about 25k relations).

> It cause huge lw-lock acquisition time for heap_open and planning stage
> of some queries is increased from milliseconds to several minutes!


> This is problem number 2. But there is one more flaw we have faced with.
> We have increased LOG2_NUM_LOCK_PARTITIONS to 8
> and ... clients start to report "too many LWLocks taken" error.
> There is yet another hardcoded constant MAX_SIMUL_LWLOCKS = 200
> which relation with NUM_LOCK_PARTITIONS  was not mentioned anywhere.

Seems like self-inflicted damage. I certainly don't recall anyplace
in the docs where we suggest that you can alter that constant without
worrying about consequences.

> So looks like NUM_LOCK_PARTITIONS and MAXNUMMESSAGES  constants have to
> be replaced with GUCs.

I seriously doubt we'd do that.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2020-07-08 21:55:26 Re: [PATCH] Btree BackwardScan race condition on Standby during VACUUM
Previous Message Tom Lane 2020-07-08 21:26:48 Re: Is this a bug in pg_current_logfile() on Windows?