From: John A Meinel <john@arbash-meinel.com>
To: SpaceBallOne <space_ball_one@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Can anyone explain this: duplicate dbs.
Date: Tue, 24 May 2005 22:00:34 -0500
Message-ID: <4293EA52.20005@arbash-meinel.com> (raw)
In-Reply-To: <BAY14-DAV16F2B796085B6591A93AFACC0E0@phx.gbl>
References: <BAY14-DAV38E580C92EFF811F1266ACC0E0@phx.gbl>
<4293E553.6070400@arbash-meinel.com>
<BAY14-DAV16F2B796085B6591A93AFACC0E0@phx.gbl>
SpaceBallOne wrote:
>> What version of postgres?
>
>
> 8.0.2 ... but I think I've seen this before on 7.3 ...
>
>> There are a few possibilities. If you are having a lot of updates to the
>> table, you can get index bloat. And vacuum doesn't fix indexes. You have
>> to "REINDEX" to do that. Though REINDEX has the same lock that VACUUM
>> FULL has, so you need to be a little careful with it.
>
>
>> Probably better is to do CLUSTER, as it does a REINDEX and a sort, so
>> your table ends up nicer when you are done.
>
>
> Thanks, will try those next time this problem crops up (i just deleted
> / recreated the database to speed things for its users in the office
> ... probably should have held off to see if I could find a solution
> first!).
>
> Yes, the database / table-in-question does have a lot of updates,
> deletes, and new rows (relatively speaking for a small business).
>
> Would CLUSTER / REINDEX still have an effect if our queries were done
> via sequential scan? This is a old database (as in built by me when i
> was just starting to learn unix / postgres) so the database design is
> pretty horrible (little normalisation, no indexes).
Well, my first recommendation is to put in some indexes. :) They are
relatively easy to setup and can drastically improve select performance.
What version of postgres are you using?
What does it say at the end of "VACUUM FULL ANALYZE VERBOSE", that
should tell you how many free pages were reclaimed and how big your free
space map should be.
If you only did 1 VACUUM FULL, you might try another, as it sounds like
your tables aren't properly filled. I'm pretty sure vacuum only removes
empty pages/marks locations for the free space map so they can be
re-used, while vacuum full will move entries around to create free pages.
It sounds like it didn't do it properly.
But even so, CLUSTER is still your friend, as it allows you to "presort"
the rows in your tables.
>
> Have taken Chris's advice onboard too and setup cron to do a vacuumdb
> hourly instead of my weekly vacuum.
>
> Cheers,
>
> Dave.
>
>
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
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: john@arbash-meinel.com, space_ball_one@hotmail.com
Subject: Re: Can anyone explain this: duplicate dbs.
In-Reply-To: <4293EA52.20005@arbash-meinel.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