X-Original-To: pgsql-benchmarks-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 34DD6329D7F; Wed, 6 Oct 2004 11:15:46 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40870-09; Wed, 6 Oct 2004 10:15:33 +0000 (GMT) Received: from mailer.unicite.fr.netcentrex.net (unknown [62.161.167.249]) by svr1.postgresql.org (Postfix) with ESMTP id 5B0F4329CDD; Wed, 6 Oct 2004 11:15:33 +0100 (BST) Received: from [192.168.101.228] ([192.168.101.228]) by mailer.unicite.fr.netcentrex.net with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id 42CX026B; Wed, 6 Oct 2004 12:15:32 +0200 Message-ID: <4163C5C3.3030304@fr.netcentrex.net> Date: Wed, 06 Oct 2004 12:15:31 +0200 From: =?ISO-8859-1?Q?=22Alban_M=E9dici_=28NetCentrex=29=22?= Organization: NetCentrex User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-benchmarks@postgresql.org, pgsql-performance@postgresql.org Subject: stats on cursor and query execution troubleshooting Content-Type: multipart/alternative; boundary="------------040806000209030206000904" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200410/1 X-Sequence-Number: 18 This is a multi-part message in MIME format. --------------040806000209030206000904 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Hi, 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. The stat collector give me good results (sequencial scans , acceded=20 tuple .....) for regular query but nor for cursor (as explain in the=20 documentation) For more results, i have activated some log level in the postgresql.conf : show_query_stats =3D 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= =3D 0.00% ! Local blocks: 0 read, 0 written, buffer hit rate= =3D 0.00% ! Direct blocks: 0 read, 0 written Here is result done after fetching ALL a row with 178282 records in the=20 table. looking at the i/o stat of linux I saw a filesystem access while=20 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=20 fetch AND better stats ? thx Ps: please excuse my poor english --=20 Alban M=E9dici R&D software engineer ------------------------------ you can contact me @ : http://www.netcentrex.net ------------------------------ --------------040806000209030206000904 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
------------------------------

--------------040806000209030206000904--