pg.ddx.io pgsql-benchmarks@postgresql.org mailing list archive
help / color / mirror / Atom feedstats on cursor and query execution troubleshooting
4+ messages / 2 participants
[nested] [flat]
* stats on cursor and query execution troubleshooting
@ 2004-10-06 10:15 "Alban Médici (NetCentrex)" <amedici@fr.netcentrex.net>
2004-10-06 14:16 ` Re: [PERFORM] stats on cursor and query execution troubleshooting Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: "Alban Médici (NetCentrex)" @ 2004-10-06 10:15 UTC (permalink / raw)
To: pgsql-benchmarks; pgsql-performance@postgresql.org
Hi,
I'm looking for the statistic of memory, CPU, filesystem access while
executing some regular SQL query, and I want to compare them to
same kind of results while executing a cursor function.
The stat collector give me good results (sequencial scans , acceded
tuple .....) for regular query but nor for cursor (as explain in the
documentation)
For more results, i have activated some log level in the postgresql.conf :
show_query_stats = true
But I have some trouble in the results interpretation such as :
! system usage stats:
! 2.776053 elapsed 1.880000 user 0.520000 system sec
! [1.910000 user 0.540000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 5/1 [319/148] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 3877 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
Here is result done after fetching ALL a row with 178282 records in the
table.
looking at the i/o stat of linux I saw a filesystem access while
executing this request but not in the previous log !!!
! 0/0 [0/0] filesystem blocks in/out
I'm running postgresql 7.2.4 under redhat 7.2
Does am i wrong in my interpretation ?
Does any newest postgresql version could told me execution paln for a
fetch AND better stats ?
thx
Ps: please excuse my poor english
--
Alban Médici
R&D software engineer
------------------------------
you can contact me @ :
http://www.netcentrex.net
------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] stats on cursor and query execution troubleshooting
2004-10-06 10:15 stats on cursor and query execution troubleshooting "Alban Médici (NetCentrex)" <amedici@fr.netcentrex.net>
@ 2004-10-06 14:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-08 08:47 ` Re: [PERFORM] stats on cursor and query execution troubleshooting "Alban Médici (NetCentrex)" <amedici@fr.netcentrex.net>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2004-10-06 14:16 UTC (permalink / raw)
To: "Alban Médici (NetCentrex)" <amedici@fr.netcentrex.net>; +Cc: pgsql-benchmarks; pgsql-performance@postgresql.org
=?ISO-8859-1?Q?=22Alban_M=E9dici_=28NetCentrex=29=22?= <amedici@fr.netcentrex.net> writes:
> I'm looking for the statistic of memory, CPU, filesystem access while=20
> executing some regular SQL query, and I want to compare them to
> same kind of results while executing a cursor function.
I think your second query is finding all the disk pages it needs in
kernel disk cache, because they were all read in by the first query.
This has little to do with cursor versus non cursor, and everything
to do with hitting recently-read data again.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] stats on cursor and query execution troubleshooting
2004-10-06 10:15 stats on cursor and query execution troubleshooting "Alban Médici (NetCentrex)" <amedici@fr.netcentrex.net>
2004-10-06 14:16 ` Re: [PERFORM] stats on cursor and query execution troubleshooting Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-10-08 08:47 ` "Alban Médici (NetCentrex)" <amedici@fr.netcentrex.net>
2004-10-08 13:55 ` Re: [PERFORM] stats on cursor and query execution troubleshooting Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: "Alban Médici (NetCentrex)" @ 2004-10-08 08:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-benchmarks; pgsql-performance@postgresql.org
Thanks for your repply, but I still don"t understand why the statistic
logs :
! 0/0 [0/0] filesystem blocks in/out
it told me there is no hard disk access, I'm sure there is, I heard my
HDD, and see activity using gkrellm (even using my first query ; big
select *) ?
2004-10-08 10:40:05 DEBUG: query: select * from "LINE_Line";
2004-10-08 10:40:53 DEBUG: QUERY STATISTICS
! system usage stats:
! 48.480196 elapsed 42.010000 user 0.700000 system sec
! [42.030000 user 0.720000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 6/23 [294/145] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 3902 read, 0 written, buffer hit
rate = 11.78%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
looking at the web some logs, I saw those fields filled (i/o filesystem)
Does my postgresql.conf missing an option or is therer a known bug of my
postgresql server 7.2.4 ?
thx
regards
Alban Médici
on 06/10/2004 16:16 Tom Lane said the following:
>=?ISO-8859-1?Q?=22Alban_M=E9dici_=28NetCentrex=29=22?= <amedici@fr.netcentrex.net> writes:
>
>
>>I'm looking for the statistic of memory, CPU, filesystem access while=20
>>executing some regular SQL query, and I want to compare them to
>>same kind of results while executing a cursor function.
>>
>>
>
>I think your second query is finding all the disk pages it needs in
>kernel disk cache, because they were all read in by the first query.
>This has little to do with cursor versus non cursor, and everything
>to do with hitting recently-read data again.
>
> regards, tom lane
>
>
>
--
Alban Médici
R&D software engineer
------------------------------
you can contact me @ :
http://www.netcentrex.net
------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] stats on cursor and query execution troubleshooting
2004-10-06 10:15 stats on cursor and query execution troubleshooting "Alban Médici (NetCentrex)" <amedici@fr.netcentrex.net>
2004-10-06 14:16 ` Re: [PERFORM] stats on cursor and query execution troubleshooting Tom Lane <tgl@sss.pgh.pa.us>
2004-10-08 08:47 ` Re: [PERFORM] stats on cursor and query execution troubleshooting "Alban Médici (NetCentrex)" <amedici@fr.netcentrex.net>
@ 2004-10-08 13:55 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-10-08 13:55 UTC (permalink / raw)
To: "Alban Médici (NetCentrex)" <amedici@fr.netcentrex.net>; +Cc: pgsql-benchmarks; pgsql-performance@postgresql.org
=?ISO-8859-1?Q?=22Alban_M=E9dici_=28NetCentrex=29=22?= <amedici@fr.netcentrex.net> writes:
> Thanks for your repply, but I still don"t understand why the statistic
> logs :
> ! 0/0 [0/0] filesystem blocks in/out
> it told me there is no hard disk access, I'm sure there is,
Complain to your friendly local kernel hacker. We just report what
getrusage() tells us; so if the number is wrong then it's a kernel bug.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-10-08 13:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-06 10:15 stats on cursor and query execution troubleshooting "Alban Médici (NetCentrex)" <amedici@fr.netcentrex.net>
2004-10-06 14:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-08 08:47 ` "Alban Médici (NetCentrex)" <amedici@fr.netcentrex.net>
2004-10-08 13:55 ` Tom Lane <tgl@sss.pgh.pa.us>
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