From: Guillaume Lelarge <guillaume@lelarge.info>
To: gnanam@zoniac.com
Cc: pgsql-admin@postgresql.org
Subject: Re: Database size growing over time and leads to performance impact
Date: Sat, 27 Mar 2010 15:41:23 +0100
Message-ID: <4BAE1913.3090905@lelarge.info> (raw)
In-Reply-To: <004701cacdad$7013e9f0$503bbdd0$@com>
References: <004701cacdad$7013e9f0$503bbdd0$@com>
Le 27/03/2010 14:00, Gnanakumar a écrit :
> [...]
> 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
>
Which means you can be sure you have bloated indexes.
> 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.
>
VACUUM doesn't compact a database. VACUUM FULL does for tables. REINDEX
does for index.
And this is why, I think, you have an issue. You do VACUUM FULL each
week, but don't do a REINDEX.
> 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.
>
Not surprising, indexes are recreated.
> 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?
>
If you still want to use VACUUM FULL, then you need to use REINDEX. But
you shouldn't need VACUUM FULL. Configure autovacuum so that your tables
don't get bloated.
> 2. How in real time, this issues are handled by other PostgreSQL users
> without taking to downtime?
>
Using the autovacuum to VACUUM and ANALYZE when it's really needed.
> 3. Any ideas or links whether this is addressed in upcoming PostgreSQL
> version 9.0 release?
>
--
Guillaume.
http://www.postgresqlfr.orghttp://dalibo.com
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: guillaume@lelarge.info, gnanam@zoniac.com, pgsql-admin@postgresql.org
Subject: Re: Database size growing over time and leads to performance impact
In-Reply-To: <4BAE1913.3090905@lelarge.info>
* 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