Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id EAAAE6501AC for ; Mon, 21 Jul 2008 12:42:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 24620-01-3 for ; Mon, 21 Jul 2008 12:41:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.29]) by postgresql.org (Postfix) with ESMTP id CF6506501AE for ; Mon, 21 Jul 2008 12:41:52 -0300 (ADT) Received: by yx-out-2324.google.com with SMTP id 8so208165yxb.73 for ; Mon, 21 Jul 2008 08:41:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:mime-version:content-type; bh=fp2RkcMqWq6HeKicqHVkyLk1AF6kVLs8yAVeXOtP7y4=; b=NDkK7GieRe+um9SSnAzmFdq5fxlMwoNx/J2JJh7/RPUoX4E5iAdLD+6PqV/9lDswtr 1smR8tWwzXri04d9d4xHvvk22hlSVJiHILeJp5rscJ5SXGisuxpnnDSBpHBv1DsRUopo TpEWudm9P1uxRmPdzLYWU6S4UnE+ddVt21TaM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=GO9Fff5bmGb8hEuEX0t+0JJCZ2WURikF1FOvakR5AObxSIRH5AKCDYOqDwwUEno8Pi +mddn5iUmav7aZ4hpWOnUXi9ZHjl2Qk0eWo3qWa7lj10Dn1/pB0KD0Jtz2L+GuKynzlB VKhxC1abRL5pkBo4q1mZYh+hAKDZY1qiqbrC4= Received: by 10.151.27.15 with SMTP id e15mr1612035ybj.84.1216654911573; Mon, 21 Jul 2008 08:41:51 -0700 (PDT) Received: by 10.151.41.7 with HTTP; Mon, 21 Jul 2008 08:41:51 -0700 (PDT) Message-ID: <18cc0be90807210841l637556ffl7a239a10bc45c285@mail.gmail.com> Date: Mon, 21 Jul 2008 09:41:51 -0600 From: "=?ISO-8859-1?Q?Ernesto_Eduardo_Medina_N=FA=F1ez?=" To: pgsql-admin@postgresql.org Subject: auto vacuum for opentaps MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_49593_13340259.1216654911557" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200807/179 X-Sequence-Number: 29789 ------=_Part_49593_13340259.1216654911557 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi I installed the open source ERP Opentaps which have *802* tables and using PostgreSQL as backend, and also filled the tables with demo data. When I browse the tables using PgAdmin3 I get messages saying that:* The estimated row count on the table "some_table" deviates significantly from the actual row count. You should run vacuum analyze. *So I do it again and again with each table that throws me the message until I get tired, then I went to the database's root and click on maintenance, vacuum analyze. Then a long process begin, after that I continue browsing the tables but I'm still getting the messages, so I suppose that I'm not doing the vacuum correctly. What I want It's to make auto vacuums in a general way, NOT table by table because there are 802, you know what I mean... I edited my postgresql.conf like this, but I'm not sure if that's enough, take a look please and give some feed back please: #--------------------------------------------------------------------------- # RUNTIME STATISTICS #--------------------------------------------------------------------------- # - Statistics Monitoring - #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off # - Query/Index Statistics Collector - *stats_start_collector = on* #stats_command_string = off #stats_block_level = off *stats_row_level = on* #stats_reset_on_server_start = off #--------------------------------------------------------------------------- # AUTOVACUUM PARAMETERS #--------------------------------------------------------------------------- *autovacuum = on * # enable autovacuum subprocess? #autovacuum_naptime = 60 # time between autovacuum runs, in secs #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before # vacuum #autovacuum_analyze_threshold = 500 # 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 # default vacuum cost delay for autovac, -1 means use vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for autovac, -1 means use vacuum_cost_limit ------=_Part_49593_13340259.1216654911557 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi I installed the open source ERP Opentaps which have 802 tables and using PostgreSQL as backend, and also filled the tables with demo data.

When I browse the tables using PgAdmin3 I get messages saying that:
The estimated row count on the table "some_table" deviates significantly from the actual row count. You should run vacuum analyze.

So I do it again and again with each table that throws me the message until I get tired, then I went to the database's root and click on maintenance, vacuum analyze.
Then a long process begin, after that I continue browsing the tables but I'm still getting the messages, so I suppose that I'm not doing the vacuum correctly.

What I want It's to make auto vacuums  in a general way, NOT table by table because there are 802, you know what I mean...

I edited my postgresql.conf like this, but I'm not sure if that's enough, take a look please and give some feed back please:

#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------

# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off

# - Query/Index Statistics Collector -

stats_start_collector = on
#stats_command_string = off
#stats_block_level = off
stats_row_level = on
#stats_reset_on_server_start = off


#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------

autovacuum = on                 # enable autovacuum subprocess?
#autovacuum_naptime = 60                # time between autovacuum runs, in secs
#autovacuum_vacuum_threshold = 1000     # min # of tuple updates before
                                        # vacuum
#autovacuum_analyze_threshold = 500     # 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      # default vacuum cost delay for autovac, -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for autovac, -1 means use vacuum_cost_limit



------=_Part_49593_13340259.1216654911557--