From: Merlin Moncure <mmoncure@gmail.com>
To: Saurabh Agrawal <saurabh.miet@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres 9 slave lagging
Date: Mon, 11 Apr 2011 17:39:38 -0500
Message-ID: <BANLkTik2UqnYntNY1YyV++WSnoGwbNGetg@mail.gmail.com> (raw)
In-Reply-To: <BANLkTi=7ui8yRQHkFg2jMCP6+OqSknc-FA@mail.gmail.com>
References: <BANLkTi=7ui8yRQHkFg2jMCP6+OqSknc-FA@mail.gmail.com>
On Mon, Apr 11, 2011 at 9:10 AM, Saurabh Agrawal <saurabh.miet@gmail.com> wrote:
> Hi All,
>
> I have setup postgres 9 master slave streaming replication but
> experiencing slave lagging sometimes by 50 min to 60 min. I am not
> getting exact reason for slave lag delay. Below are the details:
>
> 1. Master table contains partition tables with frequent updates.
> 2. Slave is used for report generation so long running queries hit slave.
> 3. ANALYZE runs every hour on partition table on master.
> 4. postgresql.conf:
> archive_timeout = 900
> checkpoint_segments = 500
> checkpoint_timeout = 30min
> checkpoint_warning = 2000s
> max_standby_archive_delay = -1
>
> I noticed that whenever long running query executes on slave, slave
> starts lagging by master and as soon as query completes log files get
> applied immediately. Please help how can I avoid slave lag.
You answered your own question. Long running queries on the slave can
hold up replay so that the slave can see consistent data....this is
explained in detail in the documentation You deliberately configured
the slave to do this: max_standby_archive_delay -1, which mains defer
replay forever so you can get queries to complete. You can set this
lower to get more responsive replication, but that means you need to
be prepared to have long running queries on the slave fail.
The larger answer is that a particular slave can be configured to
support long running queries or be very responsive but not both. You
can always configure 2 slaves of course with settings optimized for
specific purposes.
merlin
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: mmoncure@gmail.com, saurabh.miet@gmail.com
Subject: Re: Postgres 9 slave lagging
In-Reply-To: <BANLkTik2UqnYntNY1YyV++WSnoGwbNGetg@mail.gmail.com>
References: <BANLkTi=7ui8yRQHkFg2jMCP6+OqSknc-FA@mail.gmail.com> <BANLkTik2UqnYntNY1YyV++WSnoGwbNGetg@mail.gmail.com>
* 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