From: "Dave Dutcher" <dave@tridecap.com>
To: "'Mike'" <akiany@gmail.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: Vacuum and Memory Loss
Date: Sun, 22 Oct 2006 16:30:53 -0500
Message-ID: <005901c6f621$5a94e960$8300a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <1161307454.955144.107400@m73g2000cwd.googlegroups.com>
Importance: Normal

> Hello friends,
>=20
> I am responsible for maintaining a high volume website using=20
> postgresql
> 8.1.4. Given the amount of reads and writes, I vacuum full=20
> the server a
> few times a week around 1, 2 AM shutting down the site for a few
> minutes. The next day morning around 10 - 11 AM the server slows down
> to death. It used to be that the error 'Too many clients' would be
> recorded, until I increased the number of clients it can handle, and
> now it simply slows down to death having lots and lots of postmaster
> processes running:

If you are saying that running the vacuum full helps your performance, =
then
you want to make sure you are running plain vacuum and analyze =
frequently
enough.  If you have a database which has lots of update and delete
statements, and you do not run vacuum regularly enough, you can end up =
with
lots dead blocks slowing down database scans.  If you do lots of updates =
and
deletes you should shedule vacuum and analyze more often, or you might =
want
to look into running auto vacuum:

http://www.postgresql.org/docs/8.1/interactive/maintenance.html#AUTOVACUU=
M

If you aren't doing lots of updates and deletes, then maybe you just =
have a
busy database.  Lots of postmaster processes implies you have lots of
clients connecting to your database.  You can turn on =
stats_command_string
and then check the pg_stat_activity table to see what these connections =
are
doing.  If they are running queries, you can try to optimize them.  Try
turning on logging of long running queries with =
log_min_duration_statement.
Then use EXPLAIN ANALYZE to see why the query is slow and if anything =
can be
done to speed it up.