pg.ddx.io pgsql-admin@postgresql.org mailing list archive help / color / mirror / Atom feed From: Jonah H. Harris <jonah.harris@gmail.com>
To: Jack <jdetate@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Tuning
Date: Wed, 26 Mar 2008 09:15:34 -0400
Message-ID: <36e682920803260615v125638bdu5089600955fb2474@mail.gmail.com> (raw )
In-Reply-To: <002601c88e02$5629fc10$027df430$@com >
References: <002601c88e02$5629fc10$027df430$@com >
On Mon, Mar 24, 2008 at 6:56 PM, Jack <jdetate@gmail.com> wrote:
> Is there a Postgres equivalent of tkprofs for tuning?
No. But what you could do is combine several Postgres things to try
and get the same information.
IIRC, you could use:
- log_parser_stats
- log_planner_stats
- log_executor_stats
while running EXPLAIN ANALYZE on the query.
psql -U user1 -d postgres > /tmp/pgprof.out 2>&1 <<SQL
SET log_parser_stats TO on;
SET log_planner_stats TO on;
SET log_executor_stats TO on;
SET client_min_messages TO log;
EXPLAIN ANALYZE SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp;
SQL
This will output:
SET
SET
SET
SET
LOG: PARSER STATISTICS
DETAIL: ! system usage stats:
! 0.047635 elapsed 0.000000 user 0.000000 system sec
! [0.008000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 2/19 [10/726] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 2/0 [41/13] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
LOG: PARSE ANALYSIS STATISTICS
DETAIL: ! system usage stats:
! 0.000003 elapsed 0.000000 user 0.000000 system sec
! [0.008000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 0/0 [10/726] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [41/14] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
LOG: REWRITER STATISTICS
DETAIL: ! system usage stats:
! 0.000002 elapsed 0.000000 user 0.000000 system sec
! [0.008000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 0/0 [10/726] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [41/15] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
LOG: PARSE ANALYSIS STATISTICS
DETAIL: ! system usage stats:
! 0.142050 elapsed 0.004000 user 0.000000 system sec
! [0.012000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 0/42 [10/786] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 7/0 [50/16] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 10 read, 0 written, buffer hit
rate = 75.61%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
LOG: REWRITER STATISTICS
DETAIL: ! system usage stats:
! 0.075110 elapsed 0.000000 user 0.000000 system sec
! [0.012000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 1/6 [11/795] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 1/0 [51/17] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
LOG: EXECUTOR STATISTICS
DETAIL: ! system usage stats:
! 0.164599 elapsed 0.000000 user 0.000000 system sec
! [0.012000 user 0.004000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 2/95 [12/884] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 8/1 [58/18] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 10 read, 0 written, buffer hit
rate = 80.77%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on emp (cost=0.00..5.25 rows=225 width=56) (actual
time=19.637..20.404 rows=150 loops=1)
Total runtime: 42.937 ms
(2 rows)
You could write a quick awk script to parse this out into something
more usable... but you get the idea.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
view thread (16+ messages) latest in thread
Message-ID: <36e682920803260615v125638bdu5089600955fb2474@mail.gmail.com>
Permalink: ../36e682920803260615v125638bdu5089600955fb2474@mail.gmail.com/
Also on: postgresql.org/message-id/36e682920803260615v125638bdu5089600955fb2474@mail.gmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jonah.harris@gmail.com, jdetate@gmail.com
Subject: Re: Tuning
In-Reply-To: <36e682920803260615v125638bdu5089600955fb2474@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox