Skip site navigation (1) Skip section navigation (2)

Re: Why so slow?

From: K C Lau <kclau60(at)netvigator(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why so slow?
Date: 2006-04-29 01:46:06
Message-ID: 6.2.1.2.0.20060429084119.05290438@localhost (view raw or flat)
Thread:
Lists: pgsql-performance
At 03:00 06/04/29, Bruno Wolff III wrote:
>On Fri, Apr 28, 2006 at 17:37:30 +0000,
>   Bealach-na Bo <bealach_na_bo(at)hotmail(dot)com> wrote:
> > >The above shows that the indexes contained 10M rows and 160M of dead
> > >space each. That means you weren't vacuuming nearly enough.
> >
> > How is it that a row in the table can grow to a size far exceeding the sum
> > of the maximum sized of the fields it consists of?
>
>Because unless you run vacuum, the old deleted rows are not reused. Those
>rows cannot be deleted immediately, because the rows may be visible to
>other transactions. Periodic vacuums are used to find deleted rows which
>are no longer visible to any transactions.
>
>You probably want to read the following:
>http://developer.postgresql.org/docs/postgres/routine-vacuuming.html

Would recycling dead tuples on the fly (mentioned in p.14 in the article 
http://www.postgresql.org/files/developer/transactions.pdf ) significantly 
reduce the need for periodic vacuums?

Without knowing the internals, I have this simplistic idea: if Postgres 
maintains the current lowest transaction ID for all active transactions, it 
probably could recycle dead tuples on the fly. The current lowest 
transaction ID could be maintained in a doubly linked list with maximum 
<max_connections> entries. A backward link in the tuple header might be 
needed too.

Any comments?

Cheers,
KC.





In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-04-29 02:39:28
Subject: Re: Why so slow?
Previous:From: Gregory StewartDate: 2006-04-28 20:29:58
Subject: Performance Issues on Opteron Dual Core

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group