X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B7F049FA3AD for ; Wed, 17 May 2006 12:53:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 40603-03 for ; Wed, 17 May 2006 12:53:45 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.199]) by postgresql.org (Postfix) with ESMTP id 710C99F9C18 for ; Wed, 17 May 2006 12:53:45 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id t16so8231wxc for ; Wed, 17 May 2006 08:53:44 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=j4bxU88Qw/53C8eL/Gp3xuyPyLPo2u9IgwdxCh0QPSvksYskKZuOmCaoZE9hAAihL9uaQ1cWMMuHuDy8NhzzAPHmx8xXWWpEDZJLOOJBMfFW+RShCrEdQkKlVgXfzPE24NueeEukrtgRev1JfuSfhBlHWnedN+pxxUiTmTpBqFQ= Received: by 10.70.111.12 with SMTP id j12mr1341626wxc; Wed, 17 May 2006 08:53:44 -0700 (PDT) Received: by 10.70.80.16 with HTTP; Wed, 17 May 2006 08:53:44 -0700 (PDT) Message-ID: <1d219a6f0605170853j3d7c38a1n3ebb2305a12d915e@mail.gmail.com> Date: Wed, 17 May 2006 11:53:44 -0400 From: "Chris Hoover" To: "pgsql-admin@postgresql.org" Subject: Figuring autovacuum settings MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_105069_25564708.1147881224753" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/235 X-Sequence-Number: 21767 ------=_Part_105069_25564708.1147881224753 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Ok, I'm still working on the possibility of turning autovacuum on. Question, I have a tables that are multi GB. When I run the threshold calculations using "normal" values, it seems that my large important tables would almost never be vacuumed. Here is an example, in my largest database= , my most important table has 1,855,970 tuples in it. However, this is a table with 3 years of data. It has data loaded into it every day and is ke= y to the majority of our queries. In the past 16 hours or so I have had the following activity (via pg_stat_user_indexes) - 2960 tuples inserted and 40389 tuples updated, and no tuples deleted. If I understand the threshold calculations, then I need to run with autovacuum_vacuum_scale_factor set to between .005 and .001. Does this see= m to be to small? Here is the math: vacuum threshold =3D vacuum base threshold + vacuum scale factor * number o= f tuples vacuum_threshold =3D 200 + (.001*1,855,970) vacuum_threshold =3D 2,056 vacuum_threshold =3D 200 + (.005*1,855,970) vacuum_threshold =3D 9,480 It would seem with the numbers given that I should set it somewhere in this range. Do you agree? If you don't agree, where would you recomment setting them. This is my largest database and is over 110GB. It has tables with 305,702,000 reltuples down to about 500,000 reltuples (for important tables). I'm just not sure how to properly set the numbers. My servers have databases in the folowing ranges: Server 1 66 GB to 100 MB Server 2 117 GB to 1.70 GB. Server 3 95 GB to 4 GB Server 4 83 GB to 1.2 GB While the sizes differ, the ratios should be the same since they are identical databases. Any suggestions about recommendations for the autovacuum values? Also, should I set the autovacuum just at the cluster level, or should I also set some of the larger tables with table specific values. Thanks, Chris ------=_Part_105069_25564708.1147881224753 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Ok, I'm still working on the possibility of turning autovacuum on.

Q= uestion, I have a tables that are multi GB.  When I run the threshold = calculations using "normal" values, it seems that my large import= ant tables would almost never be vacuumed.  Here is an example, in my = largest database, my most important table has 1,855,970 tuples in it. = However, this is a table with 3 years of data.  It has data loaded in= to it every day and is key to the majority of our queries.  In the pas= t 16 hours or so I have had the following activity (via pg_stat_user_indexe= s) - 2960 tuples inserted and 40389 tuples updated, and no tuples deleted.

If I understand the threshold calculations, then I need to run with= autovacuum_vacuum_scale_factor set to between .005 and .001.  Does th= is seem to be to small?

Here is the math:

vacuum threshold = =3D vacuum base threshold + vacuum scale factor * number of tuples

vacuum_threshold =3D 200 + (.001*1,855,970)
vacuum_threshold =3D= 2,056

vacuum_threshold =3D 200 + (.005*1,855,970)
vacuum_threshold =3D 9,4= 80

It would seem with the numbers given that I should set it somewhe= re in this range.  Do you agree?

If you don't agree, where woul= d you recomment setting them.

This is my largest database and is over 110GB.  It has tables = with 305,702,000 reltuples down to about 500,000 reltuples (for important t= ables).

I'm just not sure how to properly set the numbers. 
My servers have databases in the folowing ranges:
Server 1 66 GB to 100 = MB
Server 2 117 GB to 1.70 GB.
Server 3 95 GB to 4 GB
Server 4 83 = GB to 1.2 GB

While the sizes differ, the ratios should be the same s= ince they are identical databases.

Any suggestions about recommendations for the autovacuum values?&nb= sp; Also, should I set the autovacuum just at the cluster level, or should = I also set some of the larger tables with table specific values.

Tha= nks,

Chris


------=_Part_105069_25564708.1147881224753--