pg_top may be able to help when you have poor AMP performance and I/O has been ruled out (and by I/O, I mean writes from (AW)RRDTool) and the Postgresql backends seem to be chewing up abnormally large amounts of CPU.
You run it like this:
# pg_top –Uairwave
Once started, it will look and feel a little bit like “top” but possibly more sluggish. You can hit “h” to get a list of commands you can run. Here are the ones I have found particularly useful:
• L (show locks held by a process)
• R (show user-space table statistics; see below for more details)
• Q (show current query of a process)
• s (change the frequency of update)
The output after pressing “R” can tell you when a particular table is getting hit by sequential scans (and sequential data reads). This is generally bad and we try to avoid it w/indexes. For example, the rap_match table seems to be a likely culprit of problems at UWash as evidenced by large spikes of sequential scans/reads on the rap_match table:
last pid: 18621; load avg: 8.38, 6.66, 6.28; up 74+20:57:55 16:08:22
45 processes: 7 running, 38 sleeping
CPU states: 87.0% user, 0.0% nice, 0.8% system, 12.2% idle, 0.0% iowait
Memory: 16G used, 321M free, 181M buffers, 9611M cached
Swap: 92K used, 1984M free
SEQ_SCANS SEQ_READS I_SCANS I_FETCHES INSERTS UPDATES DELETES RELNAME
1934 0 0 0 0 0 0 firmware_task
565 362983685 0 0 0 0 0 rap_match
147 147 0 0 0 0 0 seas_config
Note the enormous number under SEQ_READS; the rap_match table has about 642k records taking up ~60 MiB on disk.