Re: Ever increasing pg_clog disk usage v8.4

On 11/30/2014 04:34 PM, Powrie, William wrote:
> Hello,
>
> I have a simple database with just a few tables that runs on an embedded
> Linux system 2.6.31.8. The OS including postresql 8.4 is loaded directly
> from cf-flash media and is not saved in any way across power recycles.
> It is always created at startup from the /inittab/rc script and nearly
> all the elements are inserted at this point. The database job is to
> service remote access requests via lighttpd from a web browser and
> provide an up to date account of the monitored equipment. The database
> is used to store hardware parameter values that are frequency polled via
> a number of Linux daemons.
>
> In normal operation there are no inserts but frequent updates and reads.
> Reliability is of upmost importance since each system is unmanaged but
> is remotely monitored. There are a number of systems currently deployed
> worldwide.
>
> Postgresql itself runs from a 56Meg ramdisk so disk space is limited.
> This is where the problem is.
>
> The files in pg_clog increase on a day to day basis until the ramdisk
> reaches 100% utilization. This takes roughly 30 days to occur and
> postgresql fails at this point.
>
> The software runs vacuumdb from cron every day at a predefined time but
> this does not appear to do anything. I have increased it to run more
> frequently but this does not have the desired effect. Performing a full
> vacuum is not possible since I cannot easily get database exclusive
> access for which a full vacuum appears to need.
>
> I have tried modifying the following vacuum parameters
>
> vacuum_freeze_table_age
>
> vacuum_freeze_min_age
>
> with no effect.
>
> If I run a vacuumdb analyse in verbose mode, this is an extract of what
> I see:
>
> INFO: vacuuming "public.mib_snmpinteger"
>
> INFO: index "mib_snmpinteger_element_id_key" now contains 2880 row
> versions in 23 pages
>
> DETAIL: 0 index row versions were removed.
>
> 0 index pages have been deleted, 0 are currently reusable.
>
> I never see any index pages being returned to the operating system which
> is the problem

I thought the pg_clog directory is the problem?

In any event you might want to take at the functions below to get an
idea of the space being taken by your tables/indexes:

http://www.postgresql.org/docs/9.3/interactive/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT

>
> Does anyone know how I can reclaim the every growing ramdisk space?

Might want to take a look at this:

http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html

"The sole disadvantage of increasing autovacuum_freeze_max_age (and
vacuum_freeze_table_age along with it) is that the pg_clog subdirectory
of the database cluster will take more space, because it must store the
commit status of all transactions back to the autovacuum_freeze_max_age
horizon. The commit status uses two bits per transaction, so if
autovacuum_freeze_max_age is set to its maximum allowed value of two
billion, pg_clog can be expected to grow to about half a gigabyte. If
this is trivial compared to your total database size, setting
autovacuum_freeze_max_age to its maximum allowed value is recommended.
Otherwise, set it depending on what you are willing to allow for pg_clog
storage. (The default, 200 million transactions, translates to about
50MB of pg_clog storage.)"

>
> Regards,
>
> William Powrie
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com