codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>pg_test_timing</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="pgtestfsync.html" title="pg_test_fsync" /><link rel="next" href="pgupgrade.html" title="pg_upgrade" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center"><span class="application">pg_test_timing</span></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pgtestfsync.html" title="pg_test_fsync">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="reference-server.html" title="PostgreSQL Server Applications">Up</a></td><th width="60%" align="center">PostgreSQL Server Applications</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pgupgrade.html" title="pg_upgrade">Next</a></td></tr></table><hr /></div><div class="refentry" id="PGTESTTIMING"><div class="titlepage"></div><a id="id-1.9.5.11.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle"><span class="application">pg_test_timing</span></span></h2><p>pg_test_timing — measure timing overhead</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><div class="cmdsynopsis"><p id="id-1.9.5.11.4.1"><code class="command">pg_test_timing</code> [<em class="replaceable"><code>option</code></em>...]</p></div></div><div class="refsect1" id="id-1.9.5.11.5"><h2>Description</h2><p>3  <span class="application">pg_test_timing</span> is a tool to measure the timing overhead4  on your system and confirm that the system time never moves backwards.5  Systems that are slow to collect timing data can give less accurate6  <code class="command">EXPLAIN ANALYZE</code> results.7 </p></div><div class="refsect1" id="id-1.9.5.11.6"><h2>Options</h2><p>8    <span class="application">pg_test_timing</span> accepts the following9    command-line options:10 11    </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-d <em class="replaceable"><code>duration</code></em></code><br /></span><span class="term"><code class="option">--duration=<em class="replaceable"><code>duration</code></em></code></span></dt><dd><p>12        Specifies the test duration, in seconds. Longer durations13        give slightly better accuracy, and are more likely to discover14        problems with the system clock moving backwards. The default15        test duration is 3 seconds.16       </p></dd><dt><span class="term"><code class="option">-V</code><br /></span><span class="term"><code class="option">--version</code></span></dt><dd><p>17        Print the <span class="application">pg_test_timing</span> version and exit.18       </p></dd><dt><span class="term"><code class="option">-?</code><br /></span><span class="term"><code class="option">--help</code></span></dt><dd><p>19        Show help about <span class="application">pg_test_timing</span> command line20        arguments, and exit.21       </p></dd></dl></div><p>22   </p></div><div class="refsect1" id="id-1.9.5.11.7"><h2>Usage</h2><div class="refsect2" id="id-1.9.5.11.7.2"><h3>Interpreting Results</h3><p>23   Good results will show most (&gt;90%) individual timing calls take less than24   one microsecond. Average per loop overhead will be even lower, below 10025   nanoseconds. This example from an Intel i7-860 system using a TSC clock26   source shows excellent performance:27 28</p><pre class="screen">29Testing timing overhead for 3 seconds.30Per loop time including overhead: 35.96 ns31Histogram of timing durations:32  &lt; us   % of total      count33     1     96.40465   8043560434     2      3.59518    299965235     4      0.00015        12636     8      0.00002         1337    16      0.00000          238</pre><p>39  </p><p>40   Note that different units are used for the per loop time than the41   histogram. The loop can have resolution within a few nanoseconds (ns),42   while the individual timing calls can only resolve down to one microsecond43   (us).44  </p></div><div class="refsect2" id="id-1.9.5.11.7.3"><h3>Measuring Executor Timing Overhead</h3><p>45   When the query executor is running a statement using46   <code class="command">EXPLAIN ANALYZE</code>, individual operations are timed as well47   as showing a summary.  The overhead of your system can be checked by48   counting rows with the <span class="application">psql</span> program:49 50</p><pre class="screen">51CREATE TABLE t AS SELECT * FROM generate_series(1,100000);52\timing53SELECT COUNT(*) FROM t;54EXPLAIN ANALYZE SELECT COUNT(*) FROM t;55</pre><p>56  </p><p>57   The i7-860 system measured runs the count query in 9.8 ms while58   the <code class="command">EXPLAIN ANALYZE</code> version takes 16.6 ms, each59   processing just over 100,000 rows.  That 6.8 ms difference means the timing60   overhead per row is 68 ns, about twice what pg_test_timing estimated it61   would be.  Even that relatively small amount of overhead is making the fully62   timed count statement take almost 70% longer.  On more substantial queries,63   the timing overhead would be less problematic.64  </p></div><div class="refsect2" id="id-1.9.5.11.7.4"><h3>Changing Time Sources</h3><p>65   On some newer Linux systems, it's possible to change the clock source used66   to collect timing data at any time.  A second example shows the slowdown67   possible from switching to the slower acpi_pm time source, on the same68   system used for the fast results above:69 70</p><pre class="screen">71# cat /sys/devices/system/clocksource/clocksource0/available_clocksource72tsc hpet acpi_pm73# echo acpi_pm &gt; /sys/devices/system/clocksource/clocksource0/current_clocksource74# pg_test_timing75Per loop time including overhead: 722.92 ns76Histogram of timing durations:77  &lt; us   % of total      count78     1     27.84870    115568279     2     72.05956    299037180     4      0.07810       324181     8      0.01357        56382    16      0.00007          383</pre><p>84  </p><p>85   In this configuration, the sample <code class="command">EXPLAIN ANALYZE</code> above86   takes 115.9 ms.  That's 1061 ns of timing overhead, again a small multiple87   of what's measured directly by this utility.  That much timing overhead88   means the actual query itself is only taking a tiny fraction of the89   accounted for time, most of it is being consumed in overhead instead.  In90   this configuration, any <code class="command">EXPLAIN ANALYZE</code> totals involving91   many timed operations would be inflated significantly by timing overhead.92  </p><p>93   FreeBSD also allows changing the time source on the fly, and it logs94   information about the timer selected during boot:95 96</p><pre class="screen">97# dmesg | grep "Timecounter"98Timecounter "ACPI-fast" frequency 3579545 Hz quality 90099Timecounter "i8254" frequency 1193182 Hz quality 0100Timecounters tick every 10.000 msec101Timecounter "TSC" frequency 2531787134 Hz quality 800102# sysctl kern.timecounter.hardware=TSC103kern.timecounter.hardware: ACPI-fast -&gt; TSC104</pre><p>105  </p><p>106   Other systems may only allow setting the time source on boot.  On older107   Linux systems the "clock" kernel setting is the only way to make this sort108   of change.  And even on some more recent ones, the only option you'll see109   for a clock source is "jiffies".  Jiffies are the older Linux software clock110   implementation, which can have good resolution when it's backed by fast111   enough timing hardware, as in this example:112 113</p><pre class="screen">114$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource115jiffies116$ dmesg | grep time.c117time.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer.118time.c: Detected 2400.153 MHz processor.119$ pg_test_timing120Testing timing overhead for 3 seconds.121Per timing duration including loop overhead: 97.75 ns122Histogram of timing durations:123  &lt; us   % of total      count124     1     90.23734   27694571125     2      9.75277    2993204126     4      0.00981       3010127     8      0.00007         22128    16      0.00000          1129    32      0.00000          1130</pre></div><div class="refsect2" id="id-1.9.5.11.7.5"><h3>Clock Hardware and Timing Accuracy</h3><p>131   Collecting accurate timing information is normally done on computers using132   hardware clocks with various levels of accuracy.  With some hardware the133   operating systems can pass the system clock time almost directly to134   programs.  A system clock can also be derived from a chip that simply135   provides timing interrupts, periodic ticks at some known time interval.  In136   either case, operating system kernels provide a clock source that hides137   these details.  But the accuracy of that clock source and how quickly it can138   return results varies based on the underlying hardware.139  </p><p>140   Inaccurate time keeping can result in system instability.  Test any change141   to the clock source very carefully.  Operating system defaults are sometimes142   made to favor reliability over best accuracy. And if you are using a virtual143   machine, look into the recommended time sources compatible with it.  Virtual144   hardware faces additional difficulties when emulating timers, and there are145   often per operating system settings suggested by vendors.146  </p><p>147   The Time Stamp Counter (TSC) clock source is the most accurate one available148   on current generation CPUs. It's the preferred way to track the system time149   when it's supported by the operating system and the TSC clock is150   reliable. There are several ways that TSC can fail to provide an accurate151   timing source, making it unreliable. Older systems can have a TSC clock that152   varies based on the CPU temperature, making it unusable for timing. Trying153   to use TSC on some older multicore CPUs can give a reported time that's154   inconsistent among multiple cores. This can result in the time going155   backwards, a problem this program checks for.  And even the newest systems156   can fail to provide accurate TSC timing with very aggressive power saving157   configurations.158  </p><p>159   Newer operating systems may check for the known TSC problems and switch to a160   slower, more stable clock source when they are seen.  If your system161   supports TSC time but doesn't default to that, it may be disabled for a good162   reason.  And some operating systems may not detect all the possible problems163   correctly, or will allow using TSC even in situations where it's known to be164   inaccurate.165  </p><p>166   The High Precision Event Timer (HPET) is the preferred timer on systems167   where it's available and TSC is not accurate.  The timer chip itself is168   programmable to allow up to 100 nanosecond resolution, but you may not see169   that much accuracy in your system clock.170  </p><p>171   Advanced Configuration and Power Interface (ACPI) provides a Power172   Management (PM) Timer, which Linux refers to as the acpi_pm.  The clock173   derived from acpi_pm will at best provide 300 nanosecond resolution.174  </p><p>175   Timers used on older PC hardware include the 8254 Programmable Interval176   Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt177   Controller (APIC) timer, and the Cyclone timer.  These timers aim for178   millisecond resolution.179  </p></div></div><div class="refsect1" id="id-1.9.5.11.8"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-explain.html" title="EXPLAIN"><span class="refentrytitle">EXPLAIN</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pgtestfsync.html" title="pg_test_fsync">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="reference-server.html" title="PostgreSQL Server Applications">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="pgupgrade.html" title="pg_upgrade">Next</a></td></tr><tr><td width="40%" align="left" valign="top"><span class="application">pg_test_fsync</span> </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> <span class="application">pg_upgrade</span></td></tr></table></div></body></html>