Re: BUG #7519: incresed data base size and query performance lost

"Lokendra Dixit" <Lokendra(dot)Dixit(at)rmsi(dot)com> wrote:

> RAM: 2 GB

You do realize how small that is for a database server, I hope.
Many people are walking around with cell phones in their pockets
that have a lot more. This could contribute to severe slowdown with
even minimal growth of the database, as cached access will suddenly
become disk access, which is orders of magnitude slower.

> (Embedded image moved to file: pic00041.jpg)

It's much better to include such information in text form than to
use a screen image.

Anyway, according to that you didn't change autovacuum values from
the defaults. You might want to make autovacuum a bit more
aggressive to try to keep table sizes down; but I think the root of
the problem is that a pg_dump and restore will normally pack the
data very tightly on disk. In normal operations thing become less
tight as index pages split, etc., and you are probably going from a
very high cache hit ratio to a lower one, causing the slowdown.

For about $35 you could probably double or triple the amount of RAM
you have available and totally eliminate the problem. You have
probably spent a lot more money on staff time to deal with the
problem than the RAM will cost.

-Kevin