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 520C1D1C9C0; Thu, 20 Nov 2003 07:24:06 +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 76556-01; Thu, 20 Nov 2003 03:23:36 -0400 (AST) Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [192.108.102.143]) by svr1.postgresql.org (Postfix) with ESMTP id 75C9DD1C9E5; Thu, 20 Nov 2003 03:23:33 -0400 (AST) Received: from myrealbox.com shridhar_daithankar@smtp-send.myrealbox.com [202.54.11.72] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 3.44 $ on Novell NetWare via secured & encrypted transport (TLS); Thu, 20 Nov 2003 00:23:33 -0700 Message-ID: <3FBC6BED.9090809@myrealbox.com> Date: Thu, 20 Nov 2003 12:53:25 +0530 From: Shridhar Daithankar User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031013 Thunderbird/0.3 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Josh Berkus Cc: pgsql-performance@postgresql.org, PostgreSQL-development Subject: Re: More detail on settings for pgavd? References: <200311181558.45847.josh@agliodbs.com> <3FBB79E6.6040701@myrealbox.com> <200311190906.15828.josh@agliodbs.com> In-Reply-To: <200311190906.15828.josh@agliodbs.com> 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: 200311/172 X-Sequence-Number: 4732 Josh Berkus wrote: > Shridhar, >>However I do not agree with this logic entirely. It pegs the next vacuum >>w.r.t current table size which is not always a good thing. > > > No, I think the logic's fine, it's the numbers which are wrong. We want to > vacuum when updates reach between 5% and 15% of total rows. NOT when > updates reach 110% of total rows ... that's much too late. Well, looks like thresholds below 1 should be norm rather than exception. > Hmmm ... I also think the threshold level needs to be lowered; I guess the > purpose was to prevent continuous re-vacuuuming of small tables? > Unfortunately, in the current implementation, the result is tha small tables > never get vacuumed at all. > > So for defaults, I would peg -V at 0.1 and -v at 100, so our default > calculation for a table with 10,000 rows is: > > 100 + ( 0.1 * 10,000 ) = 1100 rows. I would say -V 0.2-0.4 could be great as well. Fact to emphasize is that thresholds less than 1 should be used. >>Furthermore analyze threshold depends upon inserts+updates. I think it >>should also depends upon deletes for obvious reasons. > Yes. Vacuum threshold is counting deletes, I hope? It does. > My comment about the frequency of vacuums vs. analyze is that currently the > *default* is to analyze twice as often as you vacuum. Based on my > experiece as a PG admin on a variety of databases, I believe that the default > should be to analyze half as often as you vacuum. OK. >>I am all for experimentation. If you have real life data to play with, I >>can give you some patches to play around. > I will have real data very soon ..... I will submit a patch that would account deletes in analyze threshold. Since you want to delay the analyze, I would calculate analyze count as n=updates + inserts *-* deletes Rather than current "n = updates + inserts". Also update readme about examples and analyze frequency. What does statistics gather BTW? Just number of rows or something else as well? I think I would put that on Hackers separately. I am still wary of inverting vacuum analyze frequency. You think it is better to set inverted default rather than documenting it? Shridhar