Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 783046328BD for ; Sat, 5 Jun 2010 11:21:06 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 66681-04 for ; Sat, 5 Jun 2010 14:20:57 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com [209.85.212.46]) by mail.postgresql.org (Postfix) with ESMTP id 44BFA632508 for ; Sat, 5 Jun 2010 11:20:59 -0300 (ADT) Received: by vws5 with SMTP id 5so1066656vws.19 for ; Sat, 05 Jun 2010 07:20:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=wenU/zX8hk0xD2PDGEeFg5w8Kb6JGH08aL0htkchf3I=; b=F/Bhj2eUmdGqv+Xz2GY9ohoE0mTknogeAL1eJDLF7dDMfzVWqk/GgiUasX1z7LhpOT ehYDQZQHcgxGx6Twb1EgnJK2m43THSJQutooogqXK6fOh94+TeyfOwxxfIveLFEdoyA7 QT7DJcWw/PAhVKnyt/PnwcloDTAfiAzXkj7+c= 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:content-transfer-encoding; b=O2gFhCR9PlVwkKnz9kY1kaqEoIjs9ukKHwvXMpYfYV0uRw+boYrtLfKTDFZoI8BIXT 2EqloIo3K6e6CdrsNJC3RgjQEHdavghunitNM9XmERC2kuErcEVFFwY5JincAAq2LgCA TnIN414q8YmhgVB76vsxL7x/2ywPVIAi3rR4U= MIME-Version: 1.0 Received: by 10.224.37.222 with SMTP id y30mr7164120qad.103.1275747657070; Sat, 05 Jun 2010 07:20:57 -0700 (PDT) Received: by 10.220.186.199 with HTTP; Sat, 5 Jun 2010 07:20:57 -0700 (PDT) In-Reply-To: References: Date: Sat, 5 Jun 2010 07:20:57 -0700 Message-ID: Subject: Re: Strange performance issue apparently causd by pg_stat timeout From: Anj Adu To: Benjamin Krajmalnik Cc: pgsql-admin@postgresql.org Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201006/58 X-Sequence-Number: 34836 To clarify on the rules...I was using rules for the tables that are the entry point for the insertion into the database. On Sat, Jun 5, 2010 at 7:19 AM, Anj Adu wrote: > We run a similar system with very large volumes > > I had similar queue build ups (not related to pg_stat_timeouts though) > > I was using Rules for my partition management instead of triggers. > > Changing from rules to triggers fixed the problem. (rules were extremely = slow) > > Hope this helps you. > > On Wed, Jun 2, 2010 at 8:48 PM, Benjamin Krajmalnik = wrote: >> System is running PG 8.4.0 (I have been unable to upgrade because the sy= stem >> needs to be up 24x7), FreeBSD 7.2 amd64, 8 cores, 16GB RAM. >> >> Our application is a network monitoring system, so we are constantly >> inserting vast amounts of data (server presently processes about 50 mill= ion >> transactions per day). >> >> As digests of test points come in, they are stored in message queues on = a >> second server (running PG 8.4.4).=A0 A set of daemons process the digest= s and >> insert the data into the main database residing on a second server. >> Presently, the database has about 60GB of data. >> >> >> >> A few days ago, I noticed that the data in the message queues on the oth= er >> server was getting backed up, and then after a few minutes it would proc= ess >> and clear.=A0 This was a totally new behavior.=A0=A0 Initially I suspect= ed >> deadlocks caused by background processes which create materialized views= , so >> I stopped those, yet the behavior continued.=A0 Then I suspected server = load, >> yet CPU utilization and load was fine (l minute and 5 minute load was at= < >> 4), and iostat did not show an overly busy disk subsystem (I had seen it= at >> much higher utilization levels on both the data and log partitions witho= ut >> any performance hits). >> >> >> >> I then suspected network issues, so I checked the infrastructure (runnig= on >> Juniper Gig switches, of course non-blocking, and all of the port >> information was clean). >> >> Finally, I noticed that the logs had =93pg_stat wait timeout=94 warnings= . >> Initially I though these were caused by our checking the running process= via >> pgadmin from our office to the data center, yet even when I exited pgadm= in, >> the warnings were still there. >> >> After further testing, I saw a correlation between the data getting queu= ed >> up and the =93pg_stat wait timeout=94 warnings.=A0 As data would begin t= o queue >> up, I would see the warnings, and about a minute later it would start to >> dequeue and get stored on the server. >> >> >> >> I searched the archives and found some messages stating that this has be= en >> observed.=A0 The interesting thing is that nothing has changed on the se= rver >> and it started to manifest itself.=A0 I ran an analyze of the entire dat= abase, >> hoping this may rectify any issue, but unfortunately to no avail. >> >> >> >> Any suggestions would be deeply appreciated =96 this behavior is definit= ely >> not good. >> >> >> >> Below is a snapshot from the log files: >> >> >> >> 2010-06-02 20:59:19 MDT WARNING:=A0 pgstat wait timeout >> >> 2010-06-02 21:10:42 MDT WARNING:=A0 pgstat wait timeout >> >> 2010-06-02 21:16:21 MDT WARNING:=A0 pgstat wait timeout >> >> 2010-06-02 21:21:23 MDT WARNING:=A0 pgstat wait timeout >> >> 2010-06-02 21:25:50 MDT WARNING:=A0 pgstat wait timeout >> >> 2010-06-02 21:35:20 MDT WARNING:=A0 pgstat wait timeout >> >> 2010-06-02 21:39:13 MDT WARNING:=A0 pgstat wait timeout >> >> 2010-06-02 21:45:32 MDT WARNING:=A0 pgstat wait timeout >> >> >> >> Thanks in advance. >