--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 12:59 PM 10/09/2004 +0000, you wrote:
>
> >Does there exist, or are there any plans to implement, a means of
> >accumulating performance related stats for a Firebird database.
> >Useful items would include, but not necessarily be limited to:
>
> If this is a "wish list" it doesn't belong here.
>
> If it's a support question, I'll try to help:
>
> >1) Active connections and what IP address they originate from.
>
> No, but network tools can collect this information.
>

I guess that I am lazy and want all pertinent info in one place.

> >2) Current or last active SQL command for each connection.
>
> No. Clients can do this, of course, via SQL monitoring and profiling.
>

This is true but pulling all the information together into one place
to analyze it is a pain and it is extremely difficult to capture the
information for all clients at an instant in time when you are having
the problem.

> >3) Execution time of above command.
>
> same as above
>
> >4) # of IOs done by last command.
>
> Mere "I/O" counts wouldn't be meaningful, since row data, index data

and

> blob data are all stored on pages. Recently-used pages are in the

page cache.

>
> You can examine the fill distribution of pages using gstat.
>

I would like to know the # of "physical" I/Os done to satisfy a
request. I realize that what Firebird thinks of as physical may be
satisfied by the OS cache but it would give me a hint as to who the
piggy process is.

> >5) Total execution time for connection.
>
> Don't understand what you want here.
>

To give me an idea of who is using the most resources.

> >6) Total IOs done by connection.
>
> Irrelevant, as above. You can get a count of rows affected by an I/U/D
> statement back to the client via the XSQLDA structure.
>

Rows affected <> IO.

> >7) Total length of time connection active.
>
> Network tools can log this.
>
> >8) Current transaction #.
>
> Not meaningful. There are typically many transactions "current" at
> once. You can use the context variables CURRENT_CONNECTION and
> CURRENT_TRANSACTION for the client to discover those IDs respectively.
>

Meaningful in that if the current, active transaction # of a
connections is n and the most recent transaction # for Firebird is n +
10000 then we know that the connection in question is sitting with a
transaction open for an inordinate period of time.

> Gstat will give you the ID of the Next Transaction.
>
> >9) Length of time current transaction active.
>
> Client only.
>
> >This would be a big help while trying to find the cause of Firebird
> >related performance problems.
>
> If you want to pursue the idea, post to firebird-Architect. There

is stuff

> in the lock tables that (theoretically) could be transmogrified into
> interesting peformance statistics.
>
> ./heLen

Thanks for the input. I usually hesitate to post to the "developer"
lists because I am not one and I have been barked at in the past for
posting to "their" lists. But I think that I will take you advice and
post this to the architect list as well.