Hi Igor 2011/12/16 Igor Neyman <iney...@perceptron.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 <iney...@perceptron.com>: > From: Simon Windsor [mailto:simon.wind...@cornfield.me.uk] > Sent: Wednesday, December 14, 2011 3:02 PM > To: pgsql-general@postgresql.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.wind...@cornfield.org.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
