Re: performance; disk bad or something? - Mailing list pgsql-general

Michael Fuhr skrev:
> On Sat, Apr 07, 2007 at 09:28:58AM +0200, Marcus Engene wrote:
>
>> INFO:  "apa_item_common": removed 9028 row versions in 3651 pages
>> DETAIL:  CPU 0.24s/0.36u sec elapsed 30.69 sec.
>> INFO:  "apa_item_common": found 9028 removable, 12863 nonremovable row
>> versions in 14489 pages
>> DETAIL:  0 dead row versions cannot be removed yet.
>> There were 76646 unused item pointers.
>>
>
> How often does this table receive updates and deletes and how often
> are you vacuuming it?  It averages less than one row per page (12863
> nonremovable row versions in 14489 pages) and appears to have become
> quite bloated with dead rows sometime in the past (76646 unused
> item pointers).  Use CLUSTER or VACUUM FULL + REINDEX to compact
> the table and run ANALYZE afterwards to update the statistics, then
> make sure you're vacuuming it often enough to keep it from becoming
> bloated again.
>
> Is your free space map sufficiently sized?  If you do a database-wide
> VACUUM VERBOSE, what are the last few lines of the output that
> mention free space map settings?
>
>
If I should take a guess, there are 5 deletes per day and 5 updates or
inserts per hour. The table is 1.5 years old and I try to vacuuming it
once a week; although without "full". I normally do a reindex as well.

I've googled a bit to find optimizer hints a la oracle's /*+
index(asdasd) */ but from what I can tell pg has chosen not to use that?
I find them convenient for testing at least, even if I agree that one
perhaps should avoid having them in a final product. Toggling role/chose
in Oracle is something I've often had use for too.

The original select seems to be consistantly fast now. That is good, but
do I have a ticking bomb? 12k rows is little by any measure and if it
was so slow by a little bloat it will be inevitable to reoccur again?
Worth mentioning is perhaps that I also have a tsearch2 index on each
row that is about 50words each. But timed lookups on that index, which
one would expect to be the slowest(?), always seem to be blazingly fast.

Is 8.2.x better at these simple things too or is it mainly complex
multithreadable queries which will benefit from it?

I hadn't touched any fsm settings but I've now set it to
max_fsm_pages = 200000          # min max_fsm_relations*16, 6 bytes each 20k
max_fsm_relations = 10000       # min 100, ~50 bytes each 1k

Thanks for your help!
Marcus

apa=# vacuum full verbose apa_item_common;
INFO:  vacuuming "public.apa_item_common"
INFO:  "apa_item_common": found 176 removable, 12866 nonremovable row
versions in 14489 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 220 to 2032 bytes long.
There were 85496 unused item pointers.
Total free space (including removable row versions) is 103371272 bytes.
8673 pages are or will become empty, including 0 at the end of the table.
14479 pages containing 103370096 free bytes are potential move destinations.
CPU 0.38s/0.04u sec elapsed 60.17 sec.
INFO:  index "apa_item_common_pkey" now contains 12866 row versions in
36 pages
DETAIL:  176 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.20 sec.
INFO:  index "apa_item_common_x1" now contains 12866 row versions in 38
pages
DETAIL:  176 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.84 sec.
INFO:  index "apa_item_common_fts" now contains 12866 row versions in
396 pages
DETAIL:  176 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.01u sec elapsed 1.67 sec.
INFO:  index "apa_item_common_x2" now contains 12866 row versions in 36
pages
DETAIL:  176 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.88 sec.
INFO:  "apa_item_common": moved 10868 row versions, truncated 14489 to
1832 pages
DETAIL:  CPU 1.77s/21.13u sec elapsed 294.11 sec.
INFO:  index "apa_item_common_pkey" now contains 12866 row versions in
58 pages
DETAIL:  10868 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.02u sec elapsed 0.35 sec.
INFO:  index "apa_item_common_x1" now contains 12866 row versions in 69
pages
DETAIL:  10868 index row versions were removed.
4 index pages have been deleted, 4 are currently reusable.
CPU 0.00s/0.02u sec elapsed 0.40 sec.
INFO:  index "apa_item_common_fts" now contains 12866 row versions in
671 pages
DETAIL:  10868 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.04u sec elapsed 1.80 sec.
INFO:  index "apa_item_common_x2" now contains 12866 row versions in 67
pages
DETAIL:  10868 index row versions were removed.
21 index pages have been deleted, 21 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.34 sec.
INFO:  vacuuming "pg_toast.pg_toast_181470"
INFO:  "pg_toast_181470": found 10 removable, 1040 nonremovable row
versions in 1288 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 174 to 2034 bytes long.
There were 6729 unused item pointers.
Total free space (including removable row versions) is 9236604 bytes.
732 pages are or will become empty, including 0 at the end of the table.
1288 pages containing 9236604 free bytes are potential move destinations.
CPU 0.02s/0.00u sec elapsed 3.91 sec.
INFO:  index "pg_toast_181470_index" now contains 1040 row versions in 5
pages
DETAIL:  10 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.17 sec.
INFO:  "pg_toast_181470": moved 832 row versions, truncated 1288 to 164
pages
DETAIL:  CPU 0.02s/0.14u sec elapsed 3.51 sec.
INFO:  index "pg_toast_181470_index" now contains 1040 row versions in 8
pages
DETAIL:  832 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
apa=# analyze verbose apa_item_common;
INFO:  analyzing "public.apa_item_common"
INFO:  "apa_item_common": scanned 1871 of 1871 pages, containing 12875
live rows and 228 dead rows; 3000 rows in sample, 12875 estimated total rows
ANALYZE
apa=#