> 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. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
