On Mon, Apr 11, 2011 at 9:10 AM, Saurabh Agrawal <saurabh.miet@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
