On Mon, 23 Feb 2009, Scott Marlowe wrote:
> well that's pretty normal as the indexes grow large enough to not fit in
> cache, then not fit in memory, etc...
Right, the useful thing to do in this case is to take a look at how big
all the relations (tables, indexes) involved are at each of the steps in
the process. The script at http://wiki.postgresql.org/wiki/Disk_Usage
will show you that. That will give some feedback on whether the
vacuum/reindex methodology is really doing what you expect, and it will
also let you compare the size of the table/index with how much RAM is in
the system.
Have you done any tuning of the postgresql.conf file? If you haven't
increased shared_buffers substantially, you could be seeing buffer cache
churn as the CPU spends all its time shuffling buffers between PostgreSQL
and the OS once the working set involved exceeds around 32MB.
Shouldn't someone have ranted about RAID-5 by this point in the thread?
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD