On Wed, Mar 28, 2018 at 2:58 AM, Laurenz Albe <laurenz.a...@cybertec.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.
