In response to Rafael Martinez <r.m.guerrero@xxxxxxxxxxx>: > 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@xxxxxxxxxxxxxxxxxxxxxxx Phone: 412-422-3463x4023 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
