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

On Sat, Apr 07, 2007 at 01:49:38PM +0200, Marcus Engene wrote:
> Michael Fuhr skrev:
> >How often does this table receive updates and deletes and how often
> >are you vacuuming it?
>
> 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.

The first VACUUM output showed 9028 removable and 12863 nonremovable
rows, which indicates more activity than your guess.  I'd suggest
vacuuming the table (without FULL) more often than once per week;
this table is small enough that vacuuming should take at most a few
seconds if you do it often enough.  One way to ensure that this
happens is to use cron (or whatever your system's scheduler is) to
run VACUUM ANALYZE nightly or more often.  Or use autovacuum, which
is available in 8.0 and earlier as a contributed module and in 8.1
and later as a core component (but not enabled by default).

> 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.

You can influence the query planner with configuration settings.
Here's the relevant section in the 8.0 documentation:

http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-QUERY

I'd recommend increasing effective_cache_size if you're still using
the default.  Most of the remaining settings are best used only for
debugging unless you can't get a reasonable query plan any other
way, and then I'd recommend changing settings only for specific
queries (and resetting them after those queries) and not changing
the system-wide values.

> 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?

The table was more than a little bloated -- VACUUM FULL shrunk it
from 14489 pages to 1832 pages, which means that the table was
nearly 8 times larger than it needed to be.  You could get an idea
of the amount of bloat from the first VACUUM output:

> 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.

The table had almost as many dead rows (9028) as live rows (12863)
and it had additional space (76646) for many times more rows than
the table contained.

If you vacuum often enough then tables shouldn't become bloated.
Autovacuum can help in this respect.

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

8.1 and later have autovacuum as a core component; if you enable
it then the database will vacuum tables as needed (in 8.1 you might
want to lower the default scale factors and thresholds; 8.2's
defaults are halved compared to 8.1's).  New major releases have
other improvements such as better query planning.

> 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

The output of a database-wide VACUUM VERBOSE will indicate whether
the fsm settings are high enough.

--
Michael Fuhr