Re: Defining performance.

On Thu, 2006-11-30 at 18:26, Tom Lane wrote:
> Paul Lathrop <plathrop(at)squaretrade(dot)com> writes:
> > ... When I joined the company last year, the databases were
> > deployed on 12-disk RAID5 arrays on dual-proc AMD machines with 4Gb of
> > RAM, running Debian Woody and Postgres 7.2. These systems seemed to
> > suffer a gradually decreasing performance accompanied by a gradually
> > growing disk space usage. The DBA had come to the conclusion that the
> > VACUUM command did/does not work on these systems, because even after a
> > VACUUM FULL, the size of the database was continually increasing.
>
> The very first thing you need to do is get off 7.2.
>
> After that, I'd recommend looking at *not* using VACUUM FULL. FULL is
> actually counterproductive in a lot of scenarios, because it shrinks the
> tables at the price of bloating the indexes. And 7.2's poor ability to
> reuse index space turns that into a double whammy. Have you checked
> into the relative sizes of tables and indexes and tracked the trend over
> time?

And if you cant get off 7.2, look into scheduling some downtime to run
reindex on the bloated indexes.

In all honesty, a simple single processor workstation with a gig of ram
and a couple of good sized SATA drives and a modern linux distro can
probably outrun your 7.2 server if it's running on 8.1 / 8.2

It's that much faster now.

For the love of all that's holy, as well as your data, start planning
your migration now, and if you can, have it done by the end of next week
or so.

And backup every night religiously.