Skip site navigation (1) Skip section navigation (2)

Re: Problem with pgstat timneouts

From: "Benjamin Krajmalnik" <kraj(at)servoyant(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-admin" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Problem with pgstat timneouts
Date: 2011-12-22 18:55:53
Message-ID: F4E6A2751A2823418A21D4A160B689888CA5B6@fletch.stackdump.local (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)sss(dot)pgh(dot)pa(dot)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(at)servoyant(dot)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

In response to

Responses

pgsql-admin by date

Next:From: Craig RingerDate: 2011-12-23 03:18:48
Subject: Re: User password encryption using a stronger hashing function?
Previous:From: Tom LaneDate: 2011-12-22 18:45:22
Subject: Re: Problem with pgstat timneouts

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group