>-----Original Message-----
>Sent: Friday, December 10, 2021 9:20 AM
>To: Jeff Janes <jeff.ja...@gmail.com>
>Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
>Subject: [EXTERNAL] Re: track_io_timing default setting
>Jeff Janes <jeff.ja...@gmail.com> writes:
> Can we change the default setting of track_io_timing to on?
>That adds a very significant amount of overhead on some platforms
>(gettimeofday is not cheap if it requires a kernel call). And I doubt the
>claim that the average Postgres user needs this, and doubt even more >that
>they need it on all the time.
>So I'm -1 on the idea.
regards, tom lane
>
In all honesty, the term "significant amount of overhead on some platforms" is
ambiguous. Exactly how much overhead and on what platforms??? I would prefer
the document to say something on the order of:
"Enables timing of database I/O calls. This parameter is historically
off by default, because it will repeatedly query the operating system for the
current time, which may increase overhead costs of elapsed time for
each IO. Platforms known to incur a problematic overhead are, <etc, etc, etc>.
To measure the overhead of timing on your system, use the pg_test_timing tool.
This overhead may become a performance issue when less than 90% of the
tests execute for more than 1 microsecond (us). Please refer to the
pg_test_timing tool page for more details"
I have the timing always turned on, but that doesn't necessarily mean the
default should be changed. However the documentation should be changed as the
current phrasing would probably discourage some folks from even trying. I ran
the pg_test_timing tool and it came out to .000000023 seconds overhead. Since
we typically measure IO in terms of milliseconds, this number is statistically
insignificant.
As long as we're on the topic, the documentation for the pg_test_timing tool as
well as the output of the tool have something to be desired. The tool output
looks like this:
Testing timing overhead for 3 seconds.
Per loop time including overhead: 23.02 ns
Histogram of timing durations:
< us % of total count
1 97.70191 127332403
2 2.29729 2993997
4 0.00007 90
8 0.00069 904
16 0.00004 57
Take note of the comment: "Per loop time including overhead" - so does that
means the overhead IS LESS than the reported 23.02 ns? Is that an issue with
the actual test code or the output prose? Furthermore the tool's doc goes on to
things like this:
"The i7-860 system measured runs the count query in 9.8 ms while the
EXPLAIN ANALYZE version takes 16.6 ms, each processing just over 100,000 rows.
That 6.8 ms difference means the timing overhead per row is 68 ns, about
twice what pg_test_timing estimated it would be. Even that relatively small
amount of overhead is making the fully timed count statement take almost 70%
longer. On more substantial queries, the timing overhead would be less
problematic."
IMHO this is misleading. This timing process is what EXPLAIN ANALYZE does and
most likely completely unrelated to the topic in question - that is turning on
io timing! What this paragraph is implying through the reader's chain of events
is that IF you turn on track_io_timing you may result in a 70% overhead!!! Umm
- really???
Long story short, I'm perfectly fine with this 'overhead' - unless someone
wants to refute this.
Regards,
phil
