Обсуждение: BUG #7519: incresed data base size and query performance lost

The following bug has been logged on the website:

Bug reference:      7519
Logged by:          Lokendra Dixit
Email address:      lokendra.dixit@rmsi.com
PostgreSQL version: 8.4.11
Operating system:   Windows 7
Description:        =


Hi,
    1.When I am taking bacup and restore two three times when databse size
automatically incresed please descrive the solution. =


2. I have created non cluster index on tables to increase query performance
at same time performance incresed but after one month on same query on same
database again query lost performance two three time what is reson behind
this.
any suggestion ?

With regards
Lokendra

<lokendra.dixit@rmsi.com> wrote:

> PostgreSQL version: 8.4.11

> 1.When I am taking bacup and restore two three times when databse
> size automatically incresed please descrive the solution.
>
> 2. I have created non cluster index on tables to increase query
> performance at same time performance incresed but after one month
> on same query on same database again query lost performance two
> three time what is reson behind this.

Everything you are describing sounds like the kind of bloat you get
if you don't have autovacuum configured aggressively enough.  To
give people a little more to work with, please describe you hardware
and show the results of running the query on this page:

http://wiki.postgresql.org/wiki/Server_Configuration

-Kevin

"Lokendra Dixit" <Lokendra.Dixit@rmsi.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