<!-- doc/src/sgml/monitoring.sgml -->

<chapter id="monitoring">
 <title>Monitoring Database Activity</title>

 <indexterm zone="monitoring">
  <primary>monitoring</primary>
  <secondary>database activity</secondary>
 </indexterm>

 <indexterm zone="monitoring">
  <primary>database activity</primary>
  <secondary>monitoring</secondary>
 </indexterm>

 <para>
  A database administrator frequently wonders, <quote>What is the system
  doing right now?</quote>
  This chapter discusses how to find that out.
 </para>

  <para>
   Several tools are available for monitoring database activity and
   analyzing performance.  Most of this chapter is devoted to describing
   <productname>PostgreSQL</productname>'s cumulative statistics system,
   but one should not neglect regular Unix monitoring programs such as
   <command>ps</command>, <command>top</command>, <command>iostat</command>, and <command>vmstat</command>.
   Also, once one has identified a
   poorly-performing query, further investigation might be needed using
   <productname>PostgreSQL</productname>'s <link linkend="sql-explain"><command>EXPLAIN</command></link> command.
   <xref linkend="using-explain"/> discusses <command>EXPLAIN</command>
   and other methods for understanding the behavior of an individual
   query.
  </para>

 <sect1 id="monitoring-ps">
  <title>Standard Unix Tools</title>

  <indexterm zone="monitoring-ps">
   <primary>ps</primary>
   <secondary>to monitor activity</secondary>
  </indexterm>

  <para>
   On most Unix platforms, <productname>PostgreSQL</productname> modifies its
   command title as reported by <command>ps</command>, so that individual server
   processes can readily be identified.  A sample display is

<screen>
$ ps auxww | grep ^postgres
postgres  15551  0.0  0.1  57536  7132 pts/0    S    18:02   0:00 postgres -i
postgres  15554  0.0  0.0  57536  1184 ?        Ss   18:02   0:00 postgres: background writer
postgres  15555  0.0  0.0  57536   916 ?        Ss   18:02   0:00 postgres: checkpointer
postgres  15556  0.0  0.0  57536   916 ?        Ss   18:02   0:00 postgres: walwriter
postgres  15557  0.0  0.0  58504  2244 ?        Ss   18:02   0:00 postgres: autovacuum launcher
postgres  15582  0.0  0.0  58772  3080 ?        Ss   18:04   0:00 postgres: joe runbug 127.0.0.1 idle
postgres  15606  0.0  0.0  58772  3052 ?        Ss   18:07   0:00 postgres: tgl regression [local] SELECT waiting
postgres  15610  0.0  0.0  58772  3056 ?        Ss   18:07   0:00 postgres: tgl regression [local] idle in transaction
</screen>

   (The appropriate invocation of <command>ps</command> varies across different
   platforms, as do the details of what is shown.  This example is from a
   recent Linux system.)  The first process listed here is the
   primary server process.  The command arguments
   shown for it are the same ones used when it was launched.  The next four
   processes are background worker processes automatically launched by the
   primary process.  (The <quote>autovacuum launcher</quote> process will not
   be present if you have set the system not to run autovacuum.)
   Each of the remaining
   processes is a server process handling one client connection.  Each such
   process sets its command line display in the form

<screen>
postgres: <replaceable>user</replaceable> <replaceable>database</replaceable> <replaceable>host</replaceable> <replaceable>activity</replaceable>
</screen>

  The user, database, and (client) host items remain the same for
  the life of the client connection, but the activity indicator changes.
  The activity can be <literal>idle</literal> (i.e., waiting for a client command),
  <literal>idle in transaction</literal> (waiting for client inside a <command>BEGIN</command> block),
  or a command type name such as <literal>SELECT</literal>.  Also,
  <literal>waiting</literal> is appended if the server process is presently waiting
  on a lock held by another session.  In the above example we can infer
  that process 15606 is