From: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
To: Don Seiler <don@seiler.us>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: AW: query performance after database rename
Date: Wed, 21 Nov 2018 15:41:59 +0000
Message-ID: <2517bbd286ed41388e4a474314682e76@zuerich.ch> (raw)
In-Reply-To: <CAHJZqBDofwpbRXLb=Lg32VafRxT8+pEwXR8NHY6Y1NRZBL7dYw@mail.gmail.com>
References: <441f8b8943b44771b05fa808dbda4eb0@zuerich.ch>
<CADfH0ysCGBny8S+tOr1XEeCKa5sfR4iqrwFBh2g6u0scSjVfvg@mail.gmail.com>
<88044608393e4016ab0a00dc0773e97a@zuerich.ch>
<CADfH0yvjQrVk4Yf0sqwTXLb16-3J6+MxTDVSKaj=1R+ZSouinw@mail.gmail.com>
<912eabc9b27c4acbb5f2cdb3a28f490e@zuerich.ch>
<CAHJZqBDofwpbRXLb=Lg32VafRxT8+pEwXR8NHY6Y1NRZBL7dYw@mail.gmail.com>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Markus.Zwettler@zuerich.ch, don@seiler.us, pgsql-admin@lists.postgresql.org
Subject: Re: AW: query performance after database rename
In-Reply-To: <2517bbd286ed41388e4a474314682e76@zuerich.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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