Re: Review: DTrace probes (merged version) ver_03

Theo Schlossnagle napsal(a):
>
> On Jul 24, 2008, at 11:11 AM, Zdenek Kotala wrote:
>
>> I performed review and I prepared own patch which contains only probes
>> without any issue. I suggest commit this patch because the rest of
>> patch is independent and it can be committed next commit fest after
>> rework.
>>
>> I found following issues:
>>
>> 1) SLRU probes.
>>
>> I think it is good to have probes there but they needs polish. See my
>> comments
>> http://reviewdemo.postgresql.org/r/25/
>
> The slru's are quite useful and general enough to use easily. I used
> them to verify the metered checkpointing stuff:
>
> http://lethargy.org/~jesus/archives/112-Probing-for-Success.html

I agree that SLRU probes are useful but I'm worry about implementation. I think
that these probes need more work before commit. Currently there are several bugs
in placement and arguments (from my point of view).

>> 3) Executor probes
>>
>> I would like to see any use case for them/
>
> I added them with two thoughts (and knowing that they cost nothing).
> (1) you can trace them to assist in debugging an explain plan and to
> better understand the flow of the execution engine. This is not a
> compelling reason, but a reason none-the-less.
> (2) you can trace and existing long-running query for which you do not
> have the original plan (may have changed) and make an educated guess at
> the plan chosen at time of execution.

I'm not executor expert and (1) is useful for me :-). What I'm thinking about is
if we can mine more information from executor like number of tuples processed by
node number and so on. I think that it needs discussion.

>> 8) mark dirty and BM_HINT... flag
>>
>> I remove these because I don't see any use case for it. It would be
>> nice provide some dtrace script or describe basic ideas.
>
>
> Perhaps I misunderstood what mark dirty does, but here was my thinking:
>
> Because of the background writer, it is difficult to understand which
> postgres process (and thus query) induced disk writes. Marking a page
> as dirty is a good indication that a query will be causing I/O and you
> can measure calls to mark dirty per query as a telling metric.
>
> Perhaps I misunderstood, but I have a very serious problem that I can't
> reliably track write I/O to postgresql process ID as the bgwriter and
> the kernel are flushing those dirty blocks to disk while the process
> isn't running. In my (albeit naive) tests, the mark dirty gave me quite
> expected results for correlating query execution to disk I/O to be induced.
>

If I understand correctly you need to analyze number of writes per
query/session. It seems to me, that to use mark dirty is good way, but it
probably needs more probes. (Robert L. any idea?)

However what I suggested is commit probes without issue now and the rest will be
processed on the next commit fest after rework/discussion.

Zdenek

--
Zdenek Kotala Sun Microsystems
Prague, Czech Republic http://sun.com/postgresql