pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedquery performance after database rename
5+ messages / 4 participants
[nested] [flat]
* query performance after database rename
@ 2018-11-21 09:20 Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
2018-11-21 11:24 ` Re: query performance after database rename srinivas oguri <srinivasoguri7@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Zwettler Markus (OIZ) @ 2018-11-21 09:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi,
We have a postgresql cluster with two databases.
We rename (switch) the databases daily (alter database x rename to y;)
The query performance slows down after each renaming.
Question: Why? Does postgresql flush the db-cache?
Thanks,
Markus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query performance after database rename
2018-11-21 09:20 query performance after database rename Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
@ 2018-11-21 11:24 ` srinivas oguri <srinivasoguri7@gmail.com>
2018-11-21 12:12 ` Re: query performance after database rename srinivas oguri <srinivasoguri7@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: srinivas oguri @ 2018-11-21 11:24 UTC (permalink / raw)
To: Markus.Zwettler@zuerich.ch; +Cc: pgsql-admin@lists.postgresql.org
Hi Markus,
Can you give an example ? what is the postgres version your are using? what
exactly you are doing ?
PostgreSQL do not perform any flush operation after renaming. Below is the
debug log of postgresql
< 2018-11-21 11:12:19.481 UTC > DEBUG: postmaster child[14261]: starting
with (
< 2018-11-21 11:12:19.481 UTC > DEBUG: postgres
< 2018-11-21 11:12:19.481 UTC > DEBUG: )
< 2018-11-21 11:12:19.481 UTC > DEBUG: InitPostgres
< 2018-11-21 11:12:19.482 UTC > DEBUG: my backend ID is 3
< 2018-11-21 11:12:19.482 UTC > DEBUG: StartTransaction
< 2018-11-21 11:12:19.482 UTC > DEBUG: name: unnamed; blockState:
DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
< 2018-11-21 11:12:19.483 UTC > DEBUG: CommitTransaction
< 2018-11-21 11:12:19.483 UTC > DEBUG: name: unnamed; blockState:
STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
< 2018-11-21 11:12:19.483 UTC > DEBUG: StartTransactionCommand
< 2018-11-21 11:12:19.483 UTC > DEBUG: StartTransaction
< 2018-11-21 11:12:19.483 UTC > DEBUG: name: unnamed; blockState:
DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
< 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransactionCommand
< 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransaction
< 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
< 2018-11-21 11:12:19.484 UTC > LOG: duration: 0.367 ms statement: SELECT
pg_backend_pid()
< 2018-11-21 11:12:19.484 UTC > DEBUG: StartTransactionCommand
< 2018-11-21 11:12:19.484 UTC > DEBUG: StartTransaction
< 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
< 2018-11-21 11:12:19.484 UTC > DEBUG: ProcessUtility
< 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransactionCommand
< 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransaction
< 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
STARTED; state: INPROGR, xid/subid/cid: 151784/1/0 (used), nestlvl: 1,
children:
*< 2018-11-21 11:12:19.485 UTC > LOG: duration: 1.007 ms statement: alter
database test rename to test_old*
< 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(0): 1 before_shmem_exit
callbacks to make
< 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(0): 6 on_shmem_exit
callbacks to make
< 2018-11-21 11:12:19.485 UTC > DEBUG: proc_exit(0): 3 callbacks to make
< 2018-11-21 11:12:19.485 UTC > DEBUG: exit(0)
< 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(-1): 0 before_shmem_exit
callbacks to make
< 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(-1): 0 on_shmem_exit
callbacks to make
< 2018-11-21 11:12:19.485 UTC > DEBUG: proc_exit(-1): 0 callbacks to make
< 2018-11-21 11:12:19.486 UTC > DEBUG: reaping dead processes
< 2018-11-21 11:12:19.486 UTC > DEBUG: server process (PID 14261) exited
with exit code 0
On Wed, Nov 21, 2018 at 2:50 PM Zwettler Markus (OIZ) <
Markus.Zwettler@zuerich.ch> wrote:
> Hi,
>
> We have a postgresql cluster with two databases.
> We rename (switch) the databases daily (alter database x rename to y;)
> The query performance slows down after each renaming.
> Question: Why? Does postgresql flush the db-cache?
>
> Thanks,
> Markus
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query performance after database rename
2018-11-21 09:20 query performance after database rename Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
2018-11-21 11:24 ` Re: query performance after database rename srinivas oguri <srinivasoguri7@gmail.com>
@ 2018-11-21 12:12 ` srinivas oguri <srinivasoguri7@gmail.com>
2018-11-21 14:53 ` Re: query performance after database rename Don Seiler <don@seiler.us>
0 siblings, 1 reply; 5+ messages in thread
From: srinivas oguri @ 2018-11-21 12:12 UTC (permalink / raw)
To: Markus.Zwettler@zuerich.ch; +Cc: pgsql-admin@lists.postgresql.org
How you are identifying the slowdown in database system ? Are you executing
some queries through psql or pgadmin and checking execution time or from
application and find slowness ? Are you using connection pooling ? If so
which one it is application or postgresql tools (pgpool, pgbouncer etc).
Thanks
Srinivas
On Wed, Nov 21, 2018 at 5:30 PM Zwettler Markus (OIZ) <
Markus.Zwettler@zuerich.ch> wrote:
> Hi,
>
>
>
> We have to exchange two databases db1 ó db2.
>
>
>
> alter database [db1|db2] with allow_connections=false;
>
> select pg_terminate_backend (pg_stat_activity.pid) from pg_stat_activity
> where pg_stat_activity.datname in ('db1', 'db2') and pid <>
> pg_backend_pid();
>
> alter database [db1|db2|temp] rename to [temp|db1|db2];
>
> alter database [db1|db2] with allow_connections=true;
>
>
>
> Version 9.6
>
>
>
> Cheers, Markus
>
>
>
>
>
>
>
>
>
> *Von:* srinivas oguri <srinivasoguri7@gmail.com>
> *Gesendet:* Mittwoch, 21. November 2018 12:24
> *An:* Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
> *Cc:* pgsql-admin@lists.postgresql.org
> *Betreff:* Re: query performance after database rename
>
>
>
> Hi Markus,
>
>
>
> Can you give an example ? what is the postgres version your are using?
> what exactly you are doing ?
>
>
>
> PostgreSQL do not perform any flush operation after renaming. Below is the
> debug log of postgresql
>
>
>
> < 2018-11-21 11:12:19.481 UTC > DEBUG: postmaster child[14261]: starting
> with (
>
> < 2018-11-21 11:12:19.481 UTC > DEBUG: postgres
>
> < 2018-11-21 11:12:19.481 UTC > DEBUG: )
>
> < 2018-11-21 11:12:19.481 UTC > DEBUG: InitPostgres
>
> < 2018-11-21 11:12:19.482 UTC > DEBUG: my backend ID is 3
>
> < 2018-11-21 11:12:19.482 UTC > DEBUG: StartTransaction
>
> < 2018-11-21 11:12:19.482 UTC > DEBUG: name: unnamed; blockState:
> DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>
> < 2018-11-21 11:12:19.483 UTC > DEBUG: CommitTransaction
>
> < 2018-11-21 11:12:19.483 UTC > DEBUG: name: unnamed; blockState:
> STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>
> < 2018-11-21 11:12:19.483 UTC > DEBUG: StartTransactionCommand
>
> < 2018-11-21 11:12:19.483 UTC > DEBUG: StartTransaction
>
> < 2018-11-21 11:12:19.483 UTC > DEBUG: name: unnamed; blockState:
> DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransactionCommand
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransaction
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
> STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>
> < 2018-11-21 11:12:19.484 UTC > LOG: duration: 0.367 ms statement:
> SELECT pg_backend_pid()
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: StartTransactionCommand
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: StartTransaction
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
> DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: ProcessUtility
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransactionCommand
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransaction
>
> < 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
> STARTED; state: INPROGR, xid/subid/cid: 151784/1/0 (used), nestlvl: 1,
> children:
>
> *< 2018-11-21 11:12:19.485 UTC > LOG: duration: 1.007 ms statement:
> alter database test rename to test_old*
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(0): 1 before_shmem_exit
> callbacks to make
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(0): 6 on_shmem_exit
> callbacks to make
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: proc_exit(0): 3 callbacks to make
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: exit(0)
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(-1): 0
> before_shmem_exit callbacks to make
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(-1): 0 on_shmem_exit
> callbacks to make
>
> < 2018-11-21 11:12:19.485 UTC > DEBUG: proc_exit(-1): 0 callbacks to make
>
> < 2018-11-21 11:12:19.486 UTC > DEBUG: reaping dead processes
>
> < 2018-11-21 11:12:19.486 UTC > DEBUG: server process (PID 14261) exited
> with exit code 0
>
>
>
>
>
> On Wed, Nov 21, 2018 at 2:50 PM Zwettler Markus (OIZ) <
> Markus.Zwettler@zuerich.ch> wrote:
>
> Hi,
>
> We have a postgresql cluster with two databases.
> We rename (switch) the databases daily (alter database x rename to y;)
> The query performance slows down after each renaming.
> Question: Why? Does postgresql flush the db-cache?
>
> Thanks,
> Markus
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query performance after database rename
2018-11-21 09:20 query performance after database rename Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
2018-11-21 11:24 ` Re: query performance after database rename srinivas oguri <srinivasoguri7@gmail.com>
2018-11-21 12:12 ` Re: query performance after database rename srinivas oguri <srinivasoguri7@gmail.com>
@ 2018-11-21 14:53 ` Don Seiler <don@seiler.us>
2018-11-21 16:06 ` Re: query performance after database rename Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Don Seiler @ 2018-11-21 14:53 UTC (permalink / raw)
To: Markus.Zwettler@zuerich.ch; +Cc: srinivasoguri7@gmail.com; pgsql-admin@lists.postgresql.org
On Wed, Nov 21, 2018 at 6:22 AM Zwettler Markus (OIZ) <
Markus.Zwettler@zuerich.ch> wrote:
> Hi,
>
>
>
> Customer claims about application slowdown after such an database exchange.
>
> I will debug the cluster to get more in-depth in-sight.
>
> No connection pooling used.
>
>
>
> Thanks,
>
> Markus
>
>
>
>
>
> We have to exchange two databases db1 ó db2.
>
>
>
> alter database [db1|db2] with allow_connections=false;
>
> select pg_terminate_backend (pg_stat_activity.pid) from pg_stat_activity
> where pg_stat_activity.datname in ('db1', 'db2') and pid <>
> pg_backend_pid();
>
> alter database [db1|db2|temp] rename to [temp|db1|db2];
>
> alter database [db1|db2] with allow_connections=true;
>
>
>
> Version 9.6
>
>
Off the top of my head, I'd say after switching to a new database, you'd
have a cold buffer cache. There's a possibility of old statistics. Are you
syncing data changes from the old DB to the new DB?
Also, I'm curious *why* you're doing this.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query performance after database rename
2018-11-21 09:20 query performance after database rename Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
2018-11-21 11:24 ` Re: query performance after database rename srinivas oguri <srinivasoguri7@gmail.com>
2018-11-21 12:12 ` Re: query performance after database rename srinivas oguri <srinivasoguri7@gmail.com>
2018-11-21 14:53 ` Re: query performance after database rename Don Seiler <don@seiler.us>
@ 2018-11-21 16:06 ` Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jonah H. Harris @ 2018-11-21 16:06 UTC (permalink / raw)
To: don@seiler.us; +Cc: Markus.Zwettler@zuerich.ch; srinivasoguri7@gmail.com; pgsql-admin@lists.postgresql.org
On Wed, Nov 21, 2018 at 9:54 AM Don Seiler <don@seiler.us> wrote:
> Off the top of my head, I'd say after switching to a new database, you'd
> have a cold buffer cache. There's a possibility of old statistics. Are you
> syncing data changes from the old DB to the new DB?
>
Yeah. That would be my guess as well. Sounds like blocks from the new
database would likely be causing high evictions from those of the old
database. Would be interesting to see an overview of it with pg_buffercache.
Also, I'm curious *why* you're doing this.
>
Ditto.
--
Jonah H. Harris
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-11-21 16:06 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-21 09:20 query performance after database rename Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
2018-11-21 11:24 ` srinivas oguri <srinivasoguri7@gmail.com>
2018-11-21 12:12 ` srinivas oguri <srinivasoguri7@gmail.com>
2018-11-21 14:53 ` Don Seiler <don@seiler.us>
2018-11-21 16:06 ` Jonah H. Harris <jonah.harris@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox