Re: Postgres performance slowly gets worse over a month

"Robert M. Meyer" <rmeyer(at)installs(dot)com> writes:
> Back on July 23, I posted on our performance problem. At that time, I
> got several suggestions about what to do to try to fix it. Well, it's
> happening again...

Given that you've increased the FSM size *and* are doing a full vacuum
every night (I hope with analyze option as well!), it seems unlikely
that your tables are physically growing --- I suspect there may be an
index growth problem instead. But you should try to check that.
Try doing

select relkind,sum(relpages) from pg_class group by relkind;

every so often (preferably just after a vacuum run) to see how the space
totals change over time.

> 1. shut down postgres
> 2. run 'postgres -O -P' to start a single user instantiation of the
> engine
> 3. type 'reindex' to get it to do it
> 4. exit postgres and restart the DB with pg_ctl

IIRC, that only rebuilds indexes on the system tables. You mentioned
that you'd rebuilt user indexes too --- how did you go about that
exactly?

> We also notice that while the load keeps increasing,
> the actual CPU time is very small. We'll see loads above 3 with each
> CPU (we have two) sitting with 80%+ idle time.

The load must be all disk I/O then. Do you have adequate RAM in this
thing? What have you set shared_buffers to?

More generally, what's your typical query mix? Have you checked for
inefficient plans on the most common queries?

regards, tom lane