Hi Tim, 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
