From aa@devshock.com Wed Aug 1 20:27:26 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 1CE9FDA629A for ; Wed, 1 Aug 2012 17:27:26 -0300 (ADT) Received: from mail-yw0-f46.google.com ([209.85.213.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SwfVr-0006fD-Uo for pgsql-admin@postgresql.org; Wed, 01 Aug 2012 20:27:25 +0000 Received: by yhmm54 with SMTP id m54so7787873yhm.19 for ; Wed, 01 Aug 2012 13:27:11 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=from:to:subject:date:message-id:mime-version:content-type:x-mailer :thread-index:content-language:x-gm-message-state; bh=tasL4hOMTrsthPhchAP8fo70AhDh/fh6pL17gnXhVcA=; b=DmNXsU0OYsUxFJLpNvodgSwlazqhBLeg5bK9JZX9+u3xiGG08jpt7uJeELtGfugOIf OwkF0KWVvxWewI9Nuh5/V2xKz8wEMKifP32KW10BtwPxTAPt0RDu4rSiWXizHaLapQim 77HxLQGKoA4/7eRHEKaMZDlvHqFW2LxAErlJraz9TbVHPV3KyeHClfozpSfZjDGuyE0l iJgRuKXUrhCtHWDFceosXW/lTJa3Tks5Gw5HqEV5slA7tG3PPbAn9nM7sGk7N9YOS3XR KySFrM8rYArporTuzX6isxjkO07kqNE+GLy0IpfFYQJBwLOKcxHSf4DdGKqDKR61MLA0 5ygg== Received: by 10.236.145.198 with SMTP id p46mr17955481yhj.49.1343852830996; Wed, 01 Aug 2012 13:27:10 -0700 (PDT) Received: from AnibalVAIO ([190.128.222.90]) by mx.google.com with ESMTPS id y66sm7258595yhi.10.2012.08.01.13.27.09 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 01 Aug 2012 13:27:09 -0700 (PDT) From: "Anibal David Acosta" To: Subject: Timeout error on pgstat Date: Wed, 1 Aug 2012 16:27:08 -0400 Message-ID: <00dc01cd7024$07854340$168fc9c0$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00DD_01CD7002.80743F80" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Ac1wI9c06zUgkksFQhS6iq4QmoIHtg== Content-Language: es-py X-Gm-Message-State: ALoCoQkVCD7du67UkIqPsoNnlM28w2h2Tkyfvbu9qyUZFYKJzH8wXWBtQv0g07Er4ChkIYPefhvC X-Pg-Spam-Score: -1.2 (-) X-Archive-Number: 201208/8 X-Sequence-Number: 39829 This is a multipart message in MIME format. ------=_NextPart_000_00DD_01CD7002.80743F80 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I have a lot (maybe 1 every 10 seconds) of this error WARNING: pgstat wait timeout Also this ERROR: canceling autovacuum task In the pg_stat_activity show an autovacuum process over a very used table that runs about 1 hour and then this vacuum is cancelled (according to log) I have Postgres 9.0.3 on a windows 2008 R2 running for about 1 year in same conditions, but this error is occurring about 1 week ago. Thanks!! ------=_NextPart_000_00DD_01CD7002.80743F80 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I have a lot (ma= ybe 1 every 10 seconds) of this error WARNING:  pgstat wait timeout

 

= Also this

ERROR:  canceling autova= cuum task

 

In the pg_stat_activity show an autovacuum process over a very= used table that runs about 1 hour and then this vacuum is cancelled (accor= ding to log)

 

I have Postgres 9.0.3 on a windows 2008 R2 running for about= 1 year in same conditions, but this error is occurring about 1 week ago.

 

Thanks!!

= ------=_NextPart_000_00DD_01CD7002.80743F80-- From ringerc@ringerc.id.au Thu Aug 2 02:01:02 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 56358259DA8 for ; Wed, 1 Aug 2012 23:01:02 -0300 (ADT) Received: from mail-ob0-f174.google.com ([209.85.214.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Swkij-0003FG-ED for pgsql-admin@postgresql.org; Thu, 02 Aug 2012 02:01:02 +0000 Received: by obbuo13 with SMTP id uo13so13088763obb.19 for ; Wed, 01 Aug 2012 19:00:48 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:x-gm-message-state; bh=bV7Whl6GNaF3Idvn51GGqqaNYzYopGQE5FLL/0beSs8=; b=LgQA66XgVSiSXg//RJ3PQQxY1nkFlZB/tW96QwKUb9YYceXN8xWrOtzWTXT+ENukzw 6etM6N8AbqwRztI1/A1KMgGvch8Fj+/yJsQXQuaBWp4+jil9N3Qy3CPQwstkOpwjZTjm qzWau2culgeALFrjmmaHId9p8+LVEzl1dx6ttmuRJw2vhnLPAPlaOjf8LE8Bm9f0RlIF 5wCpoR0lCnfK+5b90RMQS4UIRKCbO3zxquHIgN/70SW7QGfIJWkpePyc0G5rYXG1o9yM NEL5ZpSC/NsdEDX2hHCZJXQDcqQgAXT4FHAIquKxVyGmwuWFCZx3J6NFIINg7p59/ew8 A/pA== Received: by 10.60.2.161 with SMTP id 1mr33118861oev.48.1343872847793; Wed, 01 Aug 2012 19:00:47 -0700 (PDT) Received: from ayaki.localdomain (office1.postnewspapers.com.au. [150.101.171.128]) by mx.google.com with ESMTPS id ql3sm4228146obc.17.2012.08.01.19.00.44 (version=SSLv3 cipher=OTHER); Wed, 01 Aug 2012 19:00:47 -0700 (PDT) Message-ID: <5019DF49.9000408@ringerc.id.au> Date: Thu, 02 Aug 2012 10:00:41 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120717 Thunderbird/14.0 MIME-Version: 1.0 To: Anibal David Acosta CC: pgsql-admin@postgresql.org Subject: Re: Timeout error on pgstat References: <00dc01cd7024$07854340$168fc9c0$@devshock.com> In-Reply-To: <00dc01cd7024$07854340$168fc9c0$@devshock.com> Content-Type: multipart/alternative; boundary="------------000003090702000208070301" X-Gm-Message-State: ALoCoQkQDel7Lx1gZacl6uCY8zL1ofb1cgyM8S222og1uG+owyIxtAUM81LntslyH9wlp62VN/s2 X-Pg-Spam-Score: -0.8 (/) X-Archive-Number: 201208/9 X-Sequence-Number: 39830 This is a multi-part message in MIME format. --------------000003090702000208070301 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 08/02/2012 04:27 AM, Anibal David Acosta wrote: > > I have a lot (maybe 1 every 10 seconds) of this error WARNING: pgstat > wait timeout > A quick search suggests this can be due to excessive I/O. However, this thread: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-td5078125.html sounds very similar to your issue. I'm wondering if there's a bug lurking in there somewhere. > In the pg_stat_activity show an autovacuum process over a very used > table that runs about 1 hour and then this vacuum is cancelled > (according to log) Was there any context to the `cancelling autovacuum task' message? > > I have Postgres 9.0.3 on a windows 2008 R2 running for about 1 year > in same conditions, but this error is occurring about 1 week ago. > > The current 9.0 release is 9.0.8, so you're missing a bunch of bug fixes. http://www.postgresql.org/docs/current/static/release-9-0-8.html Consider updating. You don't need to do a dump and reload or use pg_upgrade, since it's only a minor version update. Stop the DB, install the new binaries, start the DB. However, I don't see any fixes related to the stats writer in the relnotes from the 9.0 series. -- Craig Ringer --------------000003090702000208070301 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 08/02/2012 04:27 AM, Anibal David Acosta wrote:

I have a lot (maybe 1 every 10 seconds) of this error WARNING:  pgstat wait timeout

A quick search suggests this can be due to excessive I/O. However, this thread:

http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-td5078125.html

sounds very similar to your issue. I'm wondering if there's a bug lurking in there somewhere.


In the pg_stat_activity show an autovacuum process over a very used table that runs about 1 hour and then this vacuum is cancelled (according to log)
Was there any context to the `cancelling autovacuum task' message?

 I have Postgres 9.0.3 on a windows 2008 R2 running for about 1 year in same conditions, but this error is occurring about 1 week ago.


The current 9.0 release is 9.0.8, so you're missing a bunch of bug fixes.

http://www.postgresql.org/docs/current/static/release-9-0-8.html

Consider updating. You don't need to do a dump and reload or use pg_upgrade, since it's only a minor version update. Stop the DB, install the new binaries, start the DB.

However, I don't see any fixes related to the stats writer in the relnotes from the 9.0 series.

--
Craig Ringer
--------------000003090702000208070301-- From tgl@sss.pgh.pa.us Thu Aug 2 03:59:15 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 1E89C15C4CC7 for ; Thu, 2 Aug 2012 00:59:15 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SwmZ8-000553-7l for pgsql-admin@postgresql.org; Thu, 02 Aug 2012 03:59:14 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q723x0Ab009900; Wed, 1 Aug 2012 23:59:00 -0400 (EDT) From: Tom Lane To: Craig Ringer cc: Anibal David Acosta , pgsql-admin@postgresql.org Subject: Re: Timeout error on pgstat In-reply-to: <5019DF49.9000408@ringerc.id.au> References: <00dc01cd7024$07854340$168fc9c0$@devshock.com> <5019DF49.9000408@ringerc.id.au> Comments: In-reply-to Craig Ringer message dated "Thu, 02 Aug 2012 10:00:41 +0800" Date: Wed, 01 Aug 2012 23:59:00 -0400 Message-ID: <9899.1343879940@sss.pgh.pa.us> X-Pg-Spam-Score: -0.8 (/) X-Archive-Number: 201208/10 X-Sequence-Number: 39831 Craig Ringer writes: > On 08/02/2012 04:27 AM, Anibal David Acosta wrote: >> I have a lot (maybe 1 every 10 seconds) of this error WARNING: pgstat >> wait timeout >> > A quick search suggests this can be due to excessive I/O. However, this > thread: > http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-td5078125.html > sounds very similar to your issue. I'm wondering if there's a bug > lurking in there somewhere. Yeah, a lot of us are wondering that. In principle, only a ridiculously high load could result in triggering this message, but some people can reproduce it under what apparently isn't very high load. If we could identify what's different about their setups, maybe we could figure out what's going on. regards, tom lane From aa@devshock.com Thu Aug 2 14:52:12 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 1839413A5D7F for ; Thu, 2 Aug 2012 11:52:12 -0300 (ADT) Received: from mail-yw0-f46.google.com ([209.85.213.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Swwl0-00066K-VC for pgsql-admin@postgresql.org; Thu, 02 Aug 2012 14:52:11 +0000 Received: by yhmm54 with SMTP id m54so8799019yhm.19 for ; Thu, 02 Aug 2012 07:51:58 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=from:to:cc:references:in-reply-to:subject:date:message-id :mime-version:content-type:x-mailer:thread-index:content-language :x-gm-message-state; bh=ly8cZukmOqotJkmCybK9bYAPSF3B3+WW8ViycYNpq7c=; b=az16Dbqacj0zaduxJetOcCUIr2BwYJ6hskkVApOvxPy2P8EvdjriDok/UkQ2H4QYNb Tf9+isa0Rteq5pfJOvlUc7VvlvtfOi6V4QToTr5+8dhS9gmQAkEQPykhQgmzi9UkddKx liIttxFy0kVn14Kdu3VrpeLTybwzhJZhO/PND4qvTQ1gIiz536E+IHMjy9mS2uGLU5Z3 ShrCg6zTa+QOJzWoPCtCAdRUzzOITy5wP6aKHWJUHwXkIlI7e6UAkaE+OMuEH0tY4C6u /1FSMjadiKg5gleV15r0fjBqIHCQjYbar2pgnyS0PoBBsxYJOzySnht/+6Liowes32zc n1EQ== Received: by 10.236.197.42 with SMTP id s30mr20884191yhn.64.1343919118191; Thu, 02 Aug 2012 07:51:58 -0700 (PDT) Received: from AnibalVAIO (host-1-86.b20.cvc.com.py. [186.2.211.86]) by mx.google.com with ESMTPS id w1sm5710068anm.8.2012.08.02.07.51.55 (version=TLSv1/SSLv3 cipher=OTHER); Thu, 02 Aug 2012 07:51:57 -0700 (PDT) From: "Anibal David Acosta" To: "'Craig Ringer'" Cc: References: <00dc01cd7024$07854340$168fc9c0$@devshock.com> <5019DF49.9000408@ringerc.id.au> In-Reply-To: <5019DF49.9000408@ringerc.id.au> Subject: Re: Timeout error on pgstat Date: Thu, 2 Aug 2012 10:51:54 -0400 Message-ID: <005f01cd70be$5d7813a0$18683ae0$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0060_01CD709C.D667AC20" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQHVZjQZI4NQ5X/a022xRggIqRxt1gDkh7H9ly+GjMA= Content-Language: es-py X-Gm-Message-State: ALoCoQmFxdrwEz1lPcaXXCSS2v0ZA/nnD4v2eeTnVaYV6VOX7xmrtTu+CL3jRWKeREWE3hTEd1GI X-Pg-Spam-Score: -1.5 (-) X-Archive-Number: 201208/11 X-Sequence-Number: 39832 This is a multipart message in MIME format. ------=_NextPart_000_0060_01CD709C.D667AC20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Craig, those lines appear between pgstat timeout ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "XXX" =20 The table XXX is a table with about 200 insert p/ second. No update or delete. =20 The problem apparently is just with this table because there are others autovacuum running and working fin over others tables =20 The only difference is that this table XXX has about 5millions of insert daily and all those 5millons are deleted in the night (cleanup process). =20 =20 =20 =20 De: Craig Ringer [mailto:ringerc@ringerc.id.au]=20 Enviado el: mi=E9rcoles, 01 de agosto de 2012 10:01 p.m. Para: Anibal David Acosta CC: pgsql-admin@postgresql.org Asunto: Re: [ADMIN] Timeout error on pgstat =20 On 08/02/2012 04:27 AM, Anibal David Acosta wrote: I have a lot (maybe 1 every 10 seconds) of this error WARNING: pgstat wait timeout A quick search suggests this can be due to excessive I/O. However, this thread: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-td5078125.html sounds very similar to your issue. I'm wondering if there's a bug lurking in there somewhere. In the pg_stat_activity show an autovacuum process over a very used table that runs about 1 hour and then this vacuum is cancelled (according to log) Was there any context to the `cancelling autovacuum task' message? I have Postgres 9.0.3 on a windows 2008 R2 running for about 1 year in same conditions, but this error is occurring about 1 week ago. =20 The current 9.0 release is 9.0.8, so you're missing a bunch of bug fixes. http://www.postgresql.org/docs/current/static/release-9-0-8.html Consider updating. You don't need to do a dump and reload or use pg_upgrade, since it's only a minor version update. Stop the DB, install the new binaries, start the DB. However, I don't see any fixes related to the stats writer in the relnotes from the 9.0 series. -- Craig Ringer ------=_NextPart_000_0060_01CD709C.D667AC20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Craig, those lines appear between pgstat timeout

ERROR:=A0 canceling autovacuum task

CONTEXT:=A0 automatic vacuum of table "XXX"<= /o:p>

 

The table XXX is a table with about 200 insert p/ secon= d. No update or delete.

 

The problem apparentl= y is just with this table because there are others autovacuum running and w= orking fin over others tables

 

The only differ= ence is that this table XXX has about 5millions =A0of insert daily and all = those 5millons are deleted in the night (cleanup process).

 

 

 

 

De: Craig Ringer [mailto:ringerc@ringerc.id.au]
Env= iado el: mi=E9rcoles, 01 de agosto de 2012 10:01 p.m.
Para: A= nibal David Acosta
CC: pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] Timeout error on pgstat

 

On 08/02/2= 012 04:27 AM, Anibal David Acosta wrote:

I have a lot (mayb= e 1 every 10 seconds) of this error WARNING:  pgstat wait timeout=

A quick search suggests t= his can be due to excessive I/O. However, this thread:

h= ttp://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-td5078125.html

sounds very similar to your issue. I'm wondering if there's a bug= lurking in there somewhere.



Was there any context to= the `cancelling autovacuum task' message?

= The current 9.0 release is 9.0.8, so you're missing a bunch of bug fixes.
http://www.postgresql.org/docs/current/static/release-9-0-8.html

Consider updating. You don't need to do a dump and reload or use p= g_upgrade, since it's only a minor version update. Stop the DB, install the= new binaries, start the DB.

However, I don't see any fixes related = to the stats writer in the relnotes from the 9.0 series.

--
Craig= Ringer

= ------=_NextPart_000_0060_01CD709C.D667AC20-- From aa@devshock.com Thu Aug 2 15:29:32 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 40A3813A5D7F for ; Thu, 2 Aug 2012 12:29:32 -0300 (ADT) Received: from mail-gh0-f174.google.com ([209.85.160.174]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SwxL7-00041p-Ky for pgsql-admin@postgresql.org; Thu, 02 Aug 2012 15:29:31 +0000 Received: by ghrr11 with SMTP id r11so2374882ghr.19 for ; Thu, 02 Aug 2012 08:29:16 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=from:to:cc:references:in-reply-to:subject:date:message-id :mime-version:content-type:x-mailer:thread-index:content-language :x-gm-message-state; bh=SZ45DkMidXL519Heyt2BWdMN7Wlp/jGZTMg7t1TUpBI=; b=SeB6QJSAHre469FJbcv9QHCSd4rUmNbomwJrBpYYcm/jfOg3Y96mx7ZeOa/XFvkVCI dITR6gVMYm78GFWDOZwsMXQO8UwO81lVyXLrpX9bC9H71S6EpMaOYxLaGDlnEqAYzxyG akjpK4yp9+G1LTgwu3J6ZjZWkQowBz156NcyUa/pvDQKwW9HjVDVucd47NbXGNYJCEF4 pqxxInpNgJcIHTh6ZhoODJNZWMmVGvmKJnyXS1faccQkPaJtGuEJFqO4UvCFar3BSFoY 2F4HyVpRPhXzkmLGshOyVQ63DfjN4FHRZbUNqn+b588WCcZZbrVCAn6POKMspOdhJKyS 8ZWA== Received: by 10.236.151.110 with SMTP id a74mr21116835yhk.35.1343921356040; Thu, 02 Aug 2012 08:29:16 -0700 (PDT) Received: from AnibalVAIO (host-1-86.b20.cvc.com.py. [186.2.211.86]) by mx.google.com with ESMTPS id q17sm2796110anm.12.2012.08.02.08.29.12 (version=TLSv1/SSLv3 cipher=OTHER); Thu, 02 Aug 2012 08:29:14 -0700 (PDT) From: "Anibal David Acosta" To: "'Craig Ringer'" Cc: References: <00dc01cd7024$07854340$168fc9c0$@devshock.com> <5019DF49.9000408@ringerc.id.au> In-Reply-To: Subject: Re: Timeout error on pgstat Date: Thu, 2 Aug 2012 11:29:11 -0400 Message-ID: <009801cd70c3$92f277f0$b8d767d0$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0099_01CD70A2.0BE3BE20" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQHVZjQZI4NQ5X/a022xRggIqRxt1gDkh7H9ly+GjMCAAA9+MA== Content-Language: es-py X-Gm-Message-State: ALoCoQkeceLhuxLoA04qUn7NJUA5v/+YrVBJfYNmO29nP4dRRhvG82rT5huaYH151UXsT/AFtLFw X-Pg-Spam-Score: -1.5 (-) X-Archive-Number: 201208/12 X-Sequence-Number: 39833 This is a multipart message in MIME format. ------=_NextPart_000_0099_01CD70A2.0BE3BE20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Maybe this can contribute=85 When I run a query over this table XXX, and immediately try to cancel the query, the cancel never completes. I found that this situation was fixed in last release (9.0.8) =B7 Ensure sequential scans check for query cancel reasonably often (Merlin Moncure) A scan encountering many consecutive pages that contain no live tuples would not respond to interrupts meanwhile =20 Maybe in the autovacuum happen something similar? =20 =20 =20 De: Anibal David Acosta [mailto:aa@devshock.com]=20 Enviado el: jueves, 02 de agosto de 2012 10:52 a.m. Para: 'Craig Ringer' CC: 'pgsql-admin@postgresql.org' Asunto: RE: [ADMIN] Timeout error on pgstat =20 Craig, those lines appear between pgstat timeout ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "XXX" =20 The table XXX is a table with about 200 insert p/ second. No update or delete. =20 The problem apparently is just with this table because there are others autovacuum running and working fin over others tables =20 The only difference is that this table XXX has about 5millions of insert daily and all those 5millons are deleted in the night (cleanup process). =20 =20 =20 =20 De: Craig Ringer [mailto:ringerc@ringerc.id.au]=20 Enviado el: mi=E9rcoles, 01 de agosto de 2012 10:01 p.m. Para: Anibal David Acosta CC: pgsql-admin@postgresql.org Asunto: Re: [ADMIN] Timeout error on pgstat =20 On 08/02/2012 04:27 AM, Anibal David Acosta wrote: I have a lot (maybe 1 every 10 seconds) of this error WARNING: pgstat wait timeout A quick search suggests this can be due to excessive I/O. However, this thread: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-td5078125.html sounds very similar to your issue. I'm wondering if there's a bug lurking in there somewhere. In the pg_stat_activity show an autovacuum process over a very used table that runs about 1 hour and then this vacuum is cancelled (according to log) Was there any context to the `cancelling autovacuum task' message? I have Postgres 9.0.3 on a windows 2008 R2 running for about 1 year in same conditions, but this error is occurring about 1 week ago. =20 The current 9.0 release is 9.0.8, so you're missing a bunch of bug fixes. http://www.postgresql.org/docs/current/static/release-9-0-8.html Consider updating. You don't need to do a dump and reload or use pg_upgrade, since it's only a minor version update. Stop the DB, install the new binaries, start the DB. However, I don't see any fixes related to the stats writer in the relnotes from the 9.0 series. -- Craig Ringer ------=_NextPart_000_0099_01CD70A2.0BE3BE20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

= Maybe this can contribute…

When I run a query over this table XXX, and immediately try t= o cancel the query, the cancel never completes.

I found that this situation was fixed in last release= (9.0.8)

=B7   = ;      Ensure sequential scans check for query cancel reasonably often (Merlin= Moncure)

A scan encountering many consecutive pages that contain no live tuples = would not respond to interrupts meanwhile

 

= Maybe in the autovacuum happen something similar?

 =

 

 

De: Anibal= David Acosta [mailto:aa@devshock.com]
Enviado el: jueves, 02 de= agosto de 2012 10:52 a.m.
Para: 'Craig Ringer'
CC: 'pg= sql-admin@postgresql.org'
Asunto: RE: [ADMIN] Timeout error on pg= stat

 

Craig, those lines appear between pgstat timeo= ut

ERROR:  canceling autovacuum = task

CONTEXT:  automatic vacuum = of table "XXX"

&nb= sp;

The table XXX is a table wi= th about 200 insert p/ second. No update or delete.

 

= The problem apparently is just with this table because there are oth= ers autovacuum running and working fin over others tables=

 

The only difference is that this table XXX has about 5millions=  of insert daily and all those 5millons are deleted in the night (cle= anup process).

 =

 

 

=  

De: Craig Ringer [mailto:ringerc@ringerc.id.au] <= br>Enviado el: mi=E9rcoles, 01 de agosto de 2012 10:01 p.m.
Pa= ra: Anibal David Acosta
CC: pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] T= imeout error on pgstat

 

On 08/02/2012 04:27 AM, An= ibal David Acosta wrote:

I have a lot (maybe 1 every 10 sec= onds) of this error WARNING:  pgstat wait timeout

=

A quick se= arch suggests this can be due to excessive I/O. However, this thread:
http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-t= d5078125.html

sounds very similar to your issue. I'm wondering i= f there's a bug lurking in there somewhere.


=

In the pg_stat_activity show an autovacuum process ove= r a very used table that runs about 1 hour and then this vacuum is cancelle= d (according to log)

Was there any context to the `cancelling autovacuum task'= message?

 I have Postgres 9.0.3 on a window= s 2008 R2 running for about 1 year in same conditions, but this error is oc= curring about 1 week ago.

 

The current 9.0 release is 9.0.8, so you'= re missing a bunch of bug fixes.

http://www.postgresql.org/docs/cu= rrent/static/release-9-0-8.html

Consider updating. You don't nee= d to do a dump and reload or use pg_upgrade, since it's only a minor versio= n update. Stop the DB, install the new binaries, start the DB.

Howev= er, I don't see any fixes related to the stats writer in the relnotes from = the 9.0 series.

--
Craig Ringer

= ------=_NextPart_000_0099_01CD70A2.0BE3BE20-- From aa@devshock.com Mon Aug 6 13:49:27 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 2CFDB182A958 for ; Mon, 6 Aug 2012 10:49:27 -0300 (ADT) Received: from mail-gg0-f174.google.com ([209.85.161.174]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SyNgS-0001ej-Fs for pgsql-admin@postgresql.org; Mon, 06 Aug 2012 13:49:26 +0000 Received: by ggnl2 with SMTP id l2so2474370ggn.19 for ; Mon, 06 Aug 2012 06:49:10 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :content-type:x-mailer:thread-index:content-language :x-gm-message-state; bh=qI6uhd1ceCchrYb89LLbBnPRA01dQvzWhsGCQ8pDQe4=; b=Xgc6JVtSfrdZ+yY/TwCGNhjAgGYxkl++VRN7YnjPzvc/3lokwztgaChgpNU3T4HA/d Fu2SZWB70uBRp/b4eauECtyusA4fdgarqFy36Sbju3jCmMnnYrqJWn4ufGGWmLhatjDr i2Tnd2gy5VxxgvzfdWdgJMLAEnZxUumZw50jaVOv0QZPSGs6eQTqbvumErsl5zerpAlH FApqkjIWQU9B+84nRfdZ6c9GyGFHpMJbLxOSDN6kzvUX7r+RoFJ5NJNk2+TOguMPViZx cbcx+QN6xs9GLfhzgfF/C0nGryu2jRlE0bEt0DF6WVmlsg2RmRYSLCoA+FtD4jAhYneV xnpg== Received: by 10.236.193.105 with SMTP id j69mr9944219yhn.21.1344260950418; Mon, 06 Aug 2012 06:49:10 -0700 (PDT) Received: from AnibalVAIO (host-1-86.b20.cvc.com.py. [186.2.211.86]) by mx.google.com with ESMTPS id t63sm31724188yhd.7.2012.08.06.06.49.08 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 06 Aug 2012 06:49:09 -0700 (PDT) From: "Anibal David Acosta" To: References: In-Reply-To: Subject: Re: Timeout error on pgstat Date: Mon, 6 Aug 2012 09:49:06 -0400 Message-ID: <005b01cd73da$41093680$c31ba380$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_005C_01CD73B8.B9F859D0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Ac1wI9c06zUgkksFQhS6iq4QmoIHtgDti30Q Content-Language: es-py X-Gm-Message-State: ALoCoQnuBdoif/CEQtjB0p3UuPdgA8V2lVWCpsXtB3+aJvGvpVEN4p9yRMr9TdS+kRFoGyPnb9cL X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201208/40 X-Sequence-Number: 39861 This is a multipart message in MIME format. ------=_NextPart_000_005C_01CD73B8.B9F859D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is probably that the cause of this behavior are many many dead tuples in the table.? =20 =20 =20 =20 De: Anibal David Acosta [mailto:aa@devshock.com]=20 Enviado el: mi=E9rcoles, 01 de agosto de 2012 04:27 p.m. Para: pgsql-admin@postgresql.org Asunto: Timeout error on pgstat =20 I have a lot (maybe 1 every 10 seconds) of this error WARNING: pgstat wait timeout =20 Also this=20 ERROR: canceling autovacuum task =20 In the pg_stat_activity show an autovacuum process over a very used table that runs about 1 hour and then this vacuum is cancelled (according to log) =20 I have Postgres 9.0.3 on a windows 2008 R2 running for about 1 year in same conditions, but this error is occurring about 1 week ago. =20 Thanks!! ------=_NextPart_000_005C_01CD73B8.B9F859D0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Is probably that the cause of this behavior are many many dea= d tuples in the table.?

 

 

<= span style=3D'color:#1F497D'> 

 

=

De: Anibal David Acosta [mailto:aa@d= evshock.com]
Enviado el: mi=E9rcoles, 01 de agosto de 2012 04:27= p.m.
Para: pgsql-admin@postgresql.org
Asunto: Timeout = error on pgstat

=  

I have a lot (maybe 1 every 10 seconds= ) of this error WARNING:  pgstat wait timeout

 

Also this

ERROR:  canceling autovacuum task=

 

In the p= g_stat_activity show an autovacuum process over a very used table that runs= about 1 hour and then this vacuum is cancelled (according to log)

 

I have= Postgres 9.0.3 on a windows 2008 R2 running for about 1 year in same condi= tions, but this error is occurring about 1 week ago.

 

Thanks!!<= /p>

= ------=_NextPart_000_005C_01CD73B8.B9F859D0--