database is growing... 1GB per day basis
Hello, my name is raghu. I work for Avaya. We uses postgres as database
in our application. Our application is a jboss based enterprise
application, that does some updates operations based on requests from
client. We use entities and hibernate for data access within our
application.
I have couple of questions, as listed:
1. We observe that database is growing very fast - many sets of
files with size more than 1 GB. Please see some sample snippets of the
file lists:
This does not happen regular basis. However, we have a few instances of
this incidence, and pretty much blocked on this. What could be causing
this? What can be done to diagnose/ resolve this issue? We are running
out of disk space in our box quite quickly due to this issue. Any
thoughts please?
2. One of our table has columns for LOB. All other tables are of
primary types (there are a few sting/ varchar types as well). I
understand that postgres maintains large data objects in pg_largeobject.
Is there a way to find the data within those table? How can I find out
the corresponding table information or row corresponding to this large
data maintained/ managed in pg_largeobject? I also hear about
pg_toast... wot is the relation between pg_toast and pg_largeobject?
I would appreciate your early response and help very much!
thanks/ regards,
raghu
On 01/11/2011 02:03 PM, Karayappalli, Raghunath (Raghu) wrote:
1.We observe that database is growing very fast � many sets of files
with size more than 1 GB. Please see some sample snippets of the file
lists:
What version of Pg are you using?
Got lots of connections in 'IDLE IN TRANSACTION' state as listed in
"SELECT * FROM pg_stat_activity;" ?
Is autovacuum enabled? Have you done anything like tuned autovacuum to
hardly ever run?
What're the results of running 'VACUUM ANALYZE VERBOSE;' in the problem
database?
--
Craig Ringer
I am using the version 8.2.
I have enabled auto vacuum (assuming that you are referring to the
"autovacuum = on" configuration).
thanks/ regards,
raghu
-----Original Message-----
From: Craig Ringer [mailto:craig@postnewspapers.com.au]
Sent: Tuesday, January 11, 2011 2:08 AM
To: Karayappalli, Raghunath (Raghu)
Cc: pgsql-questions@postgresql.org
Subject: Re: [GENERAL] database is growing... 1GB per day basis
On 01/11/2011 02:03 PM, Karayappalli, Raghunath (Raghu) wrote:
1.We observe that database is growing very fast - many sets of files
with size more than 1 GB. Please see some sample snippets of the file
lists:
What version of Pg are you using?
Got lots of connections in 'IDLE IN TRANSACTION' state as listed in
"SELECT * FROM pg_stat_activity;" ?
Is autovacuum enabled? Have you done anything like tuned autovacuum to
hardly ever run?
What're the results of running 'VACUUM ANALYZE VERBOSE;' in the problem
database?
--
Craig Ringer
I am missing some of your questions: what are the tuning parameters are
you referring here that would prevent running auto vacuum?
thanks/ regards,
raghu
-----Original Message-----
From: Karayappalli, Raghunath (Raghu)
Sent: Tuesday, January 11, 2011 9:38 AM
To: 'Craig Ringer'
Cc: 'pgsql-questions@postgresql.org'
Subject: RE: [GENERAL] database is growing... 1GB per day basis
I am using the version 8.2.
I have enabled auto vacuum (assuming that you are referring to the
"autovacuum = on" configuration).
thanks/ regards,
raghu
-----Original Message-----
From: Craig Ringer [mailto:craig@postnewspapers.com.au]
Sent: Tuesday, January 11, 2011 2:08 AM
To: Karayappalli, Raghunath (Raghu)
Cc: pgsql-questions@postgresql.org
Subject: Re: [GENERAL] database is growing... 1GB per day basis
On 01/11/2011 02:03 PM, Karayappalli, Raghunath (Raghu) wrote:
1.We observe that database is growing very fast - many sets of files
with size more than 1 GB. Please see some sample snippets of the file
lists:
What version of Pg are you using?
Got lots of connections in 'IDLE IN TRANSACTION' state as listed in
"SELECT * FROM pg_stat_activity;" ?
Is autovacuum enabled? Have you done anything like tuned autovacuum to
hardly ever run?
What're the results of running 'VACUUM ANALYZE VERBOSE;' in the problem
database?
--
Craig Ringer
Import Notes
Reply to msg id not found: D2BE89E426C49F43A3BCE545BD991F0302F2D900@306181ANEX4.global.avaya.com
Dne 11.1.2011 19:11, Karayappalli, Raghunath (Raghu) napsal(a):
I am missing some of your questions: what are the tuning parameters are
you referring here that would prevent running auto vacuum?
There is a bunch of parameters influencing autovacuum - how often it
runs, how much work it does, etc. Especially those beginning with
'vacuum' and 'autovacuum' - what values are set for those parameters?
Tomas
On 01/12/2011 01:37 AM, Karayappalli, Raghunath (Raghu) wrote:
I am using the version 8.2.
I have enabled auto vacuum (assuming that you are referring to the
"autovacuum = on" configuration).
Autovacuum improves significantly with every Pg release. 8.3 and 8.4
both saw quite big improvements. In 8.2, autovacuum required more tuning
to get it to work well and was less good at handling some things.
I won't be at all surprised if your database file growth is table bloat.
PostgreSQL provides very poor built-in facilities for identifying bloat,
so it's necessary to use nontrivial queries to track it down in
human-friendly form.
http://gkoenig.wordpress.com/2009/11/24/postgres-and-bloated-table/
http://wiki.postgresql.org/wiki/Show_database_bloat
http://wiki.postgresql.org/wiki/VACUUM_FULL
As for why this bloat is ocurring - it could be autovacuum not being
aggressive enough, or it could be issues with long-running transactions.
--
Craig Ringer