Re: BUG #2358: Vacuum & \dt problems

It sounds like you probably ran for a long time (or more accurately did
a large number of updates/deletes) without vacuuming. Due to the way
vacuum works, this would result in a very large amount of wasted space.

While you could VACUUM FULL and REINDEX to fix this, in your case you'd
probably be best off just dumping the database and reloading.

Also, 8.0.1 is very old. You should really upgrade to at least 8.0.7, or
8.1.3.

On Sun, Mar 26, 2006 at 12:01:09AM +0000, Alexis Wilke wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2358
> Logged by: Alexis Wilke
> Email address: alexis(at)m2osw(dot)com
> PostgreSQL version: 8.0.1
> Operating system: Linux 2.4.20-8 i686
> Description: Vacuum & \dt problems
> Details:
>
> I have a database which is fairly small (pgdump in SQL text format is just
> about 600Kb, really small! the largest table is 12 columns and 2635 rows...)
> and yet after I played with the database after a day or so, it gets dead
> slow when I try VACUUM it or worse when I attempt a \dt command on it.
>
> The vacuum can sit there for over 1h doing nothing much since the CPU usage
> is less than 1% most of the time. It seems to me this is because it is
> taking all its time querying the large files (my assumption because my
> system locks up intermittendly when I run the vacuum, as if it needed to
> access the drive and was short in resources).
>
> I have 1Gb of RAM though... it's not huge, but I'd hope it should be
> enough... except there are 3 1Gb files in that database folder!
>
> Similarly, the \dt can take a good 20 min. before listing anything. Even the
> 2nd time! And that's if I don't vacuum for a few days only.
>
> Now this could come from my hard drive too... but on the other hand, all the
> SQL commands are dead fast! No worries at all with those (be it SELECT,
> UPDATE or INSERT). So I'm not too sure why VACUUM can be so slow... I've
> seen some talks about using REINDEX instead of VACUUM... would that help and
> is that enough to 'clean up' the database?!
>
> As an extra note, I have had the following error:
>
> xmlc=# vacuum;
> ERROR: failed to re-find parent key in "pg_class_relname_nsp_index"
>
> which seems to have been fixed with a REINDEX on that table. It seems that's
> a rare hard to reproduce one.
>
> Thank you for your input!
> Alexis Wilke
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461