Re: Hash join on int takes 8..114 seconds

If Autovacuum was working, and your tables still got very bloated, it may be because your free space map is not
configuredlarge enough.
 
What is your value for max_fsm_pages?

The effect of having max_fsm_pages or max_fsm_relations too small is bloating of tables and indexes.

Increasing it too large will not use a lot of memory.  Since you had over 130000 free pages in just one table below,
makesure it is set to at least 150000.  This may be overkill with regular vacuum or autovacuum, however its much better
tohave this too large than too small.
 

Your server has 2GB of RAM?  You should make sure your shared_buffers is between 100MB and 400MB if this is a dedicated
server,or more in some cases.
 

If you can, plan to migrate to 8.3 (or 8.4 early next year).  Vacuum, bloating, and configuration related to these have
improveda great deal since 8.1.
 

Although your Indexes below remain bloated, the fact that they have been vacuumed, combined with a large enough value
setin max_fsm_pages, means that they should not get any larger.  I am not sure how to foce these to be smaller.
 
The larger size, after a vacuum and large enough max_fsm_pages value will also not cause a performance problem.  It
willwaste some disk space and be a bit slower to access, but only very slightly.
 


-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Andrus
Sent: Friday, November 21, 2008 1:51 PM
To: Richard Huxton
Cc: PFC; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Hash join on int takes 8..114 seconds

> If it's not a million rows, then the table is bloated. Try (as postgres
> or some other db superuser) "vacuum full pg_shdepend" and a "reindex
> pg_shdepend".

reindex table pg_shdepend causes error

ERROR:  shared table "pg_shdepend" can only be reindexed in stand-alone mode

vacuum full verbose pg_shdepend seems to work but indexes are still bloated.
How to remove index bloat ?

sizes after vacuum full are below.
pg_shdepend  size 1234 MB includes its index sizes, so indexes are 100%
bloated.

      4         1214 pg_catalog.pg_shdepend                        1234 MB
      6         1232 pg_catalog.pg_shdepend_depender_index         795 MB
      7         1233 pg_catalog.pg_shdepend_reference_index        439 MB

Andrus.


vacuum full verbose pg_shdepend;

INFO:  vacuuming "pg_catalog.pg_shdepend"
INFO:  "pg_shdepend": found 254 removable, 3625 nonremovable row versions in
131517 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 49 to 49 bytes long.
There were 16115259 unused item pointers.
Total free space (including removable row versions) is 1010091872 bytes.
131456 pages are or will become empty, including 8 at the end of the table.
131509 pages containing 1010029072 free bytes are potential move
destinations.
CPU 2.08s/0.92u sec elapsed 63.51 sec.
INFO:  index "pg_shdepend_depender_index" now contains 3625 row versions in
101794 pages
DETAIL:  254 index row versions were removed.
101611 index pages have been deleted, 20000 are currently reusable.
CPU 0.87s/0.28u sec elapsed 25.44 sec.
INFO:  index "pg_shdepend_reference_index" now contains 3625 row versions in
56139 pages
DETAIL:  254 index row versions were removed.
56076 index pages have been deleted, 20000 are currently reusable.
CPU 0.51s/0.15u sec elapsed 23.10 sec.
INFO:  "pg_shdepend": moved 1518 row versions, truncated 131517 to 25 pages
DETAIL:  CPU 5.26s/2.39u sec elapsed 89.93 sec.
INFO:  index "pg_shdepend_depender_index" now contains 3625 row versions in
101794 pages
DETAIL:  1518 index row versions were removed.
101609 index pages have been deleted, 20000 are currently reusable.
CPU 0.94s/0.28u sec elapsed 24.61 sec.
INFO:  index "pg_shdepend_reference_index" now contains 3625 row versions in
56139 pages
DETAIL:  1518 index row versions were removed.
56088 index pages have been deleted, 20000 are currently reusable.
CPU 0.54s/0.14u sec elapsed 21.11 sec.

Query returned successfully with no result in 253356 ms




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance