Обсуждение: 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

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

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

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

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

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