Is there a Postgres equivalent of tkprofs for tuning?

On Mon, Mar 24, 2008 at 4:56 PM, Jack <jdetate@gmail.com> wrote:
>
>
>
>
> Is there a Postgres equivalent of tkprofs for tuning?

you might want to provide an explanation of what tkprofs is and which
parts you're interested in emulating in pgsql.

Have you read up on explain, explain analyze, and the various
pg_stat_* tables? Do any of those help?

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

tkprof generates performance characteristics for an arbitrary number
of SQL statements. The output includes measurements for the individual
SQL statements as well as cumulated values for the sequence of SQL
statements being measured. Data is presented in a two dimensional
format with the first dimension being the type of operation (parsing,
compiling, executing, fetching) and the second dimension being the
time spent in CPU or IO. You can see a sample output here:
http://www.jlcomp.demon.co.uk/tkprof_01.html .

The trick is that the Oracle code is instrumented to track all the
information necessary for these measurements. When tracing is enabled,
the measure values are output sequentially and this output is the
post-processed by tkprof. The raw (tracing) output is also fairly
readable, so if you want to dig deeper than the tkprof output, you can
use it (eventually post-processing it in your own way).

I am not familiar with PostgreSQL's explain analyze method, but as far
as I remember, it can be used only for one SQL statement and requires
a significant amount of practice to interpret easily.

BTW, I had a bookmark for a good tutorial on explain analyze, but the
page is no longer available:
http://www.pervasive-postgres.com/in....aspx?id=10120 .
Can you suggest a good tutorial?

Thanks
Peter

On Tue, Mar 25, 2008 at 4:29 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Mar 24, 2008 at 4:56 PM, Jack <jdetate@gmail.com> wrote:
> >
> >
> >
> >
> > Is there a Postgres equivalent of tkprofs for tuning?
>
> you might want to provide an explanation of what tkprofs is and which
> parts you're interested in emulating in pgsql.
>
> Have you read up on explain, explain analyze, and the various
> pg_stat_* tables? Do any of those help?
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

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

Peter Kovacs написа:
[...]
> BTW, I had a bookmark for a good tutorial on explain analyze, but the
> page is no longer available:
> http://www.pervasive-postgres.com/in....aspx?id=10120 .
> Can you suggest a good tutorial?

I'm not sure what was the tutorial at the above URL but this what I know:
http://redivi.com/~bob/oscon2005_pgs...ain_Public.pdf
(google for "explaining explain")

[...]


--
Milen A. Radev


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

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/

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