Ever increasing pg_clog disk usage v8.4 - Mailing list pgsql-general
| From | Powrie, William |
|---|---|
| Subject | Ever increasing pg_clog disk usage v8.4 |
| Date | |
| Msg-id | F671E5C1EA02B3448D0B23B3414F5870EF03528C@MADARRMAILBOX02.indra.es Whole thread Raw |
| Responses |
Re: Ever increasing pg_clog disk usage v8.4
(Eduardo Morras <emorrasg@yahoo.es>)
Re: Ever increasing pg_clog disk usage v8.4 (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | 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.
pgsql-general by date: