pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matthew T. O'Connor <matthew@zeut.net>
To: Joe Conway <mail@joeconway.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: Josh Berkus <josh@agliodbs.com>
Subject: Re: rapid degradation after postmaster restart
Date: Tue, 16 Mar 2004 00:32:27 -0500
Message-ID: <4056916B.5090204@zeut.net> (raw)
In-Reply-To: <405689A4.4080406@joeconway.com>
References: <4052661D.5060505@joeconway.com>
	<14884.1079150555@sss.pgh.pa.us>
	<4055F343.6070600@joeconway.com>
	<405614CA.5030101@zeut.net>
	<405689A4.4080406@joeconway.com>

Joe Conway wrote:

> Yeah, I'm sure. Snippets from the log:
>
> [...lots-o-tables...]
> [2004-03-14 12:44:48 PM] added table: specdb."public"."parametric_states"
> [2004-03-14 12:49:48 PM] Performing: VACUUM ANALYZE 
> "public"."transaction_data"
> [2004-03-14 01:29:59 PM] Performing: VACUUM ANALYZE 
> "public"."transaction_data"
> [2004-03-14 02:08:26 PM] Performing: ANALYZE "public"."out_of_spec"
> [2004-03-14 02:08:26 PM] Performing: VACUUM ANALYZE 
> "public"."transaction_data"
> [2004-03-14 02:22:44 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
> [2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE 
> "public"."out_of_spec"
> [2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE 
> "public"."transaction_data"
> [2004-03-14 03:19:51 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
> [2004-03-14 03:21:09 PM] Performing: ANALYZE "public"."parametric_states"
> [2004-03-14 03:54:57 PM] Performing: ANALYZE "public"."out_of_spec"
> [2004-03-14 03:54:57 PM] Performing: VACUUM ANALYZE 
> "public"."transaction_data"
> [2004-03-14 04:07:52 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
> [2004-03-14 04:09:33 PM] Performing: ANALYZE 
> "public"."equip_status_history"
> [2004-03-14 04:09:33 PM] Performing: VACUUM ANALYZE 
> "public"."parametric_states"
> [2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE 
> "public"."out_of_spec"
> [2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE 
> "public"."transaction_data"
> [2004-03-14 04:56:35 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
> [2004-03-14 04:58:32 PM] Performing: ANALYZE "public"."parametric_states"
> [2004-03-14 05:28:58 PM] added database: specdb


Yeah, you're right.....

> This is the entire period of the first test, with default autovac 
> settings. The table "public"."transaction_data" is the one with 28 
> million active rows. The entire test run inserts about 600 x 600 = 
> 360,000 rows, out of which roughly two-thirds are later deleted.


Strange... I wonder if this is some integer overflow problem.  There was 
one reported recently and fixed as of CVS head yesterday, you might try 
that, however without the -d2 output I'm only guessing at why 
pg_autovacuum is vacuuming so much / so often.

> I can try. The server belongs to another department, and they are 
> under the gun to get back on track with their testing. Also, they 
> compiled without debug symbols, so I need to get permission to recompile.


Good luck, I hope you can get permission.  Would e nice to fix this 
little crash.

>> Yes I would be very curious to see the results with the vacuum delay 
>> patch installed (is that patch applied to HEAD?)
>
>
> Any idea where I can get my hands on the latest version. I found the 
> original post from Tom, but I thought there was a later version with 
> both number of pages and time to sleep as knobs.


I think Jan posted one a while back....  [searches archives...]  But I 
must say I'm at a loss to find it in the archives.  Anyone know where a 
good delay patch is for 7.4?   If we can't find one, any chance you can 
do some testing with CVS HEAD just to see if that works any better.  I 
know there has been a fair amount of work done to improve this situation 
(not just vacuum delay, but ARC etc...)
.



view thread (26+ messages)  latest in thread

Message-ID: <4056916B.5090204@zeut.net>
Permalink:  ../4056916B.5090204@zeut.net/
Also on:    postgresql.org/message-id/4056916B.5090204@zeut.net

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: matthew@zeut.net, mail@joeconway.com, tgl@sss.pgh.pa.us, josh@agliodbs.com
  Subject: Re: rapid degradation after postmaster restart
  In-Reply-To: <4056916B.5090204@zeut.net>

* 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