From: Jim C. Nasby <jnasby@pervasive.com>
To: Thomas F. O'Connell <tfo@sitening.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: vacuumdb vs. max_connections: SELECT waiting
Date: Mon, 15 May 2006 10:07:53 -0500
Message-ID: <20060515150752.GC26212@pervasive.com> (raw)
In-Reply-To: <C51C85F1-45A0-46C6-99B0-2F64E511D26A@sitening.com>
References: <BC924EDD-8D0E-4A3D-AE9A-B43AF7770BC1@sitening.com>
<25842.1147489697@sss.pgh.pa.us>
<F85632C1-4F2B-47F8-900E-8D8F8DAC3CDB@sitening.com>
<26800.1147498528@sss.pgh.pa.us>
<C51C85F1-45A0-46C6-99B0-2F64E511D26A@sitening.com>
On Sat, May 13, 2006 at 03:47:00AM -0500, Thomas F. O'Connell wrote:
>
> On May 13, 2006, at 12:35 AM, Tom Lane wrote:
>
> >VACUUM FULL does all right at packing the table (except in
> >pathological
> >cases, eg a very large tuple near the end of the table). It mostly
> >bites as far as shrinking indexes goes, however. If you've got a
> >serious index bloat problem then REINDEX is the only solution.
> >CLUSTER
> >does an automatic REINDEX after compacting the table --- it doesn't
> >have
> >any special properties as far as the index space goes. However, if
> >you've got serious table bloat then CLUSTER will probably be quicker
> >than VACUUM FULL.
>
> So my reading of VACUUM VERBOSE output leads me to believe that I
> could actually interpret both table and index bloat for the entire
> database from it (and suggests a useful reporting tool...).
>
> This is from the docs:
>
> INFO: index "onek_unique1" now contains 1000 tuples in 14 pages
> DETAIL: 3000 index tuples were removed.
> 0 index pages have been deleted, 0 are currently reusable.
>
> Does this imply that 75% of the tuples in this index were free space?
> Even so, since this is an index, the pages aren't shrunk, per your
> note above, and a REINDEX would be required for reclamation, right?
Correct.
> And, then, later:
>
> INFO: "onek": removed 3000 tuples in 108 pages
> DETAIL: CPU 0.01s/0.06u sec elapsed 0.07 sec.
> INFO: "onek": found 3000 removable, 1000 nonremovable tuples in 143
> pages
> DETAIL: 0 dead tuples cannot be removed yet.
>
> Which implies that this table was also 75% bloated? And a VACUUM FULL
> (or CLUSTER) could improve this even more than the VACUUM VERBOSE
> ANALYZE from the example in the docs?
Also correct. There are some tricks you can do to shrink the table
without resorting to a vacuum full or a cluster though, but it's a real
PITA.
Also, remember that if your database is growing it will eventually end
up reusing all that free space.
> Unfortunately, there are several seqscan-using queries and several
> large tables (with the largest currently approaching 13% of physical
> memory).
So are you saying that the entire table fits in memory? Does the entire
database fit in memory? If so, you're unlikely to find huge gains from
vacuuming, unless you're really pushing the system hard.
> I'm actually trying to prioritize administrative operations that
> could result in noticeable performance gains. If compacting tables
> and indexes turns out to be low on the pole in terms of performance
> considerations, then I'm inclined to look elsewhere, especially
> considering the administrative headache (from the perspective of the
> related application) required to undertake a database-wide CLUSTER or
> VACUUM FULL + REINDEX.
It depends. On badly bloated systems I've seen 20-50% improvements from
setting up an adequate vacuum plan.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: jnasby@pervasive.com, tfo@sitening.com
Subject: Re: vacuumdb vs. max_connections: SELECT waiting
In-Reply-To: <20060515150752.GC26212@pervasive.com>
References: <BC924EDD-8D0E-4A3D-AE9A-B43AF7770BC1@sitening.com> <25842.1147489697@sss.pgh.pa.us> <F85632C1-4F2B-47F8-900E-8D8F8DAC3CDB@sitening.com> <26800.1147498528@sss.pgh.pa.us> <C51C85F1-45A0-46C6-99B0-2F64E511D26A@sitening.com> <20060515150752.GC26212@pervasive.com>
* 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