Re: query performance after database rename

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx> 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



[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux