adding more information about process(es) cpu and memory usage

On 04/23/2015 08:00 PM, Radovan Jablonovsky wrote:

During current encounters with amazon web services - RDS, the DBA does not
have access to OS/linux shell of underlying instance. That render some
postgresql monitoring technique of process CPU and memory usage, not
useful. Even if the AWS provide internal tools/programming interface for
monitoring, it could be very useful to have this information provided by
postgresql system table(s)/view/functions/api. The information about how
much postgresql background/process is using CPU (similar to command top
result) and memory. it could be something as simple as adding cpu,memory
information fields to pg_stat_activity.

You can write an extension to do that. Of course, Amazon won't let you
run your own C extension either (otherwise you could use that to escape
into shell), but if you do it well and publish and get it included into
standard distributions, they just might pick it up. Unless they don't
want you to see that information. If they don't, then they wouldn't let
you use the system views either.

In a nutshell, I don't think PostgreSQL should get involved in that...

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Apr 23, 2015 at 10:17 AM, Heikki Linnakangas <hlinnaka@iki.fi>
wrote:

On 04/23/2015 08:00 PM, Radovan Jablonovsky wrote:

During current encounters with amazon web services - RDS, the DBA does not
have access to OS/linux shell of underlying instance. That render some
postgresql monitoring technique of process CPU and memory usage, not
useful. Even if the AWS provide internal tools/programming interface for
monitoring, it could be very useful to have this information provided by
postgresql system table(s)/view/functions/api. The information about how
much postgresql background/process is using CPU (similar to command top
result) and memory. it could be something as simple as adding cpu,memory
information fields to pg_stat_activity.

You can write an extension to do that. Of course, Amazon won't let you run
your own C extension either (otherwise you could use that to escape into
shell), but if you do it well and publish and get it included into standard
distributions, they just might pick it up. Unless they don't want you to
see that information. If they don't, then they wouldn't let you use the
system views either.

In a nutshell, I don't think PostgreSQL should get involved in that...

I have often wanted an SQL function which would expose the back-end's
rusage statistics to the front-end. This could support a \timing feature
variant to psql that reports more than just wall-clock time.

I don't use RDS, and use shell access and "top" (and "strace" and "gdb")
quite enthusiastically, but still it is a pain to correlate any given
front-end to any given back-end.

Would such an addition to core be welcome?

Cheers,

Jeff

Jeff Janes <jeff.janes@gmail.com> writes:

On Thu, Apr 23, 2015 at 10:17 AM, Heikki Linnakangas <hlinnaka@iki.fi>
wrote:

In a nutshell, I don't think PostgreSQL should get involved in that...

I have often wanted an SQL function which would expose the back-end's
rusage statistics to the front-end. This could support a \timing feature
variant to psql that reports more than just wall-clock time.

I don't use RDS, and use shell access and "top" (and "strace" and "gdb")
quite enthusiastically, but still it is a pain to correlate any given
front-end to any given back-end.

Would such an addition to core be welcome?

The reason nobody's gotten around to that in the last fifteen years is
that per-process rusage isn't actually all that interesting; there's
too much that happens in background daemons, for instance.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Apr 23, 2015 at 9:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The reason nobody's gotten around to that in the last fifteen years is
that per-process rusage isn't actually all that interesting; there's
too much that happens in background daemons, for instance.

There's *some* stuff that happens in background daemons, but if you
want to measure user and system time consume by a particularly query,
this would actually be a pretty handy way to do that, I think.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 4/24/15 6:29 AM, Robert Haas wrote:

On Thu, Apr 23, 2015 at 9:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The reason nobody's gotten around to that in the last fifteen years is
that per-process rusage isn't actually all that interesting; there's
too much that happens in background daemons, for instance.

There's *some* stuff that happens in background daemons, but if you
want to measure user and system time consume by a particularly query,
this would actually be a pretty handy way to do that, I think.

I more often am wondering what a running backend is doing OS-wise, but
being able to see what happened when it finished would definitely be
better than what's available now.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Le 24/04/2015 21:11, Jim Nasby a écrit :

On 4/24/15 6:29 AM, Robert Haas wrote:

On Thu, Apr 23, 2015 at 9:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The reason nobody's gotten around to that in the last fifteen years is
that per-process rusage isn't actually all that interesting; there's
too much that happens in background daemons, for instance.

There's *some* stuff that happens in background daemons, but if you
want to measure user and system time consume by a particularly query,
this would actually be a pretty handy way to do that, I think.

I more often am wondering what a running backend is doing OS-wise, but
being able to see what happened when it finished would definitely be
better than what's available now.

There are at least two projects that provides this kind of statistics
for backends: pg_proctab (https://github.com/markwkm/pg_proctab) and
pg_stat_kcache (https://github.com/dalibo/pg_stat_kcache). Michael also
wrote an article on this topic some weeks ago
(http://michael.otacoo.com/postgresql-2/postgres-calculate-cpu-usage-process/).

Regards
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Apr 25, 2015 at 4:23 PM, Julien Rouhaud wrote:

There are at least two projects that provides this kind of statistics
for backends: pg_proctab (https://github.com/markwkm/pg_proctab) and
pg_stat_kcache (https://github.com/dalibo/pg_stat_kcache). Michael also
wrote an article on this topic some weeks ago
(http://michael.otacoo.com/postgresql-2/postgres-calculate-cpu-usage-process/).

Don't use that hack except for development purposes ;) pg_stat_kcache
gives shows that core has enough facility for an extension doing
query-base CPU statistics by using the two hooks at executor init and
end with two calls to getrusage to calculate diffs for the stats it
needs. pg_proctab is useful as well to get live data.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers