From: Andres Freund <andres@2ndquadrant.com>
To: Ruben Domingo Gaspar Aparicio <Ruben.Gaspar.Aparicio@cern.ch>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: dbondemand-admin (DB On Demand administrators) <dbondemand-admin@cern.ch>
Subject: Re: Postgres slave not catching up (on 9.2)
Date: Sun, 9 Nov 2014 23:44:46 +0100
Message-ID: <20141109224446.GC28007@alap3.anarazel.de> (raw)
In-Reply-To: <6FCBD86BDC789C43AC696969EC340228010939EB7E@CERNXCHG44.cern.ch>
References: <6FCBD86BDC789C43AC696969EC340228010939EB7E@CERNXCHG44.cern.ch>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi,
On 2014-11-08 13:11:25 +0000, Ruben Domingo Gaspar Aparicio wrote:
> Hello,
>
> I have built up a hot-standby between a master running 9.2.4 and a slave running 9.2.9. I did the initial copy using "pg_basebackup" . My recovery.conf looks like:
>
> standby_mode = 'on'
> primary_conninfo = 'host=pXXXXXXXX port=XXXX user=replicator'
> trigger_file = 'failover.now'
> restore_command = 'test -f /ORA/dbs02/PUPDBTST/archive/%f && ln -fns /ORA/dbs02/PUPDBTST/archive/%f %p'
> archive_cleanup_command = '/usr/local/pgsql/postgresql-9.2.9/bin/pg_archivecleanup /ORA/dbs03/PUPDBTST/data %r'
>
> The slave (I don't have control on the master) is using 2 NFS file systems, one for WALs and another one for the data, on Netapp controllers:
>
> dbnasg401-12a:/vol/dodpupdbtst02 on /ORA/dbs02/PUPDBTST type nfs (rw,remount,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,timeo=600)
> dbnasg403-12a:/vol/dodpupdbtst03 on /ORA/dbs03/PUPDBTST type nfs (rw,remount,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,timeo=600)
>
> The streaming is working perfectly:
>
> /usr/local/pgsql/postgresql-9.2.9/bin/psql -U admin -h XXXXX -p XXXXX -d puppetdb -c "select pid,usesysid, usename, application_name, client_addr, state, sent_location,write_location,replay_location,sync_state from pg_stat_replication;"
> pid | usesysid | usename | application_name | client_addr | state | sent_location | write_location | replay_location | sync_state
> --------+----------+------------+------------------+-------------+-----------+---------------+----------------+-----------------+------------
> 117659 | 16384 | replicator | walreceiver | 10.16.7.137 | streaming | AA74/DD630978 | AA74/DD630978 | A977/F84F0BE0 | async
> (1 row)
>
> But the lag is increasing constantly, it looks the replay can not cope with:
I have a couple of questions:
1) Is the standby actually used for querying? Is it possible that replay
frequently conflicts with active queries? As you don't have
hot_standby_feedback enabled that seems quite possible.
2) Is the startup process on the standby CPU or IO bound?
3) Does the workload involve loads of temporary tables or generally
transactions locking lots of tables exclusively in one transaction?
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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-performance@postgresql.org
Cc: andres@2ndquadrant.com, Ruben.Gaspar.Aparicio@cern.ch, dbondemand-admin@cern.ch
Subject: Re: Postgres slave not catching up (on 9.2)
In-Reply-To: <20141109224446.GC28007@alap3.anarazel.de>
* 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