Thread: SQL profiler feature in Pgsql?
Does Postgresql have something akin to the MSSQL "SQL Profiler" to perform database traces? Any info is appreciated.
Thanks,
Michael McConnell
Systems Engineer
Education Networks of America
(615) 312-6132
mmcconnell@ena.com
http://www.ena.com
On Fri, 2002-04-19 at 04:22, Michael McConnell wrote:
> Does Postgresql have something akin to the MSSQL "SQL Profiler" to perform database traces? Any info is appreciated.
I don't think there is any tool to fully analyse the detail output that
PostgreSQL can generate, in the sort of presentation that MSSQL
produces, but you can use "EXPLAIN ....query...." to give a reasonable
view of how the query will perform.
EXPLAIN ANALYZE and EXPLAIN VERBOSE give substantially more output, but
you would have to be a guru, or a very patient person, to be able to
parse the output of that.
I have found that EXPLAIN <query> is usually sufficient for my needs.
Also, don't forget to make sure the tables are ANALYZEd before using it.
Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?
what about just the activity display that the mssql profiler generates? i typically use the sql server profiler to look at what is really happening in terms of statement execution so that i can make sure it meshes with what i think _should_ be happening. something like that would be _very_ useful. rjsjr > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Andrew McMillan > Sent: Thursday, April 18, 2002 8:20 PM > To: Michael McConnell > Cc: Pgsql-Novice@postgresql. org (E-mail) > Subject: Re: [NOVICE] SQL profiler feature in Pgsql? > > > On Fri, 2002-04-19 at 04:22, Michael McConnell wrote: > > Does Postgresql have something akin to the MSSQL "SQL Profiler" > to perform database traces? Any info is appreciated. > > I don't think there is any tool to fully analyse the detail output that > PostgreSQL can generate, in the sort of presentation that MSSQL > produces, but you can use "EXPLAIN ....query...." to give a reasonable > view of how the query will perform. > > EXPLAIN ANALYZE and EXPLAIN VERBOSE give substantially more output, but > you would have to be a guru, or a very patient person, to be able to > parse the output of that. > > I have found that EXPLAIN <query> is usually sufficient for my needs. > > Also, don't forget to make sure the tables are ANALYZEd before using it. > > Regards, > Andrew. > -- > -------------------------------------------------------------------- > Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington > WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St > DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 > Are you enrolled at http://schoolreunions.co.nz/ yet? > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
"Robert J. Sanford, Jr." <rsanford@nolimitsystems.com> writes:
> what about just the activity display that the mssql profiler
> generates?
Please don't assume that the rest of us are familiar with mssql.
If you're interested in a feature then you should describe what
it does.
regards, tom lane
will do! at its simplest the mssql profiler is a remote client that provides the ability to trace all of the sql commands that are being executed against a database. when initiating a trace you specify the server that you have admin priveleges on. you watch commands fly across the screen until you have seen enough and then stop the trace to figure out what you need to do from there. your results are presented in a grid that shows all of the activity along with other information such as the user name that executed the commands, the amount of cpu time the command took to execute, the absolute clock time the command took to execute, how many reads, how many writes, the client process id, and so forth and so on. the columns displayed in the grid are customizeable with some reasonable default list of columns displayed. you may also apply filters to limit which events are actually displayed to you - only on a particular databse on the server, only by a particular user id, only those events that require a certain number of writes or reads or cpu time to complete, only those events that involve invoking a particular stored procedure, and so forth and so on. another feature of the profiler is that it allows you replay a captured trace in a step-wise fashion. if you have something in a large batch that is failing and want to winnow down you can step through the replay until the failure occurs. just like an ide debugger but without the watch window. there is also an index tuning wizard that will try to walk you through suggestions on how to optimize your indices based on the performance of the trace that you just collected. being able to watch what is happening live very nice. rjsjr > "Robert J. Sanford, Jr." <rsanford@nolimitsystems.com> writes: > > what about just the activity display that the mssql profiler > > generates? > > Please don't assume that the rest of us are familiar with mssql. > If you're interested in a feature then you should describe what > it does. hmmmmmm.... parsing the text above trying to determine if there is any rancor in the statement.... nothing overt so i must act under the assumption that the terseness is due to the late hour, under-caffeination, and/or my lack of self-esteem and always looking for complete validation and assuming if i'm not being 100% validated then i'm being hammered.... okay, not being flamed so i will be happy! especially since i've got a cold RC Cola in my hand!
--- "Robert J. Sanford, Jr." <rsanford@nolimitsystems.com> wrote: > will do! > > at its simplest the mssql profiler is a remote > client that > provides the ability to trace all of the sql > commands that > are being executed against a database. when > initiating a > trace you specify the server that you have admin > priveleges > on. you watch commands fly across the screen until > you have > seen enough and then stop the trace to figure out > what you > need to do from there. I want one too! (Having used MS's) But, alas, spare time is hard to come by these days. > there is also an index tuning wizard that will try > to walk you > through suggestions on how to optimize your indices > based on > the performance of the trace that you just > collected. Currently doing this the hard way. > > being able to watch what is happening live very > nice. I'm using KPM 1.3k6 Visual Process Manager - it comes with SuSE 7.2. Troy ______________________________________________________________________ Find, Connect Date! http://personals.yahoo.ca