Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: "'Andy Colson'" <andy@squeakycode.net>, <pgsql-performance@postgresql.org>
References: <004701cacdad$7013e9f0$503bbdd0$@com>
	<4BAE09AE.9020305@squeakycode.net>
In-Reply-To: <4BAE09AE.9020305@squeakycode.net>
Subject: Re: Database size growing over time and leads to performance impact
Date: Tue, 30 Mar 2010 16:47:42 +0530
Organization: Zoniac Software Private Limited
Message-ID: <011e01cacffa$a780f320$f682d960$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcrNsmg6UvF/2+RuSYGEZcNtRE+fPACRLgyw
Content-Language: en-us

We're using pgpool-II version 2.0.1 for PostgreSQL connection management.

pgpool configurations are:
num_init_children = 450
child_life_time = 300
connection_life_time = 120
child_max_connections = 30

As you recommended, I ran "ps -ax|grep postgres" at almost a busy
transaction time and I can find "idle" entries:
[root@newuser ~]# ps -ax|grep postgres
 2664 ?        Ss     0:00 postgres: newuser mydb 192.168.0.200(43545) idle
 2783 ?        Ss     0:00 postgres: newuser mydb 192.168.0.200(43585) idle
 2806 ?        Ss     0:02 postgres: newuser mydb 192.168.0.200(43588) idle
 2807 ?        Ss     0:01 postgres: newuser mydb 192.168.0.200(43589) idle
 2818 ?        Ss     0:00 postgres: newuser mydb 192.168.0.200(43601) idle
 2819 ?        Ss     0:00 postgres: newuser mydb 192.168.0.200(43602) idle
 2833 ?        Ss     0:02 postgres: newuser mydb 192.168.0.200(43603) idle
 2856 ?        Ss     0:03 postgres: newuser mydb 192.168.0.200(43614) idle

Based on pgpool documentation, and also as far as I know, even though
application layer returns/closes the application, pgpool will only handle
actual closing of connections based on the connection_life_time parameter
defined.  And if this timeout, it goes to "wait for connection request"
state.

Can you throw some light on this?  Is there any better way that we need to
re-configure our pgpool parameters?

-----Original Message-----
From: Andy Colson [mailto:andy@squeakycode.net] 
Sent: Saturday, March 27, 2010 7:06 PM
To: Gnanakumar; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Database size growing over time and leads to
performance impact

On 03/27/2010 08:00 AM, Gnanakumar wrote:
> 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?
>

The "issue" is not with PG's.  Any newer version of PG will act exactly the
same.  I don't think you understand.  Vacuum is not meant to reduce size of
the db, its meant to mark pages for reuse.  VACUUM FULL is almost never
needed.  The fact it didnt reduce your db size is probably because of
something else, like an open transaction.  If you have a transaction left
open, then your db will never be able to shrink or re-use pages.  You'd
better fix that issue first.  (run ps -ax|grep postgres  and look for "idle
in transaction")

You need to vacuum way more often than once a week.  Just VACUUM ANALYZE,
two, three times a day.  Or better yet, let autovacuum do its thing.  (if
you do have autovacuum enabled, then the only problem is the open
transaction thing).

Dont "VACUUM FULL", its not helping you, and is being removed in newer
versions.

-Andy