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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
