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 0F85DD1E317 for ; Tue, 16 Mar 2004 05:00:18 +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 17411-06 for ; Tue, 16 Mar 2004 01:00:16 -0400 (AST) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id 1AD6FD1D63E for ; Tue, 16 Mar 2004 01:00:15 -0400 (AST) Received: from [192.168.5.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 1086897; Mon, 15 Mar 2004 20:55:39 -0800 Message-ID: <405689A4.4080406@joeconway.com> Date: Mon, 15 Mar 2004 20:59:16 -0800 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Matthew T. O'Connor" 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> In-Reply-To: <405614CA.5030101@zeut.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/212 X-Sequence-Number: 6068 Matthew T. O'Connor wrote: > I think you understand correctly. A table with 1,000,000 rows should > get vacuumed approx every 2,000,000 changes (assuming default values for > -V ). FYI and insert and a delete count as one change, but and update > counts as two. > > Unfortunately, the running with -d2 would show the numbers that > pg_autovacuum is using to decide if it when it should vacuum or > analyze. Also, are you sure that it vacuumed more than once and > wasn't doing analyzes most of the time? 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 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. > That's unfortunate as that is the detail we need to see what > pg_autovacuum thinks is really going on. We had a similar sounding > crash on FreeBSD due to some unitialized variables that were being > printed out by the debug code, however that was fixed a long time ago. > Any chance you can look into this? 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. > 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. Thanks, Joe