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 #