|Subject||Re: [firebird-support] Why does a query take many times as long the first time it's run?|
On 27/08/13 15:29, Tim Ward 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:
> 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
> (note 2 seconds instead of 167 seconds) . Subsequent runs then take
> consistently 2 seconds.
YOU will possibly be seeing garbage collection there, especially if this
is the first time that the table has been scanned since previous
changes/deletions were committed.
Also, note the number of reads - in the slow query, you have 92,650
while the speedy query has only 14,491 so you have a lot of pages to
read into cache prior to getting the results out.
You can see what effect a sweep is having on your query performance by
leaving a day or so's worth of transactions to happen, and then manually
running a sweep. When the sweep completes, try your query again.
You also have a large difference in fetches too. I'm away from my notes,
but as far as I remember, a read is a physical read from disc while a
fetch is a cached read from the cache.
I'd say that your slow response is down to cache filling, physical reads
and possibly, a bit of garbage collection too.
PS. If I got any of the above wrong, I shall be corrected! ;-)
Dunbar IT Consultants Ltd
27a Lidget Hill
Company Number: 05132767