Thread: Ever increasing pg_clog disk usage v8.4
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.
On Mon, 1 Dec 2014 01:34:14 +0100 "Powrie, William" <wpowrie@indracompany.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 afterthey'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 informationmust 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 appto open/close transactions, using "BEGIN/END". > Regards, As final note, why don't use Sqlite3 for embedded instead PostgreSQL? > > William Powrie --- --- Eduardo Morras <emorrasg@yahoo.es>
Eduardo Morras <emorrasg@yahoo.es> writes:
> "Powrie, William" <wpowrie@indracompany.com> wrote:
>> 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.
> pg_clog stores information about current commit status for open/active transactions. Do you close all transactions
afterthey're work is done? Do you have any in "IDLE in transaction" state?
Leaving prepared transactions sitting (without committing them or
rolling them back) could do this as well, I believe.
8.4 seems to default to max_prepared_transactions = 0 so it would
take some extra work to shoot yourself in the foot that way, but
it's definitely possible.
regards, tom lane
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.klaver@aklaver.com