Re: Help tuning postgres

From: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Help tuning postgres
Date: 2005-10-18 16:48:06
Message-ID: 20051018164806.GH3441@phlogiston.dyndns.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Tue, Oct 18, 2005 at 05:21:37PM +0200, Csaba Nagy wrote:
> INFO: vacuuming "public.some_table"
> INFO: "some_table": removed 29598 row versions in 452 pages
> DETAIL: CPU 0.01s/0.04u sec elapsed 18.77 sec.
> INFO: "some_table": found 29598 removable, 39684 nonremovable row
> versions in 851 pages
> DETAIL: 0 dead row versions cannot be removed yet.

> Does that mean that 39684 nonremovable pages are actually the active
> live pages in the table (as it reports 0 dead) ? I'm sure I don't have
> any long running transaction, at least according to pg_stats_activity
> (backed by the linux ps too). Or I should run a vacuum full...
>
> This table is one of which has frequently updated rows.

No, you should be ok there. What that should tell you is that you
have about 40,000 rows in the table. But notice that your vacuum
process just removed about 75% of the live table rows. Moreover,
your 39684 rows are taking 851 pages. On a standard installation,
that's usually 8Kb/page. So that's about 6,808 Kb of physical
storage space you're using. Is that consistent with the size of your
data? If it's very large compared to the data you have stored in
there, you may want to ask if you're "leaking" space from the free
space map (because of that table turnover, which seems pretty
severe).

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The whole tendency of modern prose is away from concreteness.
--George Orwell

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Rodrigo Madera 2005-10-18 18:07:12 Inefficient escape codes.
Previous Message Csaba Nagy 2005-10-18 15:49:36 Re: Help tuning postgres