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 5B8939FA5DC for ; Thu, 1 Jun 2006 12:51:41 -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 26548-02 for ; Thu, 1 Jun 2006 12:51:35 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.206]) by postgresql.org (Postfix) with ESMTP id 0EE1D9FA040 for ; Thu, 1 Jun 2006 12:51:34 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id h30so235334wxd for ; Thu, 01 Jun 2006 08:51:34 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=doq2QoJAblBJOr0FZRwzNOSFKPs21wNarHwLtaqiRqtD0uR9OKe5wnLYQFlge+X9Crpr6yQHeH+IxF69CnUWB6XypTxks5aSYxzpgo2bI8S+lRuj/oMea9YHV789uVGlVwwRcWhsUb360534op214gdXAaxZRyOiwOS3jt0frqw= Received: by 10.70.109.7 with SMTP id h7mr903243wxc; Thu, 01 Jun 2006 08:51:34 -0700 (PDT) Received: by 10.70.73.18 with HTTP; Thu, 1 Jun 2006 08:51:34 -0700 (PDT) Message-ID: <1d219a6f0606010851r43d38bc1j2ab647e39bf7936e@mail.gmail.com> Date: Thu, 1 Jun 2006 11:51:34 -0400 From: "Chris Hoover" To: "Sriram Dandapani" Subject: Re: autovacuum Cc: pgsql-admin@postgresql.org In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF04997AF3@ca-mail1.cis.local> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2315_18108930.1149177094477" References: <6992E470F12A444BB787B5C937B9D4DF04997AF3@ca-mail1.cis.local> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/2 X-Sequence-Number: 21964 ------=_Part_2315_18108930.1149177094477 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Autovacuum does not release space back to the OS. It only marks the tuples avaiable for reuse. To give the space back to the OS, you would have to run a vacuum full on the table. Chris On 6/1/06, Sriram Dandapani wrote: > > Hi > > > > I have a table about 17G in size. I did a pg_relation_size before deleting > 2 millions rows. I waited a few hours(actually overnight) and then checked > the table size with pg_relation_size. The size reported was exactly the > same. > > > > My autovacuum is set to run every 50 minutes. > > > > Settings: > > > > autovacuum = on # enable autovacuum subprocess? > > autovacuum_naptime = 3000 # time between autovacuum runs, in > secs > > autovacuum_vacuum_threshold = 10000 # min # of tuple updates before > > # vacuum > > autovacuum_analyze_threshold = 1500 # min # of tuple updates before > > # analyze > > autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before > > # vacuum > > autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before > > # analyze > > autovacuum_vacuum_cost_delay = -1 > > > > vacuum_cost_delay = 200 # 0-1000 milliseconds > > #vacuum_cost_page_hit = 1 # 0-10000 credits > > #vacuum_cost_page_miss = 10 # 0-10000 credits > > #vacuum_cost_page_dirty = 20 # 0-10000 credits > > vacuum_cost_limit = 50 > > > > > > work_mem = 802400 # min 64, size in KB > > maintenance_work_mem = 102400 # min 1024, size in KB > > #max_stack_depth = 2048 # min 100, size in KB > > > > # - Free Space Map - > > max_fsm_pages = 2000000 > > > > > > Why doesn't pg_relation_size take the autovacuum maintenance into effect > ------=_Part_2315_18108930.1149177094477 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Autovacuum does not release space back to the OS.  It only marks the tuples avaiable for reuse.  To give the space back to the OS, you would have to run a vacuum full on the table.

Chris

On 6/1/06, Sriram Dandapani <sdandapani@counterpane.com> wrote:

Hi

 

I have a table about 17G in size. I did a pg_relation_size before deleting 2 millions rows. I waited a few hours(actually overnight) and then checked the table size with pg_relation_size. The size reported was exactly the same.

 

My autovacuum is set to run every 50 minutes.

 

Settings:

 

autovacuum = on                 # enable autovacuum subprocess?

autovacuum_naptime = 3000               # time between autovacuum runs, in secs

autovacuum_vacuum_threshold = 10000     # min # of tuple updates before

                                        # vacuum

autovacuum_analyze_threshold = 1500     # min # of tuple updates before

                                        # analyze

autovacuum_vacuum_scale_factor = 0.4    # fraction of rel size before

                                        # vacuum

autovacuum_analyze_scale_factor = 0.2   # fraction of rel size before

                                        # analyze

autovacuum_vacuum_cost_delay = -1

 

vacuum_cost_delay = 200                 # 0-1000 milliseconds

#vacuum_cost_page_hit = 1               # 0-10000 credits

#vacuum_cost_page_miss = 10             # 0-10000 credits

#vacuum_cost_page_dirty = 20            # 0-10000 credits

vacuum_cost_limit = 50

 

 

work_mem = 802400                       # min 64, size in KB

maintenance_work_mem = 102400           # min 1024, size in KB

#max_stack_depth = 2048                 # min 100, size in KB

 

# - Free Space Map -

max_fsm_pages = 2000000

 

 

Why doesn't pg_relation_size take the autovacuum maintenance into effect


------=_Part_2315_18108930.1149177094477--