Re: sort_mem statistics ...

On Wed, Oct 26, 2005 at 06:50:49PM -0300, Marc G. Fournier wrote:
> On Wed, 26 Oct 2005, Jim C. Nasby wrote:
>
> >On Tue, Oct 18, 2005 at 06:15:02PM -0400, Tom Lane wrote:
> >>"Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> >>>do we maintain anything anywhere for this? mainly, some way of
> >>>determining # of 'sorts to disk' vs 'sort in memory', to determine
> >>>whether
> >>>or not sort_mem is set to a good value?
> >>
> >>As of 8.1 you could turn on trace_sort to collect some data about this.
> >
> >While trace_sort is good, it doesn't really help for monitoring. What I
> >would find useful would be statistics along the lines of:
> >
> >How many sorts have occured?
> >How many spilled to disk?
> >What's the largest amount of memory used by an in-memory sort?
> >What's the largest amount of memory used by an on-disk sort?
>
> Actually, I'd like to see largest/smallest and average in this ... but if
> all is being logged to syslog, I can easily determine those #s with a perl
> script ..

True, but like I said that doesn't help much for monitoring. I'm
generally concerned with finding out when stuff starts spilling to disk.

Is there a way to log only queries that spill to disk?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461