pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedauto vacuum for opentaps
3+ messages / 3 participants
[nested] [flat]
* auto vacuum for opentaps
@ 2008-07-21 15:41 Ernesto Eduardo Medina Núñez <eemnpg@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Ernesto Eduardo Medina Núñez @ 2008-07-21 15:41 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: auto vacuum for opentaps
@ 2008-07-21 16:31 Scott Marlowe <scott.marlowe@gmail.com>
parent: Ernesto Eduardo Medina Núñez <eemnpg@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Scott Marlowe @ 2008-07-21 16:31 UTC (permalink / raw)
To: Ernesto Eduardo Medina Núñez <eemnpg@gmail.com>; +Cc: pgsql-admin
On Mon, Jul 21, 2008 at 9:41 AM, Ernesto Eduardo Medina Núñez
<eemnpg@gmail.com> wrote:
> 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 is your default stats target set to? Try increasing it to 50 or
100 for this db and see if the message you're seeing goes away.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: auto vacuum for opentaps
@ 2008-07-21 16:38 Alvaro Herrera <alvherre@commandprompt.com>
parent: Ernesto Eduardo Medina Núñez <eemnpg@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2008-07-21 16:38 UTC (permalink / raw)
To: Ernesto Eduardo Medina Núñez <eemnpg@gmail.com>; +Cc: pgsql-admin
Ernesto Eduardo Medina Núñez escribió:
Hi,
> *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
The thresholds and scale_factors that came with this release were too
conservative. I suggest reducing them to 50 and 0.1, respectively.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-07-21 16:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-21 15:41 auto vacuum for opentaps Ernesto Eduardo Medina Núñez <eemnpg@gmail.com>
2008-07-21 16:31 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-07-21 16:38 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox