Обсуждение: Windows Auto-Vacuum in 8.2.4 or 8.2.5
A while back it was pointed out the that the Windows version of 8.2.3 had a bug that prevented auto-vacuum from working correctly. http://archives.postgresql.org/pgsql-general/2007-04/msg00139.php I wasn't able to determine from the release notes if this bug was fixed in versions 8.2.4 or 8.2.5. Does any know if it was? Regards, Richard Broersma Jr.
I thought I would give this question a second try. --- Richard Broersma Jr <rabroersma@yahoo.com> wrote: > A while back it was pointed out the that the Windows version of 8.2.3 had a bug that prevented > auto-vacuum from working correctly. > > http://archives.postgresql.org/pgsql-general/2007-04/msg00139.php > > I wasn't able to determine from the release notes if this bug was fixed in versions 8.2.4 or > 8.2.5. Does any know if it was? > > Regards, > Richard Broersma Jr.
Richard Broersma Jr wrote: > A while back it was pointed out the that the Windows version of 8.2.3 > had a bug that prevented auto-vacuum from working correctly. > > http://archives.postgresql.org/pgsql-general/2007-04/msg00139.php > > I wasn't able to determine from the release notes if this bug was > fixed in versions 8.2.4 or 8.2.5. Does any know if it was? Hmm, maybe it was, but then I cannot recall what bug I was referring to :-( I can't find a relevant CVS log message either. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J Licensee shall have no right to use the Licensed Software for productive or commercial use. (Licencia de StarOffice 6.0 beta)
--- Alvaro Herrera <alvherre@commandprompt.com> wrote: > Hmm, maybe it was, but then I cannot recall what bug I was referring to > :-( I can't find a relevant CVS log message either. Okay. FWIW, I am still seeing the same behavior in Version 8.2.4. But I am about to upgrade to 8.2.5, and I will check to see if I notice a difference. Ofcourse, maybe auto-vacuum does work but I have a configuration error some where. Thanks for the consideration! Regards, Richard Broersma Jr.
Richard Broersma Jr wrote: > --- Alvaro Herrera <alvherre@commandprompt.com> wrote: > > > Hmm, maybe it was, but then I cannot recall what bug I was referring to > > :-( I can't find a relevant CVS log message either. > > Okay. FWIW, I am still seeing the same behavior in Version 8.2.4. But > I am about to upgrade to 8.2.5, and I will check to see if I notice a > difference. > > Ofcourse, maybe auto-vacuum does work but I have a configuration error some where. Maybe you should restate your problem so we can try to discover the cause. -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ "Digital and video cameras have this adjustment and film cameras don't for the same reason dogs and cats lick themselves: because they can." (Ken Rockwell)
--- Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Maybe you should restate your problem so we can try to discover the
> cause.
Okay.
I will dis-able the hourly manual vacuum/analyze script that I implemented as a work-around to
this problem to see if auto-vacuum is ever triggered.
But to start off with, here are the settings that I currently have in my postgresql.conf for
auto-vacuum. I will post-back with any results whether I see auto-vacuum working in 8.2.5 or not.
Thanks!
Regards,
Richard Broersma Jr.
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Query/Index Statistics Collector -
stats_start_collector = on # needed for block or row stats
# (change requires restart)
stats_row_level = on
#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------
autovacuum = on # enable autovacuum subprocess?
# 'on' requires stats_start_collector
# and stats_row_level to also be on
autovacuum_naptime = 1min # time between autovacuum runs
autovacuum_vacuum_threshold = 100 # min # of tuple updates before
# vacuum
autovacuum_analyze_threshold = 25 # min # of tuple updates before
# analyze
autovacuum_vacuum_scale_factor = 0.002 # fraction of rel size before
# vacuum
autovacuum_analyze_scale_factor = 0.001 # fraction of rel size before
# analyze
autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
--- Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> I will dis-able the hourly manual vacuum/analyze script that I implemented as a work-around to
> this problem to see if auto-vacuum is ever triggered.
it appears to be working fine in 8.2.5:
proj02u20411=> begin transaction;
BEGIN
proj02u20411=> update document set docdisc = docdisc;
UPDATE 7927
proj02u20411=> update document set docnum = docnum;
UPDATE 7927
proj02u20411=> rollback;
ROLLBACK
proj02u20411=> explain analyze select count(*) from docs.document;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Aggregate (cost=641.58..641.59 rows=1 width=0) (actual time=25.340..25.341 rows=1 loops=1)
-> Seq Scan on document (cost=0.00..587.26 rows=21726 width=0) (actual time=0.025..14.578
rows=7927 loops=1)
Total runtime: 25.508 ms
(3 rows)
proj02u20411=> \x
Expanded display is on.
proj02u20411=> SELECT *
proj02u20411-> FROM Pg_stat_all_tables
proj02u20411-> WHERE schemaname = 'docs'
proj02u20411-> AND relname = 'document'
proj02u20411-> ORDER BY schemaname, relname;
-[ RECORD 1 ]----+---------------------------
relid | 16867
schemaname | docs
relname | document
seq_scan | 4769
seq_tup_read | 34099275
idx_scan | 452899
idx_tup_fetch | 1679315
n_tup_ins | 59
n_tup_upd | 15868
n_tup_del | 52
last_vacuum | 2007-09-21 07:00:03.646-07
last_autovacuum | 2007-09-21 07:57:49.496-07
last_analyze | 2007-09-21 07:00:03.646-07
last_autoanalyze | 2007-09-21 07:57:49.496-07
proj02u20411=> \x
Expanded display is off.
proj02u20411=> explain analyze select count(*) from docs.document;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Aggregate (cost=234.09..234.10 rows=1 width=0) (actual time=22.014..22.016 rows=1 loops=1)
-> Seq Scan on document (cost=0.00..214.27 rows=7927 width=0) (actual time=0.025..11.490
rows=7927 loops=1)
Total runtime: 22.140 ms
(3 rows)
proj02u20411=>