Обсуждение: Hot Standby Lagging Behind
Hi Everyone,
I have some questions about the PostgreSQL streaming replication feature. I have been able to setup a hot-standby server, and I can see updates on the master server being replayed on the standby server.
However I notice that once I start making read-only queries, the standby starts lagging behind the master server. After a while, the delay can reach over 1 minute, even though I set the max_standby_streaming_delay parameter to 15s. I am under the impression that once a WAL record is lagging 15s behind, then it should trump any read-only queries in the system.
Below are the values of pg_last_xlog_receive_location and pg_last_xlog_replay_location at several time points
now 03:06:16
pg_last_xlog_receive_location 0/59A31620
pg_last_xlog_replay_location 0/599A6B90
now 03:07:18
pg_last_xlog_receive_location 0/59A98460
pg_last_xlog_replay_location 0/59A1BE58
now 03:07:31
pg_last_xlog_receive_location 0/59AA8F48
pg_last_xlog_replay_location 0/59A22288
So as we can see the WAL record at 0/59A31620 was received at 03:06:16, but more than a minute later at 03:07:31, it still hasn't been replayed.
Am I configuring the wrong thing? Is there an option to increase the priority of the WAL replay once it has been lagging for a certain amount of WAL record?
Thanks in advance for your help.
Prima
Are both servers identical hardware? On Sat, Aug 25, 2012 at 12:37 AM, Prima Chairunnanda <prima.ch@gmail.com> wrote: > Hi Everyone, > > I have some questions about the PostgreSQL streaming replication feature. I > have been able to setup a hot-standby server, and I can see updates on the > master server being replayed on the standby server. > > However I notice that once I start making read-only queries, the standby > starts lagging behind the master server. After a while, the delay can reach > over 1 minute, even though I set the max_standby_streaming_delay parameter > to 15s. I am under the impression that once a WAL record is lagging 15s > behind, then it should trump any read-only queries in the system. > > Below are the values of pg_last_xlog_receive_location and > pg_last_xlog_replay_location at several time points > > now 03:06:16 > pg_last_xlog_receive_location 0/59A31620 > pg_last_xlog_replay_location 0/599A6B90 > > now 03:07:18 > pg_last_xlog_receive_location 0/59A98460 > pg_last_xlog_replay_location 0/59A1BE58 > > now 03:07:31 > pg_last_xlog_receive_location 0/59AA8F48 > pg_last_xlog_replay_location 0/59A22288 > > So as we can see the WAL record at 0/59A31620 was received at 03:06:16, but > more than a minute later at 03:07:31, it still hasn't been replayed. > > Am I configuring the wrong thing? Is there an option to increase the > priority of the WAL replay once it has been lagging for a certain amount of > WAL record? > > Thanks in advance for your help. > > Prima
Yes, they have exactly the same specification.
Prima
On Sat, Aug 25, 2012 at 2:33 PM, Lonni J Friedman <netllama@gmail.com> wrote:
Are both servers identical hardware?
On Sat, Aug 25, 2012 at 12:37 AM, Prima Chairunnanda <prima.ch@gmail.com> wrote:
> Hi Everyone,
>
> I have some questions about the PostgreSQL streaming replication feature. I
> have been able to setup a hot-standby server, and I can see updates on the
> master server being replayed on the standby server.
>
> However I notice that once I start making read-only queries, the standby
> starts lagging behind the master server. After a while, the delay can reach
> over 1 minute, even though I set the max_standby_streaming_delay parameter
> to 15s. I am under the impression that once a WAL record is lagging 15s
> behind, then it should trump any read-only queries in the system.
>
> Below are the values of pg_last_xlog_receive_location and
> pg_last_xlog_replay_location at several time points
>
> now 03:06:16
> pg_last_xlog_receive_location 0/59A31620
> pg_last_xlog_replay_location 0/599A6B90
>
> now 03:07:18
> pg_last_xlog_receive_location 0/59A98460
> pg_last_xlog_replay_location 0/59A1BE58
>
> now 03:07:31
> pg_last_xlog_receive_location 0/59AA8F48
> pg_last_xlog_replay_location 0/59A22288
>
> So as we can see the WAL record at 0/59A31620 was received at 03:06:16, but
> more than a minute later at 03:07:31, it still hasn't been replayed.
>
> Am I configuring the wrong thing? Is there an option to increase the
> priority of the WAL replay once it has been lagging for a certain amount of
> WAL record?
>
> Thanks in advance for your help.
>
> Prima