Re: Ever increasing pg_clog disk usage v8.4 - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Ever increasing pg_clog disk usage v8.4 |
Date | |
Msg-id | 547C8042.1030006@aklaver.com Whole thread Raw |
In response to | Ever increasing pg_clog disk usage v8.4 ("Powrie, William" <wpowrie@indracompany.com>) |
List | pgsql-general |
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
pgsql-general by date: