Connection hangs on new created schema
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.
i can see also the server hast a lot of free memory and is no swapping at
all.
OS: Linux
Server version: 9.3.20
Any insight will be highly appreciated.
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.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Wed, Mar 28, 2018 at 2:58 AM, Laurenz Albe <laurenz.albe@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.