In response to Rafael Martinez <r.m.guerrero@usit.uio.no>:
> Heikki Linnakangas wrote:
> > Rafael Martinez wrote:
>
> >> The tables with this 'problem' are not big, so CLUSTER finnish very fast
> >> and it does not have an impact in the access because of locking. But we
> >> wonder why this happens.
> >
> > 2 seconds for seq scanning 12 MB worth of data sounds like a lot. Have
> > you increased shared_buffers from the default? Which operating system
> > are you using? Shared memory access is known to be slower on Windows.
> >
>
> This is a server with 8GB of ram, we are using 25% as shared_buffers.
> Linux RHELAS4 with a 2.6.9-55.0.9.ELsmp kernel / x86_64.
>
> > On a small table like that you could run VACUUM every few minutes
> > without much impact on performance. That should keep the table size in
> > check.
> >
>
> Ok, we run VACUUM ANALYZE only one time a day, every night. But we would
> espect the performance to get ok again after running vacuum, and it
> doesn't. Only CLUSTER helps.
If you have a large value for max_fsm_pages, but only vacuum once a day,
you could end up with considerable bloat on a small table, but not enough
to exceed max_fsm_pages (thus you wouldn't see any warning/errors)
I recommend either:
a) autovaccum, with aggressive settings for that table
b) a more aggressive schedule for that particular table, maybe a cron
that vacuums that table every 5 minutes.
You could also do a combination, i.e. enable autovacuum with conservative
settings and set a cron to vacuum the table every 10 minutes.
Vacuuming once a day is usually only enough if you have very minimal
updates.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023