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@xxxxxxxxxxxxx] > Sent: Thursday, December 22, 2011 11:45 AM > To: Benjamin Krajmalnik > Cc: pgsql-admin > Subject: Re: Problem with pgstat timneouts > > "Benjamin Krajmalnik" <kraj@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
