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

From: Scott Carey <scott(at)richrelevance(dot)com>
To: Andrus <kobruleht2(at)hot(dot)ee>, Richard Huxton <dev(at)archonet(dot)com>
Cc: PFC <lists(at)peufeu(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Hash join on int takes 8..114 seconds
Date: 2008-11-21 23:01:41
Message-ID: BDFBB77C9E07BE4A984DAAE981D19F961ACA1F1513@EXVMBX018-1.exch018.msoutlookonline.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

If Autovacuum was working, and your tables still got very bloated, it may be because your free space map is not configured large 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, make sure it is set to at least 150000. This may be overkill with regular vacuum or autovacuum, however its much better to have 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 improved a great deal since 8.1.

Although your Indexes below remain bloated, the fact that they have been vacuumed, combined with a large enough value set in 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 will waste some disk space and be a bit slower to access, but only very slightly.

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Andrus
Sent: Friday, November 21, 2008 1:51 PM
To: Richard Huxton
Cc: PFC; pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message PFC 2008-11-22 14:13:53 Re: Hash join on int takes 8..114 seconds
Previous Message Andrus 2008-11-21 21:51:24 Re: Hash join on int takes 8..114 seconds