Обсуждение: statement stats extra load?
Is there a rule of thumb for the extra load that will be put on a system when statement stats are turned on? And if so, where does that extra load go? Disk? CPU? RAM? -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food"
Alan McKay wrote: > Is there a rule of thumb for the extra load that will be put on a > system when statement stats are turned on? > > And if so, where does that extra load go? Disk? CPU? RAM? As of 8.4.X the load isn't measurable. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
>> And if so, where does that extra load go? Disk? CPU? RAM? > > As of 8.4.X the load isn't measurable. Thanks Bruce. What about 8.3 since that is our current production DB? -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food"
Alan McKay wrote: > >> And if so, where does that extra load go? ? ?Disk? ?CPU? ?RAM? > > > > As of 8.4.X the load isn't measurable. > > Thanks Bruce. What about 8.3 since that is our current production DB? Same. All statsistics settings that are enabled by default have near-zero overhead. Is there a specific setting you are thinking of? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On 21 sep 2009, at 23.41, Bruce Momjian <bruce@momjian.us> wrote: > Alan McKay wrote: >>>> And if so, where does that extra load go? ? ?Disk? ?CPU? ?RAM? >>> >>> As of 8.4.X the load isn't measurable. >> >> Thanks Bruce. What about 8.3 since that is our current production >> DB? > > Same. All statsistics settings that are enabled by default have > near-zero overhead. Is there a specific setting you are thinking of? That's not true at all. If you have many relations in your cluster that have at some point been touched, the starts collector can create a *significant* load on the I/o system. I've come across several cases where the only choice was to disable the collector completely, even given all the drawbacks from that. 8.4 makes this *a lot* better with two new features. One enabled by default (write stats file on demand) and one you have to enable manually (stats file location). Using both these together can pretty much get rid of the issue, but there's no way in 8.3. /Magnus
On Tue, Sep 22, 2009 at 2:42 AM, Magnus Hagander <magnus@hagander.net> wrote: > That's not true at all. > > If you have many relations in your cluster that have at some point been > touched, the starts collector can create a *significant* load on the I/o > system. I've come across several cases where the only choice was to disable > the collector completely, even given all the drawbacks from that. Thanks Magnus, I thought that other response sounded a bit fanciful :-) So is there any way to predict the load this will have? Or just try it and hope for the best? :-) Right now on our 8.3 system it is off and we'd like to turn it on -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food"
On Tue, Sep 22, 2009 at 15:19, Alan McKay <alan.mckay@gmail.com> wrote: > On Tue, Sep 22, 2009 at 2:42 AM, Magnus Hagander <magnus@hagander.net> wrote: >> That's not true at all. >> >> If you have many relations in your cluster that have at some point been >> touched, the starts collector can create a *significant* load on the I/o >> system. I've come across several cases where the only choice was to disable >> the collector completely, even given all the drawbacks from that. > > Thanks Magnus, I thought that other response sounded a bit fanciful :-) > > So is there any way to predict the load this will have? Or just try > it and hope for the best? :-) IIRC, the size of the statsfile will be: * Some header data (small) * For each database, not much data (IIRC about 10-15 32-bit values, so less than 100 bytes) * For each table, around 25 32-bit values, so somewhere around 100 bytes It's the table stuff that can increase the size, unless you have very many databases with just one or so tables in them. The table stats will also be written for system tables. This file will be written twice per second on 8.3 and earlier (on 8.4, only on demand). It will be written as a new file and then renamed into place, so there is also filesystem operations being created - which unfortunately are on your main data drive (unless, again, you're on 8.4 and moved it to tmpfs) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/