Thread: Tuning
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?
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/instantkb13/article.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 >
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/instantkb13/article.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_pgsql_pdf/OSCON_Explaining_Explain_Public.pdf (google for "explaining explain") [...] -- Milen A. Radev
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/