Hello,

> I've got an ASP.Net app with a FB 2.1 DB on a 64 bit Win system.
>
> From time to time, apparently ad hoc, the FB server does "something"
> that takes about half an hour. While doing so it does millions of I/O
> reads and Windows' file cache consumes all memory I allow it to, but the
> FB process itself doesn't seem to consume memory out of the ordinary.
>
> The DB has two tables with about 150 million records each, a few tables
> with a couple of million records, and several smaller tables. The large
> tables have four and six active indexes each (and a couple of inactive
> ones - I tried to deactivate a few to see if it helps).
>
> I tried to set weep interval to 0 even though the OIT and next diff has
> been less than 10 every time I looked, so I doubt a Sweep is the cause.
>
> I guess the most likely reason is that the OO framework issues some SQL
> that requires a natural scan of one or both of the large tables, or a
> full index scan or something.

Or garbage collection kicking in. The used garbage collection mode
(background, cooperative, mixed) depends on the Firebird architecture
and/or the GCPolicy parameter in firebird.conf.

> To see this, I'd need to trace/log what queries are issued when and when
> they are completed (i.e. how long they took to execute). I may be able
> to switch on loggin on the OO framework, but how would I best log it on
> the FB server side?
>
> I know there's FBTraceManager and FBSacnner(?). Would they give the info
> I need? Any other way?

Can only talk about FB TraceManager. It works only with Firebird 2.5 due
to the Trace API. We have customers, which moved to Firebird 2.5 in a
test environment for just taking advantage of the Trace API. Run stuff
there and improved queries in their dev environment. ;-)

You don't even need to backup/restore (change the ODS) for using the
Trace API. Simply take a copy of the database (just in case), connect
with Firebird 2.5 and you are ready to go in respect to the Trace API.

Not sure if a switch to Firebird 2.5 is that easy for you. OO framework etc.


HTH.

--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!