Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 41F09632A29; Sat, 27 Mar 2010 10:05:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 74114-01; Sat, 27 Mar 2010 13:05:42 +0000 (UTC) X-Greylist: delayed 00:05:21.889646 by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88]) by mail.postgresql.org (Postfix) with ESMTP id 8653A6333A5; Sat, 27 Mar 2010 10:05:42 -0300 (ADT) Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in [122.165.58.186] (may be forged)) (authenticated bits=0) by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o2RD0Dia024314; Sat, 27 Mar 2010 08:00:15 -0500 Reply-To: From: "Gnanakumar" To: , Subject: Database size growing over time and leads to performance impact Date: Sat, 27 Mar 2010 18:30:09 +0530 Organization: Zoniac Software Private Limited Message-ID: <004701cacdad$7013e9f0$503bbdd0$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0048_01CACDDB.89CC25F0" X-Mailer: Microsoft Office Outlook 12.0 thread-index: AcrNrV2lxwrWTqp4Se+uazWUBBQ1iQ== Content-Language: en-us x-cr-hashedpuzzle: CNc6 DAaC ETVP JLA9 JniL J6oE J7cy LBMj Nxfq OAHE R2sG SMSU SiGf TQv5 ThpI Tr18; 2; cABnAHMAcQBsAC0AYQBkAG0AaQBuAEAAcABvAHMAdABnAHIAZQBzAHEAbAAuAG8AcgBnADsAcABnAHMAcQBsAC0AcABlAHIAZgBvAHIAbQBhAG4AYwBlAEAAcABvAHMAdABnAHIAZQBzAHEAbAAuAG8AcgBnAA==; Sosha1_v1; 7; {35096AB8-4D95-4C2A-87F9-EDB612383EB8}; ZwBuAGEAbgBhAG0AQAB6AG8AbgBpAGEAYwAuAGMAbwBtAA==; Sat, 27 Mar 2010 12:59:41 GMT; RABhAHQAYQBiAGEAcwBlACAAcwBpAHoAZQAgAGcAcgBvAHcAaQBuAGcAIABvAHYAZQByACAAdABpAG0AZQAgAGEAbgBkACAAbABlAGEAZABzACAAdABvACAAcABlAHIAZgBvAHIAbQBhAG4AYwBlACAAaQBtAHAAYQBjAHQA x-cr-puzzleid: {35096AB8-4D95-4C2A-87F9-EDB612383EB8} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/206 X-Sequence-Number: 34005 This is a multipart message in MIME format. ------=_NextPart_000_0048_01CACDDB.89CC25F0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi, We're using PostgreSQL 8.2. Recently, in our production database, there was a severe performance impact.. Even though, we're regularly doing both: 1. VACUUM FULL ANALYZE once in a week during low-usage time and 2. ANALYZE everyday at low-usage time Also, we noticed that the physical database size has grown upto 30 GB. But, if I dump the database in the form of SQL and import it locally in my machine, it was only 3.2 GB. Then while searching in Google to optimize database size, I found the following useful link: http://www.linuxinsight.com/optimize_postgresql_database_size.html It says that even vacuumdb or reindexdb doesn't really compact database size, only dump/restore does because of MVCC architecture feature in PostgreSQL and this has been proven here. So, finally we decided to took our production database offline and performed dump/restore. After this, the physical database size has also reduced from 30 GB to 3.5 GB and the performance was also very good than it was before. Physical database size was found using the following command: du -sh /usr/local/pgsql/data/base/ I also cross-checked this size using "pg_size_pretty(pg_database_size(datname))". Questions 1. Is there any version/update of PostgreSQL addressing this issue? 2. How in real time, this issues are handled by other PostgreSQL users without taking to downtime? 3. Any ideas or links whether this is addressed in upcoming PostgreSQL version 9.0 release? ------=_NextPart_000_0048_01CACDDB.89CC25F0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

We're using PostgreSQL 8.2.  Recently, in our production database, there was a severe performance impact..  Even = though, we're regularly doing both:

1.     VACUUM FULL ANALYZE once in a week during = low-usage time and

2.     ANALYZE everyday at low-usage = time

 

Also, we noticed that the physical database size = has grown upto 30 GB.  But, if I dump the database in the form of SQL and = import it locally in my machine, it was only 3.2 GB.  Then while searching in = Google to optimize database size, I found the following useful = link:

 

http://www.linuxinsight.com/optimize_postgresql_database_size.html=

 

It says that even vacuumdb or reindexdb doesn't = really compact database size, only dump/restore does because of MVCC = architecture feature in PostgreSQL and this has been proven here.

 

So, finally we decided to took our production = database offline and performed dump/restore.  After this, the physical = database size has also reduced from  30 GB to 3.5 GB and the performance was = also very good than it was before.

 

Physical database size was found using the = following command:

du -sh = /usr/local/pgsql/data/base/<database-oid>

 

I also cross-checked this size using "pg_size_pretty(pg_database_size(datname))".

 

Questions

1.     Is there any version/update of PostgreSQL = addressing this issue?

2.     How in real time, this issues are handled by = other PostgreSQL users without taking to downtime?

3.     Any ideas or links whether this is addressed in upcoming PostgreSQL version 9.0 release?

 

------=_NextPart_000_0048_01CACDDB.89CC25F0--