Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 8672AC24695 for ; Fri, 30 Dec 2011 14:49:25 -0400 (AST) Received: from [64.207.29.137] (helo=mail.illumen.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RghW4-0006q0-Bq for pgsql-admin@postgresql.org; Fri, 30 Dec 2011 18:49:25 +0000 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: Problem with pgstat timneouts Date: Fri, 30 Dec 2011 11:49:03 -0700 Message-ID: In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Problem with pgstat timneouts Thread-Index: AczA2egIMIDYVFlTSSanLF+nz7D1+gAAHYnAAZJM3KA= References: <16795.1324576473@sss.pgh.pa.us> <17671.1324579522@sss.pgh.pa.us> From: "Benjamin Krajmalnik" To: "Benjamin Krajmalnik" , "Tom Lane" Cc: "pgsql-admin" X-Host-Lookup-Failed: Reverse DNS lookup failed for 64.207.29.137 (failed) X-Pg-Spam-Score: -1.1 (-) X-Archive-Number: 201112/154 X-Sequence-Number: 38912 Just a WAG - is it possible this is being caused by the connection pooler (pgbouncer in my case)? > -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of Benjamin Krajmalnik > Sent: Thursday, December 22, 2011 11:56 AM > To: Tom Lane > Cc: pgsql-admin > Subject: Re: [ADMIN] Problem with pgstat timneouts >=20 > checkpoint_segments =3D 1024 # in logfile segments, min = 1, > 16MB each > checkpoint_timeout =3D 60min # range 30s-1h > checkpoint_completion_target =3D 0.85 # checkpoint target = duration, > 0.0 - 1.0 > #checkpoint_warning =3D 30s # 0 disables >=20 > select * from pg_stat_bgwriter gives ne the following: > checkpoints_timed - 6887 > checkpoints_req - 36 >=20 > A snapshot from the log (I am logging checkpoints) >=20 > 2011-12-22 01:04:33 MST LOG: checkpoint starting: time > 2011-12-22 01:12:32 MST WARNING: pgstat wait timeout > 2011-12-22 01:12:32 MST WARNING: pgstat wait timeout > 2011-12-22 01:13:12 MST WARNING: pgstat wait timeout > 2011-12-22 01:13:12 MST WARNING: pgstat wait timeout > 2011-12-22 01:13:12 MST WARNING: pgstat wait timeout > 2011-12-22 01:13:18 MST WARNING: pgstat wait timeout > 2011-12-22 01:13:18 MST WARNING: pgstat wait timeout > 2011-12-22 01:35:35 MST WARNING: pgstat wait timeout > 2011-12-22 01:35:35 MST WARNING: pgstat wait timeout > 2011-12-22 01:38:32 MST WARNING: pgstat wait timeout > 2011-12-22 01:38:32 MST WARNING: pgstat wait timeout > 2011-12-22 01:41:28 MST LOG: checkpoint complete: wrote 329593 buffers > (41.9%); 0 transaction log file(s) added, 0 removed, 731 rec >=20 >=20 >=20 > 2011-12-22 09:04:33 MST LOG: checkpoint starting: time > 2011-12-22 09:06:54 MST WARNING: pgstat wait timeout > 2011-12-22 09:06:54 MST WARNING: pgstat wait timeout > 2011-12-22 09:06:54 MST WARNING: pgstat wait timeout > 2011-12-22 09:07:33 MST WARNING: pgstat wait timeout > 2011-12-22 09:07:33 MST WARNING: pgstat wait timeout > 2011-12-22 09:07:33 MST WARNING: pgstat wait timeout > 2011-12-22 09:13:41 MST WARNING: pgstat wait timeout > 2011-12-22 09:22:07 MST ERROR: canceling autovacuum task > 2011-12-22 09:22:07 MST CONTEXT: automatic vacuum of table > "ishield.public.tbltmptests" > 2011-12-22 09:28:41 MST WARNING: pgstat wait timeout > 2011-12-22 09:28:41 MST WARNING: pgstat wait timeout > 2011-12-22 09:31:35 MST WARNING: pgstat wait timeout > 2011-12-22 09:31:35 MST WARNING: pgstat wait timeout > 2011-12-22 09:33:16 MST WARNING: pgstat wait timeout > 2011-12-22 09:33:16 MST WARNING: pgstat wait timeout > 2011-12-22 09:33:55 MST WARNING: pgstat wait timeout > 2011-12-22 09:33:55 MST WARNING: pgstat wait timeout > 2011-12-22 09:33:55 MST WARNING: pgstat wait timeout > 2011-12-22 09:35:10 MST WARNING: pgstat wait timeout > 2011-12-22 09:35:10 MST WARNING: pgstat wait timeout > 2011-12-22 09:35:10 MST ERROR: canceling autovacuum task > 2011-12-22 09:35:10 MST CONTEXT: automatic vacuum of table > "ishield.public.tbltmptests" > 2011-12-22 09:36:21 MST WARNING: pgstat wait timeout > 2011-12-22 09:36:21 MST WARNING: pgstat wait timeout > 2011-12-22 09:37:02 MST WARNING: pgstat wait timeout > 2011-12-22 09:37:02 MST WARNING: pgstat wait timeout > 2011-12-22 09:37:02 MST WARNING: pgstat wait timeout > 2011-12-22 09:37:43 MST WARNING: pgstat wait timeout > 2011-12-22 09:37:43 MST WARNING: pgstat wait timeout > 2011-12-22 09:38:20 MST WARNING: pgstat wait timeout > 2011-12-22 09:38:20 MST WARNING: pgstat wait timeout > 2011-12-22 09:38:59 MST WARNING: pgstat wait timeout > 2011-12-22 09:38:59 MST WARNING: pgstat wait timeout > 2011-12-22 09:38:59 MST WARNING: pgstat wait timeout > 2011-12-22 09:42:33 MST LOG: checkpoint complete: wrote 362856 buffers > (46.1%); 0 transaction log file(s) added, 0 removed, 908 rec >=20 > I do have an aggressive autovacuum because I have some tables that get > updated thousands of times per minute and otherwise they bloat > terribly, > but it is very seldom that I see cancelation of autovacuum, and only on > this table, which is a temporary table used for the insertion of the > data, in which records are inserted and deleted - never updated. >=20 >=20 > > -----Original Message----- > > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Sent: Thursday, December 22, 2011 11:45 AM > > To: Benjamin Krajmalnik > > Cc: pgsql-admin > > Subject: Re: [ADMIN] Problem with pgstat timneouts > > > > "Benjamin Krajmalnik" writes: > > > The only thing which I see when I run iostat is load on the drives > > every > > > so often. Mifd0 is the database, mifd1 is the pg_xlog. > > > > Hmm. Maybe checkpoints or something else saturating your drives for > a > > little while? You might want to be more aggressive about smoothing > out > > checkpoints, cf > > http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm > > At the very least I'd enable checkpoint logging for awhile and see > > if there seems to be any correlation to the response-time glitches. > > > > regards, tom lane >=20 > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin