Обсуждение: auto vacuum during restore
Hi,
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
i've tested on 8.3 and 8.4 and i found that autovacuum works during restore uin my test scenario, which is:
a new database wit two tables. tab1 has 1M recs, tab2 has 10 recs.
When i drop the database and restore, pg_stat_all tables tells me good estimations for the number of live tuples.
But on a copy of a live database (postgres 8.3), there are zeros for n_live_tup on tables that do contain records.
When i do an ANALYZE (without vacuum) these remain 0. When i do VACUUM ANALYZE, they are filled in correctly.
I don't get it, they should have been analyzed at restore, should they not?
Cheers,
WBL
-----------non default postgresql.conf settings below------------
$ grep ^[^#] /etc/postgresql/8.3/oz/postgresql.conf|grep -e ^[^[:space:]]
data_directory = '/data/postgresql/8.3/oz' # use data in another directory
hba_file = '/etc/postgresql/8.3/oz/pg_hba.conf' # host-based authentication file
ident_file = '/etc/postgresql/8.3/oz/pg_ident.conf' # ident configuration file
external_pid_file = '/var/run/postgresql/8.3-oz.pid' # write an extra PID file
listen_addresses = '*' # what IP address(es) to listen on;
port = 5434 # (change requires restart)
max_connections = 100 # (change requires restart)
unix_socket_directory = '/var/run/postgresql' # (change requires restart)
tcp_keepalives_idle = 120 # TCP_KEEPIDLE, in seconds;
shared_buffers = 2048MB # min 128kB or max_connections*16kB
work_mem = 50MB # min 64kB
maintenance_work_mem = 200MB # min 1MB
max_fsm_pages = 3000000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 10000 # min 100, ~70 bytes each
synchronous_commit = off # immediate fsync at commit
wal_writer_delay = 500ms # 1-10000 milliseconds
commit_delay = 500 # range 0-100000, in microseconds
effective_cache_size = 4096MB
default_statistics_target = 100 # range 1-1000
log_min_duration_statement = 300000 # -1 is disabled, 0 logs all statements
log_line_prefix = '%t ip:%h ' # special values:
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
On Tue, Apr 26, 2011 at 11:36 AM, Willy-Bas Loos <willybas@gmail.com> wrote:
I don't get it, they should have been analyzed at restore, should they not?
I do not believe analyze is part of the restore process. You must explicitly run it.
Le 26/04/2011 18:38, Vick Khera a écrit : > On Tue, Apr 26, 2011 at 11:36 AM, Willy-Bas Loos <willybas@gmail.com> wrote: > >> I don't get it, they should have been analyzed at restore, should they not? >> > > I do not believe analyze is part of the restore process. You must > explicitly run it. > That's almost true. If autovacuum is on, it can start an ANALYZE on the big tables. Depends of course on your settings (autovacuum on, stats collector on, etc.) -- Guillaume http://www.postgresql.fr http://dalibo.com
On Tue, Apr 26, 2011 at 12:59 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
> I do not believe analyze is part of the restore process. You must
> explicitly run it.
>
That's almost true. If autovacuum is on, it can start an ANALYZE on the
big tables.
So that just means you need some luck of timing. Not really "part of the process" if you ask me.
Depends of course on your settings (autovacuum on, stats collector on, etc.)