Re: Postgres 9 slave lagging

On Mon, Apr 11, 2011 at 9:10 AM, Saurabh Agrawal <saurabh(dot)miet(at)gmail(dot)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