Обсуждение: 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
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