Re: [PERFORM] statement stats extra load?
On Tue, Sep 22, 2009 at 15:19, Alan McKay alan.mc...@gmail.com wrote: On Tue, Sep 22, 2009 at 2:42 AM, Magnus Hagander mag...@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/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] statement stats extra load?
On 21 sep 2009, at 23.41, Bruce Momjian br...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] statement stats extra load?
On Tue, Sep 22, 2009 at 2:42 AM, Magnus Hagander mag...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] statement stats extra load?
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] statement stats extra load?
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] statement stats extra load?
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance