Обсуждение: Good afternoon.
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.
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 Justin
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.
On Wed, 6 Mar 2019 at 23:45, Kenia Vergara <vergarakenia@gmail.com> 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.
Regards,
Rafia Sabih
Rafia Sabih
Hi Kenia,
Take a look of this link
I think you are looking for something line pg_activity
But in that official pgsql documentation you'll find all you need to monitor queries and pgsql cluster behavior
On Wed, Mar 6, 2019, 7:45 PM Kenia Vergara <vergarakenia@gmail.com> 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.