Обсуждение: query performance after database rename

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

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).

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

--