Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id B27001337BD8 for ; Mon, 11 Apr 2011 19:39:48 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 63228-03 for ; Mon, 11 Apr 2011 22:39:41 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pv0-f174.google.com (mail-pv0-f174.google.com [74.125.83.174]) by mail.postgresql.org (Postfix) with ESMTP id C6ED01337BD0 for ; Mon, 11 Apr 2011 19:39:40 -0300 (ADT) Received: by pvg12 with SMTP id 12so2160872pvg.19 for ; Mon, 11 Apr 2011 15:39:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=jUgDR+Cok9rPqlbco5vuAipWI2SzwG7YhQjMjJ4WmhQ=; b=hHNtbC/avfgM7XMNtT6f/ZB4JRnfZot/INehz1Ul0Hab/UNNKaURvpYoM3oomM2+VB apan3HgBvEBP00cBvrF6b7UDV1Bzo5wp64jzIJtcqxWdZIa8PZu1d0KgIxXOHKJ85Yxd nIPRAMCId0c6BYILLUpRy0ZkIX1dc6irZiw3Q= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=KVq3pEgzl/gGIfTfBTuNjZoDTaTo3CFaFEOuTyDAOzcP1fm69stlqvp6/rSJeKYHbe xdyOpa7+W9nuqM63HIxLPR2NqS5OyJ62sl2KfbntviXtAGaAfVFTpXwCn5KQQWHsfp0K Nsx9AMF1bY29A0bGqk8PNZqSaJYOz3F9tUsEg= MIME-Version: 1.0 Received: by 10.142.126.18 with SMTP id y18mr185353wfc.361.1302561578135; Mon, 11 Apr 2011 15:39:38 -0700 (PDT) Received: by 10.68.51.161 with HTTP; Mon, 11 Apr 2011 15:39:38 -0700 (PDT) In-Reply-To: References: Date: Mon, 11 Apr 2011 17:39:38 -0500 Message-ID: Subject: Re: Postgres 9 slave lagging From: Merlin Moncure To: Saurabh Agrawal Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201104/128 X-Sequence-Number: 43190 On Mon, Apr 11, 2011 at 9:10 AM, Saurabh Agrawal 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