Re: sort_mem statistics ...

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?

I don't think there is currently, but wondering how hard it would be to
get something like this added ... ?

thanks ..

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

"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.

Looking at the code, I notice that the messages are all emitted at level
NOTICE. Perhaps that was not such a good idea --- it'd be pretty much
in-your-face if it were on all the time. Does anyone think it'd be a
good idea to emit the trace_sort messages at level LOG, instead?

regards, tom lane

On Tue, 18 Oct 2005, 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.
>
> Looking at the code, I notice that the messages are all emitted at level
> NOTICE. Perhaps that was not such a good idea --- it'd be pretty much
> in-your-face if it were on all the time. Does anyone think it'd be a
> good idea to emit the trace_sort messages at level LOG, instead?

If someone sets trace_sort, does it matter what level its emit'd at? Its
not on by default, at least :)

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

"Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> On Tue, 18 Oct 2005, Tom Lane wrote:
>> Looking at the code, I notice that the messages are all emitted at level
>> NOTICE. Perhaps that was not such a good idea --- it'd be pretty much
>> in-your-face if it were on all the time. Does anyone think it'd be a
>> good idea to emit the trace_sort messages at level LOG, instead?

> If someone sets trace_sort, does it matter what level its emit'd at?

Well, yeah. It depends whether you are thinking of the trace feature as
being used interactively, or as something turned on to gather data over
time in a production installation. In the second case you'd want the
info to go to the postmaster log, but not want to see it dumped on your
terminal all the time ...

regards, tom lane

On Tue, 18 Oct 2005, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
>> On Tue, 18 Oct 2005, Tom Lane wrote:
>>> Looking at the code, I notice that the messages are all emitted at level
>>> NOTICE. Perhaps that was not such a good idea --- it'd be pretty much
>>> in-your-face if it were on all the time. Does anyone think it'd be a
>>> good idea to emit the trace_sort messages at level LOG, instead?
>
>> If someone sets trace_sort, does it matter what level its emit'd at?
>
> Well, yeah. It depends whether you are thinking of the trace feature as
> being used interactively, or as something turned on to gather data over
> time in a production installation. In the second case you'd want the
> info to go to the postmaster log, but not want to see it dumped on your
> terminal all the time ...

Oops, sorry, I was thinking in terms of syslog log levels ... :(

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

Tom,

Tom Lane wrote:
>>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.

Why is the trace_sort option DEVELOPER_OPTIONS?

I think the sort statistics are *very* important for DBAs,
not only for developers (hackers).

Without any numerical evidence, trying (and error) to fitwork_mem value
will be painfull and wasting DBA's time.

And I want to get statistic info through system views, like pg_statio_*.

Please remember my previous post.
http://archives.postgresql.org/pgsql-patches/2005-09/msg00116.php

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>

Satoshi,

> And I want to get statistic info through system views, like pg_statio_*.

I don't think anyone disagrees with that. It's just a little too late to
get in for 8.1.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
>>And I want to get statistic info through system views, like pg_statio_*.
>
> I don't think anyone disagrees with that. It's just a little too late to
> get in for 8.1.

Thanks for comment. I hope 8.2 will get it.

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>

> 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?
>
> I don't think there is currently, but wondering how hard it would be to
> get something like this added ... ?

While on the subject of stats - is there any way to count the total
transactions that have occurred since the last stats reset? Do we track
that single number somewhere?

Chris

Isn't that what pg_stat_database reports with its xact_commit and
xact_rollback values?

On Wed, 19 Oct 2005, Christopher Kings-Lynne wrote:

>> 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?
>>
>> I don't think there is currently, but wondering how hard it would be to get
>> something like this added ... ?
>
> While on the subject of stats - is there any way to count the total
> transactions that have occurred since the last stats reset? Do we track that
> single number somewhere?
>
> Chris
>
>

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

> Isn't that what pg_stat_database reports with its xact_commit and
> xact_rollback values?

Ah yes. Doh :)

Chris

On Tue, 2005-10-18 at 18:57 -0400, Tom Lane wrote:
> "Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> > On Tue, 18 Oct 2005, Tom Lane wrote:
> >> Looking at the code, I notice that the messages are all emitted at level
> >> NOTICE. Perhaps that was not such a good idea --- it'd be pretty much
> >> in-your-face if it were on all the time. Does anyone think it'd be a
> >> good idea to emit the trace_sort messages at level LOG, instead?
>
> > If someone sets trace_sort, does it matter what level its emit'd at?
>
> Well, yeah. It depends whether you are thinking of the trace feature as
> being used interactively, or as something turned on to gather data over
> time in a production installation. In the second case you'd want the
> info to go to the postmaster log, but not want to see it dumped on your
> terminal all the time ...

Yes, please set this at LOG.

It will certainly provide many more data points for us to analyse.

Best Regards, Simon Riggs

Tom Lane wrote:
> "Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> > On Tue, 18 Oct 2005, Tom Lane wrote:
> >> Looking at the code, I notice that the messages are all emitted at level
> >> NOTICE. Perhaps that was not such a good idea --- it'd be pretty much
> >> in-your-face if it were on all the time. Does anyone think it'd be a
> >> good idea to emit the trace_sort messages at level LOG, instead?
>
> > If someone sets trace_sort, does it matter what level its emit'd at?
>
> Well, yeah. It depends whether you are thinking of the trace feature as
> being used interactively, or as something turned on to gather data over
> time in a production installation. In the second case you'd want the
> info to go to the postmaster log, but not want to see it dumped on your
> terminal all the time ...

I think it should go to the logs, hence LOG. Right now it just scrolls
off my screen:

test=> select * from pg_class order by relname;
NOTICE: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = t
NOTICE: performsort starting: CPU 0.00s/0.00u sec elapsed 0.00 sec
NOTICE: performsort done: CPU 0.00s/0.00u sec elapsed 0.00 sec
NOTICE: sort ended: CPU 0.00s/0.00u sec elapsed 0.00 sec
relname | relnamespace | reltype | relowner |
relam | relfilenode | reltablespace | relpages | reltup
les | reltoastrelid | reltoastidxid | relhasindex | relisshared |
relkind | relnatts | relchecks | reltriggers | relukeys | relf
...

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Tom Lane wrote:
> > "Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> > > On Tue, 18 Oct 2005, Tom Lane wrote:
> > >> Looking at the code, I notice that the messages are all emitted at level
> > >> NOTICE. Perhaps that was not such a good idea --- it'd be pretty much
> > >> in-your-face if it were on all the time. Does anyone think it'd be a
> > >> good idea to emit the trace_sort messages at level LOG, instead?
> >
> > > If someone sets trace_sort, does it matter what level its emit'd at?
> >
> > Well, yeah. It depends whether you are thinking of the trace feature as
> > being used interactively, or as something turned on to gather data over
> > time in a production installation. In the second case you'd want the
> > info to go to the postmaster log, but not want to see it dumped on your
> > terminal all the time ...
>
> I think it should go to the logs, hence LOG. Right now it just scrolls
> off my screen:
>
> test=> select * from pg_class order by relname;
> NOTICE: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = t
> NOTICE: performsort starting: CPU 0.00s/0.00u sec elapsed 0.00 sec
> NOTICE: performsort done: CPU 0.00s/0.00u sec elapsed 0.00 sec
> NOTICE: sort ended: CPU 0.00s/0.00u sec elapsed 0.00 sec
> relname | relnamespace | reltype | relowner |
> relam | relfilenode | reltablespace | relpages | reltup
> les | reltoastrelid | reltoastidxid | relhasindex | relisshared |
> relkind | relnatts | relchecks | reltriggers | relukeys | relf
> ...

Simon also agrees, so changed to LOG in CVS.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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?
--
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

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 ..

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

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