A database administrator frequently wonders "what is the system doing right now?" This chapter discusses tool sand methods to determine database activity.
Several tools are available for monitoring database activity and analyzing performance. Most of this chapter is devoted to describing PostgreSQL's statistics collector, but one should not neglect regular Unix monitoring programs such as ps and top. Also, once one has identified a poorly-performing query, further investigation may be needed using PostgreSQL's EXPLAIN command.
PostgreSQL modifies its command title as reported by ps, so that individual server processes can readily be identified. The key elements of a sample display are:
$ ps auxw | grep ^postgres postgres 960 ... postmaster -i postgres 963 ... postgres: stats buffer process postgres 965 ... postgres: stats collector process postgres 998 ... postgres: tgl runbug 127.0.0.1 idle postgres 1003 ... postgres: tgl regression [local] SELECT waiting postgres 1016 ... postgres: tgl regression [local] idle in transaction |
postgres: user database host activity |