Обсуждение: auto vacuum for opentaps
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
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
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.
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.