monitoring.sgml

PostgreSQL7.4.6 for Linux

     <row>
      <entry><structname>pg_statio_user_sequences</></entry>
      <entry>Same as <structname>pg_statio_all_sequences</>, except that only user
      sequences are shown.</entry>
     </row>
    </tbody>
   </tgroup>
  </table>

  <para>
   The per-index statistics are particularly useful to determine which
   indexes are being used and how effective they are.
  </para>

  <para>
   The <structname>pg_statio_</> views are primarily useful to
   determine the effectiveness of the buffer cache.  When the number
   of actual disk reads is much smaller than the number of buffer
   hits, then the cache is satisfying most read requests without
   invoking a kernel call. However, these statistics do not give the
   entire story: due to the way in which <productname>PostgreSQL</>
   handles disk I/O, data that is not in the
   <productname>PostgreSQL</> buffer cache may still reside in the
   kernel's I/O cache, and may therefore still be fetched without
   requiring a physical read. Users interested in obtaining more
   detailed information on <productname>PostgreSQL</> I/O behavior are
   advised to use the <productname>PostgreSQL</> statistics collector
   in combination with operating system utilities that allow insight
   into the kernel's handling of I/O.
  </para>

  <para>
   Other ways of looking at the statistics can be set up by writing
   queries that use the same underlying statistics access functions as
   these standard views do.  These functions are listed in <xref
   linkend="monitoring-stats-funcs-table">.  The per-database access
   functions take a database OID as argument to identify which
   database to report on.  The per-table and per-index functions take
   a table or index OID.  (Note that only tables and indexes in the
   current database can be seen with these functions.)  The
   per-backend process access functions take a backend process ID
   number, which ranges from one to the number of currently active
   backend processes.
  </para>

  <table id="monitoring-stats-funcs-table">
   <title>Statistics Access Functions</title>

   <tgroup cols="3">
    <thead>
     <row>
      <entry>Function</entry>
      <entry>Return Type</entry>
      <entry>Description</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><literal><function>pg_stat_get_db_numbackends</function>(<type>oid</type>)</literal></entry>
      <entry><type>integer</type></entry>
      <entry>
       Number of active backend processes for database
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_db_xact_commit</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Transactions committed in database
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_db_xact_rollback</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Transactions rolled back in database
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_db_blocks_fetched</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Number of disk block fetch requests for database
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_db_blocks_hit</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Number of disk block fetch requests found in cache for database
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Number of sequential scans done when argument is a table,
       or number of index scans done when argument is an index
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_tuples_returned</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Number of rows read by sequential scans when argument is a table,
       or number of index rows read when argument is an index
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_tuples_fetched</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Number of valid (unexpired) table rows fetched by sequential scans
       when argument is a table, or fetched by index scans using this index
       when argument is an index
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_tuples_inserted</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Number of rows inserted into table
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_tuples_updated</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Number of rows updated in table
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_tuples_deleted</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Number of rows deleted from table
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_blocks_fetched</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Number of disk block fetch requests for table or index
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_blocks_hit</function>(<type>oid</type>)</literal></entry>
      <entry><type>bigint</type></entry>
      <entry>
       Number of disk block requests found in cache for table or index
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_backend_idset</function>()</literal></entry>
      <entry><type>set of integer</type></entry>
      <entry>
       Set of currently active backend process IDs (from 1 to the
       number of active backend processes).  See usage example in the text.
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_backend_pid</function>()</literal></entry>
      <entry><type>integer</type></entry>
      <entry>
       Process ID of the backend process attached to the current session
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_backend_pid</function>(<type>integer</type>)</literal></entry>
      <entry><type>integer</type></entry>
      <entry>
       Process ID of the given backend process
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_backend_dbid</function>(<type>integer</type>)</literal></entry>
      <entry><type>oid</type></entry>
      <entry>
       Database ID of the given backend process
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_backend_userid</function>(<type>integer</type>)</literal></entry>
      <entry><type>oid</type></entry>
      <entry>
       User ID of the given backend process
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_backend_activity</function>(<type>integer</type>)</literal></entry>
      <entry><type>text</type></entry>
      <entry>
       Active command of the given backend process (null if the
       current user is not a superuser nor the same user as that of
       the session being queried, or
       <varname>stats_command_string</varname> is not on)
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_get_backend_activity_start</function>(<type>integer</type>)</literal></entry>
      <entry><type>timestamp with time zone</type></entry>
      <entry>
       The time at which the given backend process' currently
       executing query was started (null if the
       current user is not a superuser nor the same user as that of
       the session being queried, or
       <varname>stats_command_string</varname> is not on)
      </entry>
     </row>

     <row>
      <entry><literal><function>pg_stat_reset</function>()</literal></entry>
      <entry><type>boolean</type></entry>
      <entry>
       Reset all currently collected statistics
      </entry>
     </row>
    </tbody>
   </tgroup>
  </table>

   <note>
    <para>
     <function>pg_stat_get_db_blocks_fetched</function> minus
     <function>pg_stat_get_db_blocks_hit</function> gives the number of kernel
     <function>read()</> calls issued for the table, index, or
     database; but the actual number of physical reads is usually
     lower due to kernel-level buffering.
    </para>
   </note>

  <para>
   The function <function>pg_stat_get_backend_idset</function> provides
   a convenient way to generate one row for each active backend process.  For
   example, to show the <acronym>PID</>s and current queries of all backend processes:

<programlisting>
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
       pg_stat_get_backend_activity(s.backendid) AS current_query
    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
</programlisting>
  </para>

 </sect2>
 </sect1>

 <sect1 id="monitoring-locks">
  <title>Viewing Locks</title>

  <indexterm zone="monitoring-locks">
   <primary>lock</primary>
   <secondary>monitoring</secondary>
  </indexterm>

  <para>
   Another useful tool for monitoring database activity is the
   <structname>pg_locks</structname> system table.  It allows the
   database administrator to view information about the outstanding
   locks in the lock manager. For example, this capability can be used
   to:
	
   <itemizedlist>
    <listitem>
     <para>
      View all the locks currently outstanding, all the locks on
      relations in a particular database, all the locks on a
      particular relation, or all the locks held by a particular
      <productname>PostgreSQL</productname> session.
     </para>
    </listitem>

    <listitem>
     <para>
      Determine the relation in the current database with the most
      ungranted locks (which might be a source of contention among
      database clients).
     </para>
    </listitem>

    <listitem>
     <para>
      Determine the effect of lock contention on overall database
      performance, as well as the extent to which contention varies
      with overall database traffic.
     </para>
    </listitem>
   </itemizedlist>

   Details of the <structname>pg_locks</structname> view appear in
   <xref linkend="view-pg-locks">.
   For more information on locking and managing concurrency with
   <productname>PostgreSQL</productname>, refer to <xref linkend="mvcc">.
  </para>
 </sect1>
</chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->