Ever increasing pg_clog disk usage v8.4

From: "Powrie, William" <wpowrie(at)indracompany(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Ever increasing pg_clog disk usage v8.4
Date: 2014-12-01 00:34:14
Message-ID: F671E5C1EA02B3448D0B23B3414F5870EF03528C@MADARRMAILBOX02.indra.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

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

Regards,

William Powrie

________________________________
Este correo electr?nico y, en su caso, cualquier fichero anexo al mismo, contiene informaci?n de car?cter confidencial exclusivamente dirigida a su destinatario o destinatarios. Si no es vd. el destinatario indicado, queda notificado que la lectura, utilizaci?n, divulgaci?n y/o copia sin autorizaci?n est? prohibida en virtud de la legislaci?n vigente. En el caso de haber recibido este correo electr?nico por error, se ruega notificar inmediatamente esta circunstancia mediante reenv?o a la direcci?n electr?nica del remitente.
Evite imprimir este mensaje si no es estrictamente necesario.

This email and any file attached to it (when applicable) contain(s) confidential information that is exclusively addressed to its recipient(s). If you are not the indicated recipient, you are informed that reading, using, disseminating and/or copying it without authorisation is forbidden in accordance with the legislation in effect. If you have received this email by mistake, please immediately notify the sender of the situation by resending it to their email address.
Avoid printing this message if it is not absolutely necessary.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Paul GOERGLER 2014-12-01 08:48:09 Is "WITH () UPDATE" Thread Safe ?
Previous Message Tom Lane 2014-11-30 22:41:14 Re: JSON_AGG produces extra square brakets