Re: Warm Standby lagging behind

Hi Haroon,

On Thu, Apr 3, 2014 at 7:23 PM, Haroon <haroonasher(at)gmail(dot)com> wrote:

> We are using dedicated DB and DB Backup Server configured for Warm Standby.
> Uptill now our Server was generating around 3k Wal files per day and backup
> was catching up. Recently the load on our Production Server has increased
> and now it is generating around 4k wal files.
>
>
Can't we reduce the no.of wal generations by tuning the
checkpoint_segments, wal_buffers.

> Now we can see alot of wal files in Backup Server waiting to get injested.
> They are increasing by around 1k wal files per day. On the backup Server
> the
> postgress startup process which is responsible for injesting wal files is
> alway consuing 99.1 to 99.7 %CPU and IO Wait is staying at 0.0 to 0.1 %.
>
>
I am not sure how SAS drives works in RAID 10. But i would like to check
whether the archives and data directory resides in a same mount point or
not. If both resides in a same mount point, then it will take time to read
archives from the mount point, and write to the same mount point.

> We are using 15k rpm SAS drives configured in Raid 10 on main as well as
> Backup Servers. My understanding is that drives speed is a main factor on
> which the restoration speed depend. But these drives seems capable of
> handling more load than this.
>
> We are not getting any performance issue in our main server. It is the
> backup which is lagging behind.
>

If your pg version is >= 9.0, then try to setup the streaming replication.
It's an online replication which is not mandatory to transfer these
archives to slave machine.

I believe, you already implemented the archive clean up job. If not,
implement this.

> I will appreciate if some will help me in pointing out the bottle neck and
> provide solution.
>
>
Consult pgsql-general list also. There you may get better solution for your
problem.

Thanks,
Dinesh

>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Warm-Standby-lagging-behind-tp5798498.html
> Sent from the PostgreSQL - pgadmin support mailing list archive at
> Nabble.com.
>
>
> --
> Sent via pgadmin-support mailing list (pgadmin-support(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>