Hi,
I know I'm late for the ongoing commitfest, but I thought I might as well
be early for the next one.
Attached is a patch against master that implements timing of shared buffer
fills and per relation stats collection of said timings. Buffer flushes are
timed as well but aren't exposed per table because of difficulty of
correctly attributing them.
Some notes on the implementation:
* The timing is done in bufmgr.c. Under high load some CPU contention will
get attributed to IO because the process doing the I/O won't get a time-
slice immediately.
* I decided to also account waiting for others doing the I/O as I/O waits.
They aren't double counted for per relation stats though.
* I added a GUC track_iotiming defaulting to off because timing isn't cheap
on all platforms.
* I used instr_time to keep the counts to be consistent with function
timings, but maybe both should convert to plain uint64 counts to make the
arithmetic code cleaner.
* Timings are exposed via EXPLAIN (BUFFERS), pg_stat_statements and
pg_statio_* views.
* I noticed there aren't any pg_statio_xact_* views. I don't have any need
for them myself, but thought I'd mention the inconsistency.
* The feature is really useful for me with auto_explain. Even better with
Peter's pg_stat_statements query cleaning applied.
I did some testing on an older AMD Athlon X2 BE-2350 and an Intel i5 M 540
to see the overhead. The AMD CPU doesn't have the necessary features for
fast user mode timing and has an overhead of about 900ns per gettimeofday
call. The Intel processor has an overhead of 22ns per call.
I tried a read only pgbench with scalefactor 50 and shared_buffers=32MB to
induce a lot of IO traffic that hits the OS cache. Seems like it should be
the worst case for this patch.
On the AMD I saw about 3% performance drop with timing enabled. On the
Intel machine I couldn't measure any statistically significant change. The
median was actually higher with timing enabled, but stddevs were large
enough to hide a couple of percent of performance loss. This needs some
further testing.
Preliminary results for the Intel machine with stddev (10 5min runs):
-c | master | io-stats
4 | 16521.53 ±4.49% | +1.16% ±3.21%
16 | 13923.49 ±5.98% | +0.56% ±7.11%
This is my first patch, so I hope I haven't missed anything too trivial.
--
Ants Aasma
ants.aasma@eesti.ee