Subject Re: [firebird-support] How to analyse a db using gstat?
Author Alexandre Benson Smith
Yusuf Celik wrote:
> Thanks Alexandre for the reply,
> I really appreciate it.
> I've got no performance problem with this table.
> There is a performance problem with all over the application.
> I am just trying to find out where to start from.
> And I thought to start with checking on indexes is a good idea.
> To do that I need to learn how to read the stats values.
> Thanks again
> With kindest regards
> yc


My steps would be:

1.) look at database statistics during the slowness of the server (use
gstat -h for this), if your application leaves a transaction open for a
long period it prevents the garbage collector process to remove unneeded
data, and force the serve work hard to determine which version of a
record each transaction should see

2.) Pinpoint the problematic queries, and try it out on ISQL for
example, and report here the used plan

3.) Look for problematic queries like "select count(*) from MyTable" to
look the existence of a record or "select max(Number) from Invoice"
without an descending index on Invoice.Number and so on. This point is
tight correlated to the point 2.

4.) If you upgraded from 1.5 to 2.0 take a look if you are not using NOT
IN subselect, since in FB 2.0 it can't use an index. This point is
correlated to point 2 too

This is the starting point to check for performance bottlenecks.

see you !

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil