Re: For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

> Unfortunately, folks like Phoenix are looking for yes/no answers, and
> with many of these questions, the _correct_ answer is "it depends on
> your workload"


I wanted merely to simplify the advice that gets dispensed on this
list, often conflicting to novice ears like mine. So I appreciate your
notes very much.


> If you find that reindexing improves performance, then you should
> investigate further.  Depending on the exact nature of the problem,
> there are many possible solutions, three that come to mind:
> * Add RAM/SHM


Can I add SHM with merely by managing the entry in sysctl.conf? My
current values:

kernel.shmmax = 536870912
kernel.shmall = 536870912

My "shared_buffers" in postgresql.conf is "20000". From the website
http://www.desknow.com/kb/idx/12/061/article/ I notice that shmmax
should be sharedbuffer*8192, so I suppose my shmmax can be much lower
than the above, but I raised it for performance. Am I wrong to do so?


> * REINDEX on a regular schedule


This is sadly not really feasible, because we need to offer a 100%
availability website. REINDEX does not work concurrently so it is not
really an option for us. My max_fsm_pages and max_fsm_relations are
way above the numbers that come up after the VACUUM ANALYZE VERBOSE
run.

But still, the autovacuum stuff seems like it is not working at all.
Some related entries in the conf file:

autovacuum                   = on
autovacuum_vacuum_cost_delay = 20
vacuum_cost_delay            = 20
autovacuum_naptime           = 30
stats_start_collector        = on
stats_row_level              = on
autovacuum_vacuum_threshold  = 80
autovacuum_analyze_threshold = 80

And yet, the db often slows down, at which point I manually login and
run a manual VACUUM ANALYZE and it seems fine for some more time.
Sometimes, I also restart pgsql and that seems to help for a while.

Another advice on these forums is to see "vmstat 1", without actually
specifying how to draw inferences from it. The "free" version of it is
coming up at decent rates, as follows:


procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 1  0  29124 110760 108980 3467736    0    1   206   140    0     4  2  1 85 12
 0  0  29124 110632 108980 3467736    0    0     0     0 1052   108  0  0 100  0
 2  0  29124 108840 108980 3467736    0    0     0     0 1112   299  1  1 98  0
 1  0  29124 109288 108980 3467736    0    0     0     0 1073   319  2  1 98  0
.....




> * (with newer version) reduce the fill factor and REINDEX
>


I think some of my tables are updated very frequently so a smaller
fill factor will be nice. How can I find the current fill factor on my
tables? Also, is there some method or science to calculating a decent
fill factor -- size of table, number of indexes, frequency of updates,
and such? We have one major table which faces a lot of INSERTs and
UPDATES in a day (up to 10,000) but many many more SELECTs (up to 10
million).

Thanks.