X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 34B1152A21 for ; Mon, 18 Jul 2005 01:18:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 75195-03 for ; Mon, 18 Jul 2005 04:18:46 +0000 (GMT) Received: from outbound.mailhop.org (outbound.mailhop.org [63.208.196.171]) by svr1.postgresql.org (Postfix) with ESMTP id CC00E52A0D for ; Mon, 18 Jul 2005 01:18:45 -0300 (ADT) Received: from ool-4353a8a5.dyn.optonline.net ([67.83.168.165] helo=[192.168.1.102]) by outbound.mailhop.org with esmtpa (Exim 4.51) id 1DuN5Y-0006qL-8G; Mon, 18 Jul 2005 00:18:44 -0400 X-Mail-Handler: MailHop Outbound by DynDNS.org X-Originating-IP: 67.83.168.165 X-Report-Abuse-To: abuse@dyndns.org (see http://www.mailhop.org/outbound/abuse.html for abuse reporting information) X-MHO-User: zeut Message-ID: <42DB2DA3.6000909@zeut.net> Date: Mon, 18 Jul 2005 00:18:43 -0400 From: "Matthew T. O'Connor" User-Agent: Mozilla Thunderbird 1.0.2-1.3.3 (X11/20050513) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Robert Creager Cc: PGPerformance Subject: Re: Huge performance problem between 7.4.1 and 8.0.3 - References: <20050717213416.59dcb52e@thunder.logicalchaos.org> <42DB2684.9020706@zeut.net> <20050717221050.7c4ac241@thunder.logicalchaos.org> In-Reply-To: <20050717221050.7c4ac241@thunder.logicalchaos.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200507/246 X-Sequence-Number: 13487 Ok, it doesn't look like an autovacuum problem. The only other thing I can think of is that some query is doing a seq scan rather than an index scan. Have you turned on the query logging to see what queries are taking so long? Matt Robert Creager wrote: >I am, and it is. It's ANALYZING and VACUUM'ing tables every interval (5 minutes >- 8.0.3). Right now, for that last 4 hours, I'm not VACUUMing the 7.4.1 >database and it's still clicking along at < .2 second queries. Last year >(7.4.1), I noticed that it took about a week of heavy activity (for this DB) >before I'd really need a vacuum. That's when I put in the 5 min cron. > >When I first switched over to 8.0.3, I was still running the cron vacuum. I got >into big trouble when I had vacuum's backed up for 6 hours. That's when I >started noticing the query problem, and the CS numbers being high. 7.4.1 >vacuums every 5 minutes always take < 30 seconds (when I'm watching). > >Cheers, >Rob > >When grilled further on (Sun, 17 Jul 2005 23:48:20 -0400), >"Matthew T. O'Connor" confessed: > > > >>Robert Creager wrote: >> >> >> >>>For 8.03, pg_autovacuum is running. On 7.4.1, I set up a cron job to vacuum >>>analyze every 5 minutes. >>> >>> >>> >>> >>Are you sure that pg_autovacuum is doing it's job? Meaning are you sure >>it's vacuuming as often as needed? Try to run it with -d2 or so and >>make sure that it is actually doing the vacuuming needed. >> >> > > > >