Re: response time is very long in PG9.5.5 using psql or jdbc

On Tue, 13 Feb 2018 14:21:02 -0800
Andres Freund <andres(at)anarazel(dot)de> wrote:

> On 2018-02-13 17:06:36 -0500, Tom Lane wrote:
> > Now, those issues seemed to be associated with needing to rebuild the
> > relcache init file, so you wouldn't expect them to repeat on every
> > connection ... but maybe the OP has something going on that causes the
> > init file to get invalidated constantly. That'd have to be on top of
> > some other massive performance problem, but at least you could see how
> > catalog bloat could possibly lead to this type of symptom, seeing that
> > init file rebuild requires some catalog seqscans.
>
> One of the OP's examples shows the connection establishment separate
> from the slowdown however. Shouldn't that preclude relcache init file
> rebuilds being involved?

I can't account for all the behaviors, but we have a job that spawns 100
connections to process a heavily sharded set of tables in parallel every few
minutes. Normally it runs for a few minutes, but it will extend a lot, eg
hours when the catalogs get severely bloated. I'm assuming that the relcache
init scans are chasing each others buffers out of memory as the system is
completely IO bound when this happens.

Not saying for sure that this is what is happening to the OP, but it may be
similar.

-dg

--
David Gould daveg(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.