On Tue, Aug 27, 2013 at 10:29 AM, Tim Ward <tdw@...> wrote:

> The first time I run a certain SELECT query after not running it for a
> while (hours or days) I might get stats like: (
>

(stats below)

>
> (note 2 seconds instead of 167 seconds) . Subsequent runs then take
> consistently 2 seconds.
>
> What's going on? Does the large number of "Writes" indicate that garbage
> collection has kicked in? If so, what can I do to prevent this? -
>

Without the query (and any procedures it invokes) I can't say for certain
that
you're seeing garbage collection. It does seem odd that the second run also
involves writes.

You don't say how you are running Firebird. If you are running SuperServer,
you can turn off cooperative garbage collection and let the garbage collect
thread bear that cost. My guess, based on the 150 buffers, is that you're
not using SuperServer.


As Norman said, the first time you run the query, Firebird fills the cache
with
pages it needs to find the query results. You're pushing 92 thousand pages
through 150 buffers. That may account for some of the writes - pages that
could stay in cache but age out and must be pushed to disk.

Depending on the machine you're running on and the number of potential
clients, 150 buffers may be the most you can manage. If not, increasing
the number of buffers should help.

Good luck,

Ann

>
>
> --
>
> Current memory = 3265728
> Delta memory = 290716
> Max memory = 3267368
> Elapsed time= 166.92 sec
> Buffers = 150
> Reads = 92650
> Writes 65659
> Fetches = 16182424
>
> If I immediately run the same query again I get something like
>
> Current memory = 3186284
> Delta memory = -79444
> Max memory = 3326372
> Elapsed time= 1.98 sec
> Buffers = 150
> Reads = 14491
> Writes 80
> Fetches = 1176021
>

[Non-text portions of this message have been removed]