Thread

1. how to determine what a process is doing

   Alex Howansky <alex@wankwood.com> — 2000-11-29T21:08:37Z

   I've looked in the docs, Bruce's book, and the list archives, but I've been
   unable to find an answer to this. Any help would be greatly appreciated.
   
   I have a database front-ended by a web site. All queries (apart from a few cron
   jobs and developer's manual tests) come from the web site through PHP. Lately,
   my database server's load average has been spiking badly. There may be 50
   concurrent queries running, and top shows that maybe three or four of them are
   really sucking up the horsepower. I'd like to find out what these hog processes
   are actually processing -- but it could be any one of a few hundred different
   queries.  Is there any way to determine exactly what a postgres process is
   doing at any time? The output from the ps command only shows "INSERT" or
   "SELECT", and not the full query string.
   
   TIA,
   
   -- 
   Alex Howansky
   Wankwood Associates
   http://www.wankwood.com/
   
   
   

2. Re: how to determine what a process is doing

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-29T21:43:43Z

   Alex Howansky <alex@wankwood.com> writes:
   > Is there any way to determine exactly what a postgres process is
   > doing at any time? The output from the ps command only shows "INSERT" or
   > "SELECT", and not the full query string.
   
   There isn't any really nice solution at the moment, but you could run
   the postmaster with -d2 to cause writing of all queries to the
   postmaster's log file (ie, its stdout/stderr).  You'd probably also want
   to compile with ELOG_TIMESTAMPS defined (see include/config.h) to get
   timestamps and process PIDs included in the log.  That'd give you info
   to correlate against what "top" shows.
   
   			regards, tom lane
   
   

3. indices

   Sandeep Joshi <sjoshi@zambeel.com> — 2000-11-30T01:31:16Z

   Hi,
      I had learned in theory that Hash indices are used for "=" and
   B-tree for "<" ,">".
   
       explain command doesn't tell us which index it is using. Hash or
   Btree?
   Also,
       should a following query
        "id < 1243" invoke a index ? (assuming there is an index on id).
   
       I have seen Postgres using Seq scan. Is sequential scan done afer
   getting the first
   page for "1243"?
   
   Sandeep
   
   

4. Re: how to determine what a process is doing

   Jan Wieck <janwieck@yahoo.com> — 2000-12-03T23:07:43Z

   Tom Lane wrote:
   > Alex Howansky <alex@wankwood.com> writes:
   > > Is there any way to determine exactly what a postgres process is
   > > doing at any time? The output from the ps command only shows "INSERT" or
   > > "SELECT", and not the full query string.
   >
   > There isn't any really nice solution at the moment, but you could run
   > the postmaster with -d2 to cause writing of all queries to the
   > postmaster's log file (ie, its stdout/stderr).  You'd probably also want
   > to compile with ELOG_TIMESTAMPS defined (see include/config.h) to get
   > timestamps and process PIDs included in the log.  That'd give you info
   > to correlate against what "top" shows.
   
       If  you  compiled  postgres with -g (what I do by default :-)
       you could use this little script:
   
           #!/bin/sh
   
           gdb <<_EOF_
           file /usr/home/pgsql/bin/postgres
           attach $1
           break pg_exec_query_string
           commands 1
           silent
           print query_string
           continue
           end
           continue
           _EOF_
   
       OK, could have some error checking and so, but it's  a  quick
       hack - not a final solution.
   
       Find  the PID of a backend you want to examine and give it as
       argument to the script. It'll then attach to the backend  and
       dump all queries sent from PHP until you hit ^C. It'll detach
       again and the PHP script will never know.
   
       If you redirect it's output  to  a  file,  just  wait  a  few
       seconds and hit ^C, there will not even be much delay for the
       PHP. So the user might not notice too.
   
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #================================================== JanWieck@Yahoo.com #