Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZPw2K-0005eq-Jf for pgsql-performance@arkaria.postgresql.org; Thu, 13 Aug 2015 17:11:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZPw2J-0005rW-BB for pgsql-performance@arkaria.postgresql.org; Thu, 13 Aug 2015 17:11:27 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZPw2I-0005r7-IP for pgsql-performance@postgresql.org; Thu, 13 Aug 2015 17:11:26 +0000 Received: from 01.zmailcloud.com ([192.198.85.104] helo=mx-out-1.zmailcloud.com) by makus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1ZPw2F-0001DT-Nz for pgsql-performance@postgresql.org; Thu, 13 Aug 2015 17:11:25 +0000 Received: from smtp.01.com (smtp.01.com [10.10.0.43]) by mx-out-1.zmailcloud.com (Postfix) with ESMTP id 7E76456F0E3 for ; Thu, 13 Aug 2015 12:09:36 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by smtp-out-2.01.com (Postfix) with ESMTP id 7740C60DD8 for ; Thu, 13 Aug 2015 12:09:36 -0500 (CDT) X-Virus-Scanned: amavisd-new at smtp-out-2.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-2.01.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id kmgoD66QCw2j for ; Thu, 13 Aug 2015 12:09:36 -0500 (CDT) Received: from smtp.01.com (localhost [127.0.0.1]) by smtp-out-2.01.com (Postfix) with ESMTP id 576B560DDC for ; Thu, 13 Aug 2015 12:09:36 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by smtp-out-2.01.com (Postfix) with ESMTP id 417E360DDB for ; Thu, 13 Aug 2015 12:09:36 -0500 (CDT) X-Virus-Scanned: amavisd-new at smtp-out-2.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-2.01.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id 3z7luk3FiuxF for ; Thu, 13 Aug 2015 12:09:36 -0500 (CDT) Received: from [172.47.23.104] (50-0-190-244.dsl.dynamic.fusionbroadband.com [50.0.190.244]) by smtp-out-2.01.com (Postfix) with ESMTPSA id DFE3160DD8 for ; Thu, 13 Aug 2015 12:09:35 -0500 (CDT) Message-ID: <55CCCF4A.2080102@agliodbs.com> Date: Thu, 13 Aug 2015 10:09:30 -0700 From: Josh Berkus Organization: PostgreSQL Experts Inc. User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.8.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Strange query stalls on replica in 9.3.9 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Setup: * PostgreSQL 9.3.9 * 1 master, 1 replica * Tiny database, under 0.5GB, completely cached in shared_buffers * 90% read query traffic, which is handled by replica * Traffic in the 1000's QPS. The wierdness: Periodically the master runs an "update all rows" query on the main table in the database. When this update hits the replica via replication stream, *some* (about 5%) of the queries which do seq scans will stall for 22 to 32 seconds (these queries normally take about 75ms). Queries which do index scans seem not to be affected. Thing is, the update all rows only takes 2.5 seconds to execute on the master. So even if the update is blocking the seq scans on the replica (and I can't see why it would), it should only block them for < 3 seconds. Anyone seen anything like this? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance