Re: Connection hangs on new created schema

On Wed, Mar 28, 2018 at 2:58 AM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> Juan Manuel Cuello wrote:
> > I have a postgresql database with around 4000 schemas. Each schema has
> around
> > the same 65 tables. There are 8 processes connected to the database (it
> is a web app).
> > Each process has only one direct connection to the database (no
> connection pool).
> >
> > Everything works ok until a new schema (with all its tables, indices,
> etc)
> > is added to the database. When this happens, the next query made to the
> database
> > takes too long, sometimes more than a minute o two. Also I see a high DB
> CPU usage
> > during that lapse. Then everything continues working ok as usual and CPU
> drops to normal levels.
> >
> > I'm mostly sure this has to do with the amount of schemas, maybe related
> to relcache,
> > but not sure why creating new schemas causes all this and where to look
> to solve this problem.
>
> If you know the slow query EXPLAIN (ANALYZE, BUFFERS) it and see where the
> time
> is spent.
>

I doesn't seem to depend on the query. Immediately after the new schema is
created, the next query takes a lot of time. It eventually resolves and
next statements are executed ok.

I think it is related to something the db processes are doing when the new
schema is created (maybe reloading relcache?), because the db processes
consumes a lot of CPU during that lapse. After a while, everything goes
back to normality.