From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Help tuning postgres
Date: Tue, 18 Oct 2005 12:48:06 -0400
Message-ID: <20051018164806.GH3441@phlogiston.dyndns.org> (raw)
In-Reply-To: <1129648896.27587.22.camel@coppola.muc.ecircle.de>
References: <1129128315.2995.174.camel@coppola.muc.ecircle.de>
<f3c0b4080510120939x6c08feebvfad37c98df306538@mail.gmail.com>
<1129136130.2995.188.camel@coppola.muc.ecircle.de>
<20051012220036.GE13571@phlogiston.dyndns.org>
<1129191303.2995.192.camel@coppola.muc.ecircle.de>
<20051013124007.GA15592@phlogiston.dyndns.org>
<1129648896.27587.22.camel@coppola.muc.ecircle.de>
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@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ajs@crankycanuck.ca
Subject: Re: Help tuning postgres
In-Reply-To: <20051018164806.GH3441@phlogiston.dyndns.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox