Red Hat Database: Administrator and User's Guide
PrevNext

Chapter 13. Monitoring Database Activity

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.

Standard Linux Tools

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
The first process listed here is the postmaster, the master server process. The command arguments shown for it are the same ones given when it was launched. The next two processes implement the statistics collector, which will be described in detail in the next section. (These will not be present if you have set the system not to start the statistics collector.) Each of the remaining processes is a server process handling one client connection. Each such process sets its command line display in the form 
postgres: user database host activity
The user, database, and connection source host items remain the same for the life of the client connection, but the activity indicator changes. The activity may be idle (that is, waiting for a client command), idle in transaction (waiting for client inside a BEGIN block), or a command type name such as SELECT. Also, waiting is attached if the server is presently waiting on a lock held by another server process. In the above example we can infer that process 1003 is waiting for process 1016 to complete its transaction and thereby release some lock or other.

PrevHomeNext
Genetic Query Optimization (GEQO) in PostgreSQL Statistics Collector