Re: Vacuum and Large Objects

Stefan,

> -----Original Message-----
> From: Stefan Keller [mailto:sfkeller(at)gmail(dot)com]
> Sent: Friday, January 06, 2012 1:12 AM
> To: Igor Neyman
> Cc: Simon Windsor; pgsql-general(at)postgresql(dot)org
> Subject: Re: Vacuum and Large Objects
>
> Hi Igor
> 2011/12/16 Igor Neyman <ineyman(at)perceptron(dot)com> wrote: > But I think,
> your problem is right here:
> >
> > " running VACUUM FULL  pg_largeobject"
> >
> > If you are running "VACUUM FULL ..." on the table, you should follow
> it with the "REINDEX TABLE ...", at least on PG versions prior to 9.0.
>
> I'm pretty sure that VACUUM FULL builds new indexes. That's at least of
> how I understand the docs, especially the first "tip" here
> http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html
>
> Yours, Stefan
>
>
> 2011/12/16 Igor Neyman <ineyman(at)perceptron(dot)com>:
> > From: Simon Windsor [mailto:simon(dot)windsor(at)cornfield(dot)me(dot)uk]
> > Sent: Wednesday, December 14, 2011 3:02 PM
> > To: pgsql-general(at)postgresql(dot)org
> > Subject: Vacuum and Large Objects
> >
> > Hi
> >
> > I am having problems recovering storage from a Postgres 9.05 database
> that is used to hold large XML blocks for a week, before they are
> archived off line.
> >
> > The main tables are partitioned in daily partitions, and these are
> easy to manage, however the DB keeps growing despite using Vacuum
> (daily at 0700) and autovacuum (this does not seem to run, although the
> process is running). The system is insert only, and partitions are
> dropped when over 7 days of age.
> >
> > I believe the issue lies with pg_largeobject, it is split between 88
> files of approx. 1G each.
> >
> > The Postgres settings are default, EXCEPT
> >
> > grep ^[a-z] postgresql.conf
> > listen_addresses = '*'          # what IP address(es) to listen on;
> > port = 5432                             # (change requires restart)
> > max_connections = 1000                  # (change requires restart)
> > shared_buffers = 256MB                  # min 128kB work_mem =
> 4MB
> > # min 64kB maintenance_work_mem = 256MB            # min 1MB
> > vacuum_cost_delay = 20ms                # 0-100 milliseconds
> > checkpoint_segments = 32                # in logfile segments, min 1,
> > 16MB each checkpoint_completion_target = 0.9      # checkpoint target
> > duration, 0.0 - 1.0 checkpoint_warning = 60s                # 0
> > disables archive_mode = off              # allows archiving to be
> done
> > constraint_exclusion = partition        # on, off, or partition
> > log_destination = 'stderr'              # Valid values are
> > combinations of logging_collector = on          # Enable capturing of
> > stderr and csvlog silent_mode = on                        # Run
> server silently.
> > log_checkpoints = on
> > log_line_prefix = '%t %d %u '                   # special values:
> > log_statement = 'none'                  # none, ddl, mod, all
> > track_activities = on track_counts = on autovacuum =
> on
> > # Enable autovacuum subprocess?  'on'
> > log_autovacuum_min_duration = 250       # -1 disables, 0 logs all
> > actions and autovacuum_max_workers = 3              # max number of
> > autovacuum subprocesses autovacuum_naptime = 3min               #
> time
> > between autovacuum runs autovacuum_vacuum_threshold = 500       # min
> > number of row updates before autovacuum_analyze_threshold = 100
> #
> > min number of row updates before autovacuum_vacuum_scale_factor =
> 0.1
> > # fraction of table size before vacuum
> autovacuum_analyze_scale_factor
> > = 0.05  # fraction of table size before analyze
> > autovacuum_vacuum_cost_delay = 5ms      # default vacuum cost delay
> > for autovacuum_vacuum_cost_limit = 200      # default vacuum cost
> > limit for statement_timeout = 0                   # in milliseconds,
> 0 is disabled datestyle = 'iso, dmy'
> > lc_messages = 'en_GB.UTF-8'                     # locale for system
> > error message lc_monetary = 'en_GB.UTF-8'                     #
> locale
> > for monetary formatting lc_numeric = 'en_GB.UTF-
> 8'
> > # locale for number formatting lc_time = 'en_GB.UTF-
> 8'
> > # locale for time formatting default_text_search_config =
> 'pg_catalog.english'
> >
> > Besides running VACUUM FULL  pg_largeobject;, is there a way I can
> get autovacuum to start and clear this up?
> >
> > All the best
> >
> > Simon
> >
> > Simon Windsor
> > Eml: simon(dot)windsor(at)cornfield(dot)org(dot)uk
> > Tel: 01454 617689
> > Mob: 07590 324560
> >
> > "There is nothing in the world that some man cannot make a little
> worse and sell a little cheaper, and he who considers price only is
> that man's lawful prey."
> >
> >>
> >>
> >
> > I might be a bit late in this discussion.
> > But I think, your problem is right here:
> >
> > " running VACUUM FULL  pg_largeobject"
> >
> > If you are running "VACUUM FULL ..." on the table, you should follow
> it with the "REINDEX TABLE ...", at least on PG versions prior to 9.0.
> >
> > Regards,
> > Igor Neyman
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To
> > make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general

I qualified my reply with PG version specifics, since original poster didn't specify Postgres version he's using.

Regards,
Igor Neyman