Thread: Monitoring activities of PostgreSQL
I do have a PL/SQL function that gets executed called many times but with different parameter values each of these times. For most invocations of this function run in a couple of seconds however some invocations of the same function run (on the same dataset) for hours with very little disk activity but high CPU. How can I monitor the actual DB activities during such times so I may better understand what the situation truly is. I have seen some users on this list posting some complex log/outputs, this are the kind of outputs I would like to capture and view. Where are they? Allan.
2010/6/15 Allan Kamau <kamauallan@gmail.com>
I do have a PL/SQL function that gets executed called many times but
with different parameter values each of these times. For most
invocations of this function run in a couple of seconds however some
invocations of the same function run (on the same dataset) for hours
with very little disk activity but high CPU.
How can I monitor the actual DB activities during such times so I may
better understand what the situation truly is.
You can monitor system parameters (CPU, disk IO, memory) with standard OS-specific tools (on Unix: top, ps iostat,vmstat)
I have seen some users
on this list posting some complex log/outputs, this are the kind of
outputs I would like to capture and view. Where are they?
they are mostly in log files or output from EXPLAIN ANALYZE command
for general info see
http://www.postgresql.org/docs/current/static/runtime-config-logging.html
http://www.postgresql.org/docs/8.4/static/using-explain.html
http://wiki.postgresql.org/wiki/Performance_Optimization
and this list archives.
Filip
Allan Kamau <kamauallan@gmail.com> writes: > How can I monitor the actual DB activities during such times so I may > better understand what the situation truly is. I have seen some users > on this list posting some complex log/outputs, this are the kind of > outputs I would like to capture and view. Where are they? The way you phrase the question makes me think about OProfile : http://wiki.postgresql.org/wiki/Profiling_with_OProfile Regards, -- dim
2010/6/16 Filip Rembiałkowski <filip.rembialkowski@gmail.com>: > > 2010/6/15 Allan Kamau <kamauallan@gmail.com> >> >> I do have a PL/SQL function that gets executed called many times but >> with different parameter values each of these times. For most >> invocations of this function run in a couple of seconds however some >> invocations of the same function run (on the same dataset) for hours >> with very little disk activity but high CPU. > > >> >> How can I monitor the actual DB activities during such times so I may >> better understand what the situation truly is. > > You can monitor system parameters (CPU, disk IO, memory) with standard > OS-specific tools (on Unix: top, ps iostat,vmstat) > >> I have seen some users >> on this list posting some complex log/outputs, this are the kind of >> outputs I would like to capture and view. Where are they? >> > > they are mostly in log files or output from EXPLAIN ANALYZE command > > > for general info see > > http://www.postgresql.org/docs/current/static/runtime-config-logging.html > http://www.postgresql.org/docs/8.4/static/using-explain.html > http://wiki.postgresql.org/wiki/Performance_Optimization > > and this list archives. > > > Filip > > > Thank you Filip, Craig and Dimitri, I will implement the trace tools as advised. Allan.