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 after they'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 information must be available for new transactions.
I have tried modifying the following vacuum parameters
vacuum_freeze_table_age
vacuum_freeze_min_agewith 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 problemDoes 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 app to 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>--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 after they'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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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 pagesDETAIL: 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general