On Thursday 20 November 2003 20:29, Shridhar Daithankar wrote: > On Thursday 20 November 2003 20:00, Matthew T. O'Connor wrote: > > Shridhar Daithankar wrote: > > > I will submit a patch that would account deletes in analyze threshold. > > > Since you want to delay the analyze, I would calculate analyze count as > > > > deletes are already accounted for in the analyze threshold. > > Yes. My bad. Deletes are not accounted in initializing analyze count but > later they are used. > > > > I am still wary of inverting vacuum analyze frequency. You think it is > > > better to set inverted default rather than documenting it? > > > > I think inverting the vacuum and analyze frequency is wrong. > > Me. Too. ATM all I can think of this patch attached. Josh, is it sufficient > for you?..:-)
use this one. A warning added for too aggressive vacuumming. If it is OK by everybody, we can send it to patches list. Shridhar
*** README.pg_autovacuum.orig Thu Nov 20 19:58:29 2003 --- README.pg_autovacuum Thu Nov 20 20:35:34 2003 *************** *** 141,163 **** depending on the mixture of table activity (insert, update, or delete): ! - If the number of (inserts + updates + deletes) > AnalyzeThreshold, then only an analyze is performed. ! - If the number of (deletes + updates) > VacuumThreshold, then a vacuum analyze is performed. VacuumThreshold is equal to: ! vacuum_base_value + (vacuum_scaling_factor * "number of tuples in the table") ! AnalyzeThreshold is equal to: ! analyze_base_value + (analyze_scaling_factor * "number of tuples in the table") ! The AnalyzeThreshold defaults to half of the VacuumThreshold since it represents a much less expensive operation (approx 5%-10% of vacuum), and running ANALYZE more often should not substantially degrade system performance. Sleeping: --------- --- 141,191 ---- depending on the mixture of table activity (insert, update, or delete): ! - If the number of (inserts + updates + deletes) >= AnalyzeThreshold, then only an analyze is performed. ! - If the number of (deletes + updates) >= VacuumThreshold, then a vacuum analyze is performed. VacuumThreshold is equal to: ! vacuum_base_value + (vacuum_scaling_factor * "number of tuples in the ! table") AnalyzeThreshold is equal to: ! analyze_base_value + (analyze_scaling_factor * "number of tuples in the ! table") The AnalyzeThreshold defaults to half of the VacuumThreshold since it represents a much less expensive operation (approx 5%-10% of vacuum), and running ANALYZE more often should not substantially degrade system performance. + Examples: + + Following table shows typical usage of pg_autovacuum settings. + These are put here so that a DBA can have some starting point while + tuning pg_autovacuum. + + Vacuum is triggered by updates and deletes. So in case of vacuum, + last column indicates total of updates and deletes required + to trigger vacuum. In case of analyze, the operations would count total + number of inserts, updates and deletes. + + Base Scaling factor Records No. of Operations + 1,000 1 10,000 11,000 + 1,000 2 10,000 21,000 + 1,000 0.5 10,000 6,000 + 1,000 0.1 10,000 2,000 + + Although analyze is cheaper operation compared to vacuum, + it might be needed less often. The default is to analyze twice as much as + vacuum but that might be too aggressive for some installations. It is advised + thatsuch installation tune their analyze threshold separately, rather than + relying upon the default behaviour. + + Furthermore, for aggressive vacuum/analyze behaviour, it is recommended that + scaling factor is set to less than 1. However too aggresive operation can affect + performance of normal database operations adversely. Do not apply such setting + to production databases without prior testing. + Sleeping: ---------
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org