Enclose new trace.sgml file as discussed on -docs.

This includes some heavily rewritten words and mostly verbatim programs
from http://blogs.sun.com/robertlor/entry/user_level_dtrace_probes_in,
as invited to do by Josh Berkus and Robert Lor on 27 Nov in private
correspondence prior to my post on -docs. There is substantial
additional material as well as genericisation so it is suitable for
inclusion in general docs.

This includes refactoring of some of the trace related GUC docs from
config.sgml, so its all in the one place.

SGML is clean apart from two xrefs that don't work properly:

openjade:config.sgml:4412:23:X: reference to non-existent ID
"TRACE-CONFIG-DEVELOPER"
openjade:wal.sgml:292:31:X: reference to non-existent ID "GUC-WAL-DEBUG"
openjade:/usr/share/sgml/docbook/dsssl-stylesheets/html/dblink.dsl:203:1:E: 
XRef LinkEnd to missing ID 'TRACE-CONFIG-DEVELOPER'
openjade:/usr/share/sgml/docbook/dsssl-stylesheets/html/dblink.dsl:203:1:E: 
XRef LinkEnd to missing ID 'GUC-WAL-DEBUG'

Quick fixes?

(I haven't proof-read the resulting .html as a result of the above
error, but seems like y'all would want to see this anyway).

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com

Title: Diagnostic Trace Facilities
/* $PostgreSQL:$ */ Various facilities are available for tracing internal database server activity. These are primarily intended for use by database developers to diagnose undesirable dynamic behaviour. Some of these facilities have been advocated for wider use, though such use is not yet common. You should start by referring to the production monitoring facilities in . Note that all of the features described here require special source compilation flags to work at all, so they may not be available to you if you are using installed binary programs. Developer Trace and Debug Parameters The following parameters are intended for work on the PostgreSQL source. There should be no reason to use them in a production database setup. As such, they have been excluded from the sample postgresql.conf file. debug_assertions (boolean) debug_assertions configuration parameter Turns on various assertion checks. This is a debugging aid. If you are experiencing strange problems or crashes you might want to turn this on, as it might expose programming mistakes. To use this parameter, the macro USE_ASSERT_CHECKING must be defined when PostgreSQL is built (accomplished by the configure option --enable-cassert). Note that debug_assertions defaults to on if PostgreSQL has been built with assertions enabled. post_auth_delay (integer) post_auth_delay configuration parameter If nonzero, a delay of this many seconds occurs when a new server process is started, after it conducts the authentication procedure. This is intended to give an opportunity to attach to the server process with a debugger. This parameter cannot be changed after session start. pre_auth_delay (integer) pre_auth_delay configuration parameter If nonzero, a delay of this many seconds occurs just after a new server process is forked, before it conducts the authentication procedure. This is intended to give an opportunity to attach to the server process with a debugger to trace down misbehavior in authentication. This parameter can only be set in the postgresql.conf file or on the server command line. trace_notify (boolean) trace_notify configuration parameter Generates a great amount of debugging output for the LISTEN and NOTIFY commands. or must be DEBUG1 or lower to send this output to the client or server log, respectively. trace_sort (boolean) trace_sort configuration parameter If on, emit information about resource usage during sort operations. This parameter is only available if the TRACE_SORT macro was defined when PostgreSQL was compiled. (However, TRACE_SORT is currently defined by default.) trace_locks (boolean) trace_lwlocks (boolean) trace_userlocks (boolean) trace_lock_oidmin (boolean) trace_lock_table (boolean) debug_deadlocks (boolean) log_btree_build_stats (boolean) Various other code tracing and debugging options. Only superusers can change these settings. wal_debug (boolean) wal_debug configuration parameter If on, emit WAL-related debugging output. This parameter is only available if the WAL_DEBUG macro was defined when PostgreSQL was compiled. Only superusers can change this setting. Dynamic Tracing PostgreSQL provides facilities to enable dynamic tracing utilities on the database server. This allows an external utility to be called at specific points in the code and thereby trace execution. A number of trace points, often called probes, are already inserted into the source code. By default PG_TRACE probes are disabled, and the user needs to explicitly tell the configure script to make the probes available in PostgreSQL. Currently, probes are only defined when using the DTrace utility, so enabling DTrace only makes sense on certain Operating Systems. Currently DTrace works with both Solaris Express and Solaris 10+, though the above probes do not yet work with Solaris 10+. It is expected that DTrace will be available in the future on FreeBSD and Mac OS X. Well-known Trace Points The following trace points are placed within the code to allow dynamic tracing of certain events related to locking and performance. These are: Trace Point Definition Overview transaction__start (int transactionId) The start of a new transaction. transaction__commit (int transactionId) The successful completion of a transaction. transaction__abort (int transactionId) The unsuccessful completion of a transaction. lwlock__acquire (int lockid, int mode) An shared resource has been locked. lwlock__release (int lockid, int mode) An shared resource has been released. lwlock__startwait (int lockid, int mode) An shared resource was not immediately available and a backend has begun to wait in a queue for the resource to become available. lwlock__endwait (int lockid, int mode) A backend has reached the head of the queue and the resource is now available. lwlock__condacquire (int lockid, int mode) A resource was requested only on condition that no wait was required. The resource has now been successfully locked. lwlock__condacquire__fail (int lockid, int mode) A resource was requested only on condition that no wait was required. The resource was busy, so has not been locked. lock__startwait (int locktag_field2, int lockmode) A request for a lock on user data has started to wait in a priority queue for access to that data. Meaning of the locktag varies according to the lock type requested. lock__endwait (int locktag_field2, int lockmode) A request for a lock on user data has now reached the head of the priority queue for access to that data. Meaning of the locktag varies according to the lock type requested. Compiling for Dynamic Trace By default, trace points are disabled, so you will need to explicitly tell the configure script to make the probes available in PostgreSQL. To include trace points in a 32 bit binary, specify --enable-dtrace to configure. For example: $ configure --enable-dtrace ... To include DTrace probes in a 64 bit binary, specify --enable-dtrace and DTRACEFLAGS="-64" to configure. For example: Using gcc compiler: $ configure CC='gcc -m64' --enable-dtrace DTRACEFLAGS='-64' ... Using Sun compiler: $ configure CC='/path_to_sun_compiler/cc -xtarget=native64' --enable-dtrace DTRACEFLAGS='-64' ... Using Trace Points Existing trace points can be utilised by the dynamic tracing utility. The example below shows a DTrace script for analyzing transaction counts on the system, as an alternative to snapshotting pg_stat_database before and after a performance test. #!/usr/sbin/dtrace -qs postgresql$1:::transaction-start { @start["Start"] = count(); self->ts = timestamp; } postgresql$1:::transaction-abort { @abort["Abort"] = count(); } postgresql$1:::transaction-commit /self->ts/ { @commit["Commit"] = count(); @commit["Abort"] = count(); @time["Total time (ns)"] = sum(timestamp - self->ts); self->ts=0; } Note how the double underline in trace point names needs to be replaced by a hyphen when using D script. When executed, the example D script gives the following output: # ./txn_count.d `pgrep -n postgres` ^C Start 71 Commit 70 Abort 1 Total time (ns) 2312105013 You should remember that trace programs need to be carefully written and debugged prior to their use, otherwise the trace information collected may be meaningless. In most cases where problems are found it is the instrumentation that is at fault, not the underlying system. When discussing information found using dynamic tracing, be sure to enclose the script used to allow that too to be checked and discussed. Defining Trace Points New trace points can be defined within the code wherever the developer desires, though this will require a re-compile. A trace point can be inserted in a single line by using one of the trace macros. These are chosen according to how many variables will be made available for inspection at that trace point. Tracing the occurrence of an event can be achieved with a single line, using just the trace point name, e.g. PG_TRACE (my__new__trace__point); More complex trace points can be provided with one or more variables for inspection by the dynamic tracing utility by using the PG_TRACE macro that corresponds to the number of parameters after the trace point name. PG_TRACE3 (my__complex__event, varX, varY, varZ); The definition of the transaction__start trace point is shown below: static void StartTransaction(void) { ... /* * generate a new transaction id */ s->transactionId = GetNewTransactionId(false); XactLockTableInsert(s->transactionId); PG_TRACE1(transaction__start, s->transactionId); ... } Note how the variable is made available to the dynamic tracing utility. The dynamic tracing utility may require you to further define these trace points. For example, DTrace requires you to add new probes to the file src/backend/utils/probes.d as shown here: provider postgresql { ... probe transaction__start(int); ... }; You should take care that the datatype of the probe arguments match the datatypes of the variables defined in the PG_TRACE macro. This is not checked at compile time of the source. You can check that your newly added trace point is available by recompiling, then run the new binary, and as root, execute a DTrace command such as: dtrace -l -n transaction-start Supporting new dynamic tracing utilities is possible by changing the definitions for the PG_TRACE macros in src/include/pg_trace.h
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.96
diff -c -r1.96 config.sgml
*** doc/src/sgml/config.sgml	21 Nov 2006 01:23:37 -0000	1.96
--- doc/src/sgml/config.sgml	1 Dec 2006 22:47:32 -0000
***************
*** 4400,4411 ****
  
      <para>
       The following parameters are intended for work on the
!      <productname>PostgreSQL</productname> source, and in some cases
       to assist with recovery of severely damaged databases.  There
       should be no reason to use them in a production database setup.
       As such, they have been excluded from the sample
!      <filename>postgresql.conf</> file.  Note that many of these
!      parameters require special source compilation flags to work at all.
      </para>
  
      <variablelist>
--- 4400,4415 ----
  
      <para>
       The following parameters are intended for work on the
!      <productname>PostgreSQL</productname> source specifically
       to assist with recovery of severely damaged databases.  There
       should be no reason to use them in a production database setup.
       As such, they have been excluded from the sample
!      <filename>postgresql.conf</> file. 
!     </para>
! 
!     <para>
!      Additional parameters used for tracing server behaviour are discussed
!      in the trace chapter.
      </para>
  
      <variablelist>
***************
*** 4423,4449 ****
        </listitem>
       </varlistentry>
  
-      <varlistentry id="guc-debug-assertions" xreflabel="debug_assertions">
-       <term><varname>debug_assertions</varname> (<type>boolean</type>)</term>
-       <indexterm>
-        <primary><varname>debug_assertions</> configuration parameter</primary>
-       </indexterm>
-       <listitem>
-        <para>
-         Turns on various assertion checks. This is a debugging aid. If
-         you are experiencing strange problems or crashes you might want
-         to turn this on, as it might expose programming mistakes. To use
-         this parameter, the macro <symbol>USE_ASSERT_CHECKING</symbol>
-         must be defined when <productname>PostgreSQL</productname> is
-         built (accomplished by the <command>configure</command> option
-         <option>--enable-cassert</option>). Note that
-         <varname>debug_assertions</varname> defaults to <literal>on</>
-         if <productname>PostgreSQL</productname> has been built with
-         assertions enabled.
-        </para>
-       </listitem>
-      </varlistentry>
- 
       <varlistentry id="guc-ignore-system-indexes" xreflabel="ignore_system_indexes">
        <term><varname>ignore_system_indexes</varname> (<type>boolean</type>)</term>
        <indexterm>
--- 4427,4432 ----
***************
*** 4459,4560 ****
        </listitem>
       </varlistentry>
  
-      <varlistentry id="guc-post-auth-delay" xreflabel="post_auth_delay">
-       <term><varname>post_auth_delay</varname> (<type>integer</type>)</term>
-       <indexterm>
-        <primary><varname>post_auth_delay</> configuration parameter</primary>
-       </indexterm>
-       <listitem>
-        <para>
-         If nonzero, a delay of this many seconds occurs when a new
-         server process is started, after it conducts the
-         authentication procedure.  This is intended to give an
-         opportunity to attach to the server process with a debugger.
-         This parameter cannot be changed after session start.
-        </para>
-       </listitem>
-      </varlistentry>
- 
-      <varlistentry id="guc-pre-auth-delay" xreflabel="pre_auth_delay">
-       <term><varname>pre_auth_delay</varname> (<type>integer</type>)</term>
-       <indexterm>
-        <primary><varname>pre_auth_delay</> configuration parameter</primary>
-       </indexterm>
-       <listitem>
-        <para>
-         If nonzero, a delay of this many seconds occurs just after a
-         new server process is forked, before it conducts the
-         authentication procedure.  This is intended to give an
-         opportunity to attach to the server process with a debugger to
-         trace down misbehavior in authentication.
-         This parameter can only be set in the <filename>postgresql.conf</>
-         file or on the server command line.
-        </para>
-       </listitem>
-      </varlistentry>
- 
-      <varlistentry id="guc-trace-notify" xreflabel="trace_notify">
-       <term><varname>trace_notify</varname> (<type>boolean</type>)</term>
-       <indexterm>
-        <primary><varname>trace_notify</> configuration parameter</primary>
-       </indexterm>
-       <listitem>
-        <para>
-         Generates a great amount of debugging output for the
-         <command>LISTEN</command> and <command>NOTIFY</command>
-         commands.  <xref linkend="guc-client-min-messages"> or
-         <xref linkend="guc-log-min-messages"> must be
-         <literal>DEBUG1</literal> or lower to send this output to the
-         client or server log, respectively.
-        </para>
-       </listitem>
-      </varlistentry>
- 
-      <varlistentry id="guc-trace-sort" xreflabel="trace_sort">
-       <term><varname>trace_sort</varname> (<type>boolean</type>)</term>
-       <indexterm>
-        <primary><varname>trace_sort</> configuration parameter</primary>
-       </indexterm>
-       <listitem>
-        <para>
-         If on, emit information about resource usage during sort operations.
-         This parameter is only available if the <symbol>TRACE_SORT</symbol> macro
-         was defined when <productname>PostgreSQL</productname> was compiled.
-         (However, <symbol>TRACE_SORT</symbol> is currently defined by default.)
-        </para>
-       </listitem>
-      </varlistentry>
- 
-      <varlistentry>
-       <term><varname>trace_locks</varname> (<type>boolean</type>)</term>
-       <term><varname>trace_lwlocks</varname> (<type>boolean</type>)</term>
-       <term><varname>trace_userlocks</varname> (<type>boolean</type>)</term>
-       <term><varname>trace_lock_oidmin</varname> (<type>boolean</type>)</term>
-       <term><varname>trace_lock_table</varname> (<type>boolean</type>)</term>
-       <term><varname>debug_deadlocks</varname> (<type>boolean</type>)</term>
-       <term><varname>log_btree_build_stats</varname> (<type>boolean</type>)</term>
-       <listitem>
-        <para>
-         Various other code tracing and debugging options.
-        </para>
-       </listitem>
-      </varlistentry>
- 
-      <varlistentry id="guc-wal-debug" xreflabel="wal_debug">
-       <term><varname>wal_debug</varname> (<type>boolean</type>)</term>
-       <indexterm>
-        <primary><varname>wal_debug</> configuration parameter</primary>
-       </indexterm>
-       <listitem>
-        <para>
-         If on, emit WAL-related debugging output. This parameter is
-         only available if the <symbol>WAL_DEBUG</symbol> macro was
-         defined when <productname>PostgreSQL</productname> was
-         compiled.
-        </para>
-       </listitem>
-      </varlistentry>
- 
      <varlistentry id="guc-zero-damaged-pages" xreflabel="zero_damaged_pages">
        <term><varname>zero_damaged_pages</varname> (<type>boolean</type>)</term>
        <indexterm>
--- 4442,4447 ----
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to