Re: Vacuum and Memory Loss

> Hello friends,
>
> I am responsible for maintaining a high volume website using
> postgresql
> 8.1.4. Given the amount of reads and writes, I vacuum full
> 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#AUTOVACUUM

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.