Re: HELP: Informix Online on HP-K460 is NOT-SCALABLE?

Jason Harris wrote: > > My 0.02c worth on this. > > If one process has a page from the database in shared memory and they > update it or otherwise cause it to lock then if another process wants > to access that page it has to wait for the page to be free. As the > page is in a buffer already and does not need to be read from disk the > process will wait for that buffer. So in some cases high lock > contention will also increase buff waits. It does not always mean that > there are insufficient buffers. > > Also other shared memory structures for sysmaster etc are in buffers. > In some cases a process will need to wait for one of those buffers > also. If you are suggesting that locked ROWS are the cause of buffer waits, then I have to say that you are quite wrong. While a locked row will prevent logical access to the resource, assuming the second accessor is respecting locks (i.e. not using dirty read), it will NOT prevent the very basic and low-level access represented by a buffer wait. For example, dirty read will NOT allow you to get around buffer waits, though it WILL get around locks. Buffer waits are the result of one thread holding a mutex on a buffer (which implies it is in the process of modifying the buffer contents) that contains data another thread needs access to. This is probably most commonly caused by a design that places a high degree of dependency on a small resource that is frequently updated. For example, implementing your own SERIAL functionality by having a table with a single row that is selected then updated to provide a sequence generator. There are other less common causes; this is the one I've seen the most. To track down problems of this sort, look for small tables that are updated by a lot of different processes. -- Dave "That's just my opinion - I could be wrong." - Dennis Miller