Re: New DTrace probes proposal

(Resending since it didn't work the first time. Not sure if attaching a
tar file was the culprit.)

I'd like to propose adding the following probes (some of which came from
Simon) to 8.4.

I think these probe provide very useful data. Although some of the data
can be collected now, the main advantages with probes, among others, are
(1) they are always available and can be enabled only when needed
especially in production (2) different combinations of probes can be
used together to collect interesting data.

They work on OS X Leopard & Solaris now, and hopefully on FreeBSD soon.

Preliminary patch attached along with sample DTrace scripts.

-Robert

---------------

* Probes to measure query time
query-parse-start (int, char *)
query-parse-done (int, char *)
query-plan-start ()
query-plan-done ()
query-execute-start ()
query-execute-done ()
query-statement-start (int, char *)
query-statement-done (int, char *)

* Probes to measure dirty buffer writes by the backend because bgwriter
is not effective
dirty-buffer-write-start (int, int, int, int)
dirty-buffer-write-done (int, int, int, int)

* Probes to measure physical writes from the shared buffer
buffer-write-start (int, int, int, int)
buffer-write-done (int, int, int, int, int)

* Probes to measure reads of a relation from a particular buffer block
buffer-read-start (int, int, int, int, int)
buffer-read-done (int, int, int, int, int, int)

* Probes to measure the effectiveness of buffer caching
buffer-hit ()
buffer-miss ()

* Probes to measure I/O time because wal_buffers is too small
wal-buffer-write-start ()
wal-buffer-write-done ()

* Probes to measure checkpoint stats such as running time, buffers
written, xlog files added, removed, recycled, etc
checkpoint-start (int)
checkpoint-done (int, int, int, int, int)

* Probes to measure Idle in Transaction and client/network time
idle-transaction-start (int, int)
idle-transaction-done ()

* Probes to measure sort time
sort-start (int, int, int, int, int)
sort-done (int, long)

* Probes to determine whether or not the deadlock detector has found a
deadlock
deadlock-found ()
deadlock-notfound (int)

* Probes to measure reads/writes by block numbers and relations
smgr-read-start (int, int, int, int)
smgr-read-end (int, int, int, int, int, int)
smgr-write-start (int, int, int, int)
smgr-write-end (int, int, int, int, int, int)

On Sat, 17 May 2008, Robert Lor wrote:

> I'd like to propose adding the following probes (some of which came from
> Simon) to 8.4.

There's also a big DTrace probe set patch available from OmniTI:
https://labs.omniti.com/project-dtrace/trunk/postgresql/
http://labs.omniti.com/trac/project-dtrace/wiki/Applications#PostgreSQL

I don't know if you've looked at that before. There's some overlap but
many unique and handy probes to each set. I think it would be nice to
consider a superset union of the two. I would guess OmniTI would be glad
to have their set assimilated into core as well so they don't have to
maintain their patch past 8.3; hopefully Theo or Robert will chime in on
that.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Greg Smith wrote:
> There's also a big DTrace probe set patch available from OmniTI:
> https://labs.omniti.com/project-dtrace/trunk/postgresql/
> http://labs.omniti.com/trac/project-dtrace/wiki/Applications#PostgreSQL
>
> I don't know if you've looked at that before. There's some overlap
> but many unique and handy probes to each set. I think it would be
> nice to consider a superset union of the two.
I heard about Theo's probes recently, but I haven't had a chance to look
at them closely. I'm more than happy to adapt his probes for 8.4 and
remove any duplicates if there are no objections from Theo.
> I would guess OmniTI would be glad to have their set assimilated into
> core as well so they don't have to maintain their patch past 8.3;
I'd think so too!

-Robert

On Sunday 18 May 2008 03:18:13 Greg Smith wrote:
> On Sat, 17 May 2008, Robert Lor wrote:
> > I'd like to propose adding the following probes (some of which came from
> > Simon) to 8.4.
>
> There's also a big DTrace probe set patch available from OmniTI:
> https://labs.omniti.com/project-dtrace/trunk/postgresql/
> http://labs.omniti.com/trac/project-dtrace/wiki/Applications#PostgreSQL
>
> I don't know if you've looked at that before. There's some overlap but
> many unique and handy probes to each set. I think it would be nice to
> consider a superset union of the two. I would guess OmniTI would be glad
> to have their set assimilated into core as well so they don't have to
> maintain their patch past 8.3; hopefully Theo or Robert will chime in on
> that.
>

<chime>We've had it in the back of our minds to submit the patch set for 8.4,
just hadn't gotten around to it. As we've started to see some 3rd party
uptake of the set, it might be better to get something in sooner rather than
later, so yes, we'd be happy to see the patch set adopted upstream.</chime>

--
Robert Treat
Database Architect
http://www.omniti.com

On Saturday 17 May 2008 22:33:01 Robert Lor wrote:
> (Resending since it didn't work the first time. Not sure if attaching a
> tar file was the culprit.)
>
> I'd like to propose adding the following probes (some of which came from
> Simon) to 8.4.
>

+1

> I think these probe provide very useful data. Although some of the data
> can be collected now, the main advantages with probes, among others, are
> (1) they are always available and can be enabled only when needed
> especially in production (2) different combinations of probes can be
> used together to collect interesting data.
>
> They work on OS X Leopard & Solaris now, and hopefully on FreeBSD soon.
>

certainly by the time 8.4 ships, these should work with freebsd I'd think.
ideally we would need to confirm this by release time, certainly getting a
bsd buildfarm member to compile with them would be a start (and very unlikely
to cause issues)

> Preliminary patch attached along with sample DTrace scripts.
>

One thing I didnt understand after looking at this was...

> * Probes to measure query time
> query-parse-start (int, char *)

I would have guessed that the arguments might be pid and query string, but
looking at the probes, I see it defined as:

TRACE_POSTGRESQL_QUERY_PARSE_START(query_string);

which doesn't seem to match up... can you explain that piece?

Overall, I like the probes you have breaking down query
parsing/planning/executing, though I like ours for measuring autovacuum
pieces, so I think the end game should be to just merge the two patches
together (barring any place there is direct conflict)... do you see any
issues with that?

One other questions would be what to do with the dtrace scripts. I think
having a set of these available is a large boon for dtrace users, but do you
see that as something that needs to be distriubuted with the core? I'd lean
towards reviving the dtrace project on pgfoundry, but it might be worth
expanding the dynamic tracing chapter to include more examples and a pointer
to pgfoundry.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
>
> certainly by the time 8.4 ships, these should work with freebsd I'd think.
> ideally we would need to confirm this by release time, certainly getting a
> bsd buildfarm member to compile with them would be a start (and very unlikely
> to cause issues)
>
As soon as the DTrace port is working on FreeBSD, I will confirm that
the probes are working properly, and it's definitely a good idea to get
a buildfarm machine building with --enable-dtrace.
> One thing I didnt understand after looking at this was...
>
>> * Probes to measure query time
>> query-parse-start (int, char *)
>>
> I would have guessed that the arguments might be pid and query string, but
> looking at the probes, I see it defined as:
>
> TRACE_POSTGRESQL_QUERY_PARSE_START(query_string);
>
> which doesn't seem to match up... can you explain that piece?
>
Having the pid passed as an argument was my original intention, but it's
actually redundant since the pid is readily available from the script,
so I will fix the other probes with pid as args.
>
> Overall, I like the probes you have breaking down query
> parsing/planning/executing, though I like ours for measuring autovacuum
> pieces, so I think the end game should be to just merge the two patches
> together (barring any place there is direct conflict)... do you see any
> issues with that?
>
Yes, to avoid confusion, the probes should be merged and resubmitted as
one patch. Have yours been ported to 8.4 yet? We also need to make sure
the names and arg types are consistent, probably should work on this
offline.
> One other questions would be what to do with the dtrace scripts. I think
> having a set of these available is a large boon for dtrace users, but do you
> see that as something that needs to be distriubuted with the core?
I don't see the need to include the scripts with core now, maybe some
point in the future if it makes sense.
> I'd lean
> towards reviving the dtrace project on pgfoundry, but it might be worth
> expanding the dynamic tracing chapter to include more examples and a pointer
> to pgfoundry.
>
Agreed on both. I will add the new scripts to the dtrace project on
PgFoundry and add more info to the doc. I think you guys have some
interesting scripts as well that folks will find useful.

-Robert

Robert Lor <Robert(dot)Lor(at)Sun(dot)COM> writes:
> As soon as the DTrace port is working on FreeBSD, I will confirm that
> the probes are working properly, and it's definitely a good idea to get
> a buildfarm machine building with --enable-dtrace.

I'm pretty certain one of the OS X build critters is already testing
that. Of course it's only testing that it builds, not that it does
anything useful ...

regards, tom lane

On Friday 06 June 2008 14:32:27 Robert Lor wrote:
> Robert Treat wrote:
> > certainly by the time 8.4 ships, these should work with freebsd I'd
> > think. ideally we would need to confirm this by release time, certainly
> > getting a bsd buildfarm member to compile with them would be a start (and
> > very unlikely to cause issues)
>
> As soon as the DTrace port is working on FreeBSD, I will confirm that
> the probes are working properly, and it's definitely a good idea to get
> a buildfarm machine building with --enable-dtrace.
>
> > One thing I didnt understand after looking at this was...
> >
> >> * Probes to measure query time
> >> query-parse-start (int, char *)
> >
> > I would have guessed that the arguments might be pid and query string,
> > but looking at the probes, I see it defined as:
> >
> > TRACE_POSTGRESQL_QUERY_PARSE_START(query_string);
> >
> > which doesn't seem to match up... can you explain that piece?
>
> Having the pid passed as an argument was my original intention, but it's
> actually redundant since the pid is readily available from the script,
> so I will fix the other probes with pid as args.
>
> > Overall, I like the probes you have breaking down query
> > parsing/planning/executing, though I like ours for measuring autovacuum
> > pieces, so I think the end game should be to just merge the two patches
> > together (barring any place there is direct conflict)... do you see any
> > issues with that?
>
> Yes, to avoid confusion, the probes should be merged and resubmitted as
> one patch. Have yours been ported to 8.4 yet? We also need to make sure
> the names and arg types are consistent, probably should work on this
> offline.
>

We haven't ported our probes to 8.4 yet; the focus of our work has been to
help people currently running postgres in production, and will probably
remain with that focus untill closer to feature freeze. (Granted, if we get
something into 8.4, we may just overhaul our to match that.) While it would
be nice to have a clean merge of the two, it's probably simple enough to just
re-implement the differences into your patch (since yours already compiles on
8.4). As far as naming scheme, I'm not particularly wedded to either... is
there a dtrace naming convention that could be followed?

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
>
> While it would be nice to have a clean merge of the two, it's probably simple enough to just
> re-implement the differences into your patch (since yours already compiles on 8.4).
Should be straightforward ... I can do the merge.
> As far as naming scheme, I'm not particularly wedded to either... is
> there a dtrace naming convention that could be followed?
>
Yep, and the probes I submitted pretty much follow the suggested naming
convention.

-Robert