Re: Can anyone explain this huge increase in logical I./O....?

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Mon, 23 Oct 2006 20:37:48 GMT
Message-ID: <J7LvzB.67v@igsrsparc2.er.usgs.gov>

mccmx_at_hotmail.com wrote:

>> In the "slow" database, you have many more waits for the 'cache buffer
>> chains' latch, plus you have buffer busy waits. This tells me that in
>> this system, you have much more concurrency for these resources. Is it
>> possible that in the first, you were the only user accessing this data,
>> but in the second database, there were multiple users accessing (and
>> maybe modifying) the same data blocks?
>>

>
> You hit the nail on the head - the fast DB has no concurrency (DEV) and
> the slow DB (PROD) has additional workload thanks to the users :).
>
> However the application is a reporting system in which users cannot
> update this table. So I can understand that the concurrency would
> cause a slowdown but I can't understand why the logical I/O count has
> jumped up to over 20 times more. I could accept it if it was
> rebuilding a pre-image of changed blocks via the rollback segments but
> this table cannot be modified by users of the application.
>
> Would 'delayad block cleanout' cause this behaviour..?
>
> Matt
>

It could be that your buffer cache is inadequately sized in PROD to support this to your satisfaction. The 'cache buffer chains' latch along with the 'buffer busy waits' indicates that the system is waiting for an available block in the buffer cache. So you might need to increase your db cache size.

It is probably not the 'delayed block cleanout' since the dirty buffers have likely been written to disk by now...at least for those blocks that are involved in this table.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Mon Oct 23 2006 - 15:37:48 CDT