From: Simon Windsor [mailto:simon.windsor@xxxxxxxxxxxxxxx] Sent: Wednesday, December 14, 2011 3:02 PM To: pgsql-general@xxxxxxxxxxxxxx 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.windsor@xxxxxxxxxxxxxxxx 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
