Re: Ever increasing pg_clog disk usage v8.4

On Mon, 1 Dec 2014 01:34:14 +0100
"Powrie, William" <wpowrie(at)indracompany(dot)com> 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.

Is PostgreSQL 8.4 updated to last version? I think it was 8.4.22.

pg_clog stores information about current commit status for open/active transactions. Do you close all transactions after they're work is done? Do you have any in "IDLE in transaction" state?

> 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.

Vacuum won't work because pg_clog stores data for open/active transactions, as they are alive (have a XID) that information must be available for new transactions.

> 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
>
> Does anyone know how I can reclaim the every growing ramdisk space?

It may be a bug corrected in newer 8.4 versions, update.

A fast hack will be close the application connected to PostgreSQL, it will close the transactions. Better, rewrite your app to open/close transactions, using "BEGIN/END".

> Regards,

As final note, why don't use Sqlite3 for embedded instead PostgreSQL?

>
> William Powrie

--- ---
Eduardo Morras <emorrasg(at)yahoo(dot)es>