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.

HTH

Cheers,
Norm.

PS. If I got any of the above wrong, I shall be corrected! ;-)

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
27a Lidget Hill
Pudsey
West Yorkshire
United Kingdom
LS28 7LG

Company Number: 05132767