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