From: | "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com> |
---|---|
To: | Jack <jdetate(at)gmail(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Tuning |
Date: | 2008-03-26 13:15:34 |
Message-ID: | 36e682920803260615v125638bdu5089600955fb2474@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Mon, Mar 24, 2008 at 6:56 PM, Jack <jdetate(at)gmail(dot)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(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Allan Kamau | 2008-03-27 08:21:30 | Looping the result set in plpythonu |
Previous Message | Milen A. Radev | 2008-03-26 12:39:00 | Re: Tuning |