pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAW: query performance after database rename
3+ messages / 1 participants
[nested] [flat]
* AW: query performance after database rename
@ 2018-11-21 12:00 Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
0 siblings, 1 reply; 3+ messages in thread
From: Zwettler Markus (OIZ) @ 2018-11-21 12:00 UTC (permalink / raw)
To: srinivas oguri <srinivasoguri7@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto: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] 3+ messages in thread
* AW: query performance after database rename
@ 2018-11-21 12:22 Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
parent: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
0 siblings, 1 reply; 3+ messages in thread
From: Zwettler Markus (OIZ) @ 2018-11-21 12:22 UTC (permalink / raw)
To: srinivas oguri <srinivasoguri7@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
Von: srinivas oguri <srinivasoguri7@gmail.com>
Gesendet: Mittwoch, 21. November 2018 13:13
An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
Cc: pgsql-admin@lists.postgresql.org
Betreff: Re: query performance after database rename
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<mailto: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<mailto:srinivasoguri7@gmail.com>>
Gesendet: Mittwoch, 21. November 2018 12:24
An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch<mailto:Markus.Zwettler@zuerich.ch>>
Cc: pgsql-admin@lists.postgresql.org<mailto: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<mailto: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] 3+ messages in thread
* AW: query performance after database rename
@ 2018-11-21 15:41 Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
parent: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
0 siblings, 0 replies; 3+ messages in thread
From: Zwettler Markus (OIZ) @ 2018-11-21 15:41 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi,
Don't ask me why. ☹
Both databases have the same structure.
Both databases are in use before and after the switch.
Buffer cache should be warm, statistics should be up-to-date, therefore.
Thanks,
Markus
Von: Don Seiler <don@seiler.us>
Gesendet: Mittwoch, 21. November 2018 15:54
An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
Cc: srinivasoguri7@gmail.com; pgsql-admin@lists.postgresql.org
Betreff: Re: query performance after database rename
On Wed, Nov 21, 2018 at 6:22 AM Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch<mailto: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<http://www.seiler.us;
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-11-21 15:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-21 12:00 AW: query performance after database rename Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
2018-11-21 12:22 ` Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
2018-11-21 15:41 ` Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
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