Re: Review: DTrace probes (merged version) ver_03

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

> 2) XLOG probes
>
> I think there is confuse placement of probes after merge. It needs
> cleanup.
>
>
> 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.

> 4) smgr probes
>
> I prefer to have this probes in smgr instead of md. The reason why
> Robert put them into md is that it returns number of written/read
> bytes, but it is "always" BLCKSZ which could be returned from smgr
> directly. Only difference is
> when error occurs during write/read and not all data are written/read.
>
> It needs discuss.
>
> 5) autovacuum start probes
>
> I would like to see also stat/stop for any other process types. It
> was discussed but no comment from author(s).
>
> 6) idle transaction
>
> See my comments
> http://reviewdemo.postgresql.org/r/25/
>
> 7) query-reewrite is missing
>
>
> 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.

--
Theo Schlossnagle
Esoteric Curio -- http://lethargy.org/
OmniTI Computer Consulting, Inc. -- http://omniti.com/