Thread: how to determine what a process is doing

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/

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

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

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 #