X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0702FD1C969 for ; Tue, 16 Mar 2004 05:32:36 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 22059-08 for ; Tue, 16 Mar 2004 01:32:35 -0400 (AST) Received: from outbound.mailhop.org (outbound.mailhop.org [63.208.196.171]) by svr1.postgresql.org (Postfix) with ESMTP id 29D0DD1B9BC for ; Tue, 16 Mar 2004 01:32:34 -0400 (AST) Received: from ool-4350c7ad.dyn.optonline.net ([67.80.199.173] helo=zeut.net) by outbound.mailhop.org with asmtp (TLSv1:AES256-SHA:256) (Exim 4.20) id 1B37Bo-0005kv-VO; Tue, 16 Mar 2004 00:32:33 -0500 Message-ID: <4056916B.5090204@zeut.net> Date: Tue, 16 Mar 2004 00:32:27 -0500 From: "Matthew T. O'Connor" User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Joe Conway Cc: Tom Lane , pgsql-performance , Josh Berkus Subject: Re: rapid degradation after postmaster restart References: <4052661D.5060505@joeconway.com> <14884.1079150555@sss.pgh.pa.us> <4055F343.6070600@joeconway.com> <405614CA.5030101@zeut.net> <405689A4.4080406@joeconway.com> In-Reply-To: <405689A4.4080406@joeconway.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Mail-Handler: MailHop Outbound by DynDNS.org X-Report-Abuse-To: abuse@dyndns.org X-MHO-User: zeut X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/214 X-Sequence-Number: 6070 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...) .