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--