From: Gnanakumar <gnanam@zoniac.com>
To: pgsql-admin@postgresql.org
To: pgsql-performance@postgresql.org
Subject: Database size growing over time and leads to performance impact
Date: Sat, 27 Mar 2010 18:30:09 +0530
Message-ID: <004701cacdad$7013e9f0$503bbdd0$@com> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gnanam@zoniac.com, pgsql-admin@postgresql.org
Subject: Re: Database size growing over time and leads to performance impact
In-Reply-To: <004701cacdad$7013e9f0$503bbdd0$@com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox