Skip site navigation (1) Skip section navigation (2)

Re: Database size growing over time and leads to performance impact

From: "Tomeh, Husam" <HTomeh(at)facorelogic(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Database size growing over time and leads to performance impact
Date: 2010-03-27 13:47:53
Message-ID: 106c901cacdb4$1156ad15$1a1ca8c0@ana.firstamdata.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performance
You may want to consider performing more frequent vacuums a week or really considering leveraging autovacuum if it makes sense to your transactions volume.  

Regards,
      Husam 

-----Original Message-----
From: Gnanakumar <gnanam(at)zoniac(dot)com>
Sent: Saturday, March 27, 2010 6:06 AM
To: pgsql-admin(at)postgresql(dot)org <pgsql-admin(at)postgresql(dot)org>; pgsql-performance(at)postgresql(dot)org <pgsql-performance(at)postgresql(dot)org>
Subject: [ADMIN] Database size growing over time and leads to performance impact

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?

 

****************************************************************************************** 
This message may contain confidential or proprietary information intended only for the use of the 
addressee(s) named above or may contain information that is legally privileged. If you are 
not the intended addressee, or the person responsible for delivering it to the intended addressee, 
you are hereby notified that reading, disseminating, distributing or copying this message is strictly 
prohibited. If you have received this message by mistake, please immediately notify us by  
replying to the message and delete the original message and any copies immediately thereafter. 

Thank you. 
****************************************************************************************** 
FACLD


pgsql-performance by date

Next:From: Guillaume LelargeDate: 2010-03-27 14:41:23
Subject: Re: Database size growing over time and leads to performance impact
Previous:From: Andy ColsonDate: 2010-03-27 13:35:42
Subject: Re: Database size growing over time and leads to performance impact

pgsql-admin by date

Next:From: Guillaume LelargeDate: 2010-03-27 14:41:23
Subject: Re: Database size growing over time and leads to performance impact
Previous:From: Andy ColsonDate: 2010-03-27 13:35:42
Subject: Re: Database size growing over time and leads to performance impact

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group