Обсуждение: postgresql.conf runtime statistics default
Hi, after having migrated a 7.2 pg-database to 7.4 while upgrdaing from debian woody to debian sarge there are some more conf-Parameters to evaluate. We are running a small but continuously growing datawarehouse which has recently around 40 million fact entries. To my question: I found the parameter "stats_reset_on_server_start" which is set to true by default. Why did you choose this (and not false) and what are the impacts of changeing it to false? I mean, as long as I understood it, each query or statements generates some statistic data which is used by the optimizer (or anything equal) later on. So in my oppinion, wouldn't it be better so set this parameter to false and to enable a kind of a "startup reset_stats" option? Regards, Yann
Yann Michel wrote: > > To my question: I found the parameter "stats_reset_on_server_start" > which is set to true by default. Why did you choose this (and not false) > and what are the impacts of changeing it to false? I mean, as long as I > understood it, each query or statements generates some statistic data > which is used by the optimizer (or anything equal) later on. So in my > oppinion, wouldn't it be better so set this parameter to false and to > enable a kind of a "startup reset_stats" option? This is administrator statistics (e.g. number of disk blocks read from this index) not planner statistics. You're right - it would be foolish to throw away planner stats. -- Richard Huxton Archonet Ltd
Hi, On Thu, Jun 09, 2005 at 02:11:22PM +0100, Richard Huxton wrote: > > > >To my question: I found the parameter "stats_reset_on_server_start" > >which is set to true by default. Why did you choose this (and not false) > >and what are the impacts of changeing it to false? I mean, as long as I > >understood it, each query or statements generates some statistic data > >which is used by the optimizer (or anything equal) later on. So in my > >oppinion, wouldn't it be better so set this parameter to false and to > >enable a kind of a "startup reset_stats" option? > > This is administrator statistics (e.g. number of disk blocks read from > this index) not planner statistics. You're right - it would be foolish > to throw away planner stats. So what is best to set this parameter to and when? As I read this parameter is documented within the section "16.4.7.2. Query and Index Statistics Collector" so I guess it is better to set it to false as described above. Or am I wrong? Regards, Yann
Yann Michel wrote: > Hi, > > On Thu, Jun 09, 2005 at 02:11:22PM +0100, Richard Huxton wrote: > >>>To my question: I found the parameter "stats_reset_on_server_start" >>>which is set to true by default. Why did you choose this (and not false) >>>and what are the impacts of changeing it to false? I mean, as long as I >>>understood it, each query or statements generates some statistic data >>>which is used by the optimizer (or anything equal) later on. So in my >>>oppinion, wouldn't it be better so set this parameter to false and to >>>enable a kind of a "startup reset_stats" option? >> >>This is administrator statistics (e.g. number of disk blocks read from >>this index) not planner statistics. You're right - it would be foolish >>to throw away planner stats. > > > So what is best to set this parameter to and when? As I read this > parameter is documented within the section "16.4.7.2. Query and Index > Statistics Collector" so I guess it is better to set it to false as > described above. Or am I wrong? It depends on whether you want to know how much activity your tables/indexes have received *ever* or since you last restarted. If you altered your database schema, added/removed indexes or changed hardware/configuration then you might want to reset the counts to zero to more easily see the effect of the new setup. -- Richard Huxton Archonet Ltd