Re: rusage (Re: Good afternoon.) - Mailing list pgsql-performance

From Julien Rouhaud
Subject Re: rusage (Re: Good afternoon.)
Date
Msg-id CAOBaU_Z6hQr3JnPGs05U7qZx4F7Ri6FtkJmVk_3sL-C+8Tc4Jw@mail.gmail.com
Whole thread Raw
In response to rusage (Re: Good afternoon.)  (Justin Pryzby <pryzby@telsasoft.com>)
List pgsql-performance
On Wed, Mar 6, 2019 at 11:49 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> Hi,
>
> On Wed, Mar 06, 2019 at 04:09:46PM -0400, Kenia Vergara wrote:
> > Good afternoon.
> > I need to know the commands to display the execution time, CPU usage and
> > memory usage, but through the Postgres console.Thank you.
>
> Does this do what you want ?  Note that the "QUERY STATISTICS" are log output,
> and not a part of the sql result.
>
> $ psql postgres -Atc "SET client_min_messages=log; SET log_statement_stats=on" -c 'explain analyze SELECT max(i) FROM
generate_series(1,999999)i'
> SET
> LOG:  QUERY STATISTICS
> DETAIL:  ! system usage stats:
> !       0.625402 s user, 0.059799 s system, 0.687496 s elapsed
> !       [0.629882 s user, 0.059799 s system total]
> !       10672 kB max resident size
> !       0/27344 [0/27344] filesystem blocks in/out
> !       0/1378 [0/2365] page faults/reclaims, 0 [0] swaps
> !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
> !       0/167 [2/167] voluntary/involuntary context switches
> Aggregate  (cost=12.50..12.51 rows=1 width=4) (actual time=680.155..680.161 rows=1 loops=1)
>   ->  Function Scan on generate_series i  (cost=0.00..10.00 rows=1000 width=4) (actual time=253.512..497.462
rows=999999loops=1)
 
> Planning Time: 0.227 ms
> Execution Time: 686.200 ms

You could also consider using pg_stat_kcache
(https://github.com/powa-team/pg_stat_kcache/) extension, which gather
most of those metrics and accumulate them per (queryid, dbid, userid).
It requires more configuration, depends on pg_stat_statements
extension, and need a postgres restart to activate it though.


pgsql-performance by date:

Previous
From: Justin Pryzby
Date:
Subject: rusage (Re: Good afternoon.)
Next
From: Artur Zając
Date:
Subject: IS NOT DISTINCT FROM statement