pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Benjamin Krajmalnik <kraj@servoyant.com>
To: Benjamin Krajmalnik <kraj@servoyant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Problem with pgstat timneouts
Date: Fri, 30 Dec 2011 11:49:03 -0700
Message-ID: <F4E6A2751A2823418A21D4A160B689888CA5FE@fletch.stackdump.local> (raw )
In-Reply-To: <F4E6A2751A2823418A21D4A160B689888CA5B6@fletch.stackdump.local >
References: <F4E6A2751A2823418A21D4A160B689888CA5A0@fletch.stackdump.local >
<16795.1324576473@sss.pgh.pa.us >
<F4E6A2751A2823418A21D4A160B689888CA5AE@fletch.stackdump.local >
<17671.1324579522@sss.pgh.pa.us >
<F4E6A2751A2823418A21D4A160B689888CA5B6@fletch.stackdump.local >
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
>
> checkpoint_segments = 1024 # in logfile segments, min 1,
> 16MB each
> checkpoint_timeout = 60min # range 30s-1h
> checkpoint_completion_target = 0.85 # checkpoint target duration,
> 0.0 - 1.0
> #checkpoint_warning = 30s # 0 disables
>
> select * from pg_stat_bgwriter gives ne the following:
> checkpoints_timed - 6887
> checkpoints_req - 36
>
> A snapshot from the log (I am logging checkpoints)
>
> 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
>
>
>
> 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
>
> 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.
>
>
> > -----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" <kraj@servoyant.com> 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
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
view thread (6+ messages)
Message-ID: <F4E6A2751A2823418A21D4A160B689888CA5FE@fletch.stackdump.local>
Permalink: ../F4E6A2751A2823418A21D4A160B689888CA5FE@fletch.stackdump.local/
Also on: postgresql.org/message-id/F4E6A2751A2823418A21D4A160B689888CA5FE@fletch.stackdump.local
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: kraj@servoyant.com, tgl@sss.pgh.pa.us
Subject: Re: Problem with pgstat timneouts
In-Reply-To: <F4E6A2751A2823418A21D4A160B689888CA5FE@fletch.stackdump.local>
References: <F4E6A2751A2823418A21D4A160B689888CA5A0@fletch.stackdump.local> <16795.1324576473@sss.pgh.pa.us> <F4E6A2751A2823418A21D4A160B689888CA5AE@fletch.stackdump.local> <17671.1324579522@sss.pgh.pa.us> <F4E6A2751A2823418A21D4A160B689888CA5B6@fletch.stackdump.local> <F4E6A2751A2823418A21D4A160B689888CA5FE@fletch.stackdump.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox