pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed[GENERAL]auto vacuum during restore
4+ messages / 3 participants
[nested] [flat]
* [GENERAL]auto vacuum during restore
@ 2011-04-26 15:36 Willy-Bas Loos <willybas@gmail.com>
2011-04-26 16:38 ` Re: [GENERAL]auto vacuum during restore Vick Khera <vivek@khera.org>
0 siblings, 1 reply; 4+ messages in thread
From: Willy-Bas Loos @ 2011-04-26 15:36 UTC (permalink / raw)
To: pgsql-admin
Hi,
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL]auto vacuum during restore
2011-04-26 15:36 [GENERAL]auto vacuum during restore Willy-Bas Loos <willybas@gmail.com>
@ 2011-04-26 16:38 ` Vick Khera <vivek@khera.org>
2011-04-26 16:59 ` Re: [GENERAL]auto vacuum during restore Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 4+ messages in thread
From: Vick Khera @ 2011-04-26 16:38 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL]auto vacuum during restore
2011-04-26 15:36 [GENERAL]auto vacuum during restore Willy-Bas Loos <willybas@gmail.com>
2011-04-26 16:38 ` Re: [GENERAL]auto vacuum during restore Vick Khera <vivek@khera.org>
@ 2011-04-26 16:59 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-04-26 17:07 ` Re: [GENERAL]auto vacuum during restore Vick Khera <vivek@khera.org>
0 siblings, 1 reply; 4+ messages in thread
From: Guillaume Lelarge @ 2011-04-26 16:59 UTC (permalink / raw)
To: Vick Khera <vivek@khera.org>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL]auto vacuum during restore
2011-04-26 15:36 [GENERAL]auto vacuum during restore Willy-Bas Loos <willybas@gmail.com>
2011-04-26 16:38 ` Re: [GENERAL]auto vacuum during restore Vick Khera <vivek@khera.org>
2011-04-26 16:59 ` Re: [GENERAL]auto vacuum during restore Guillaume Lelarge <guillaume@lelarge.info>
@ 2011-04-26 17:07 ` Vick Khera <vivek@khera.org>
0 siblings, 0 replies; 4+ messages in thread
From: Vick Khera @ 2011-04-26 17:07 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: pgsql-admin
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.)
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-04-26 17:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-26 15:36 [GENERAL]auto vacuum during restore Willy-Bas Loos <willybas@gmail.com>
2011-04-26 16:38 ` Vick Khera <vivek@khera.org>
2011-04-26 16:59 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-04-26 17:07 ` Vick Khera <vivek@khera.org>
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