Re: Keeping clean pages in shared memory

Raj Gopalakrishna writes: |> I am aware of the fact that flushed pages are not removed from the |> cache but what I was trying to say that the effectiveness of the cache is lost |> for writes (flushes) while it still works for page reads (lookups). This is |> because the number of IOs have been increased significantly because of |> frequent flushes of the working set of pages. Since writes are always asynchronous to database processing, writes performed (other than during the checkpoint) do not directly impact database performance. The write cache reported in onstat -p will be lower, but so what? Write caching is *meaningless* as all writes done by database threads (or sqlturbos) are to cache. |> Generally a low MAX_DIRTY value is good for OLTP appliations & an high MAX_DIRTY |> value is good for batch/DSS (with some update) type of applications. I don't |> think one can suggest a value like 10% for all types of applications, then |> why have a configurable parameter LRU_MAX_DIRTY. I agree with your logic for |> OLTP type of environment. If you have a DSS app, the buffer pool does not become dirty, so the LRU_MAX_DIRTY will not have any impact. There isn't much point in setting it that low for DSS (if you are truly doing all DSS), but it won't hurt. Thus it IS a good value for the large majority of applications. |> Also, one must look at the number of LRU queues to see if one can benefit from |> increasing the number of LRU queues and the number of page cleaners. This depends entirely on the user load. With 5.0 and before, that means number of sqlturbos. 6.0 and beyond, that means number of CPU VPs. |> As it is on most databases servers, the system is IO bound as opposed |> to CPU bound and hence it is imperative that we *minimize* and *spread* IO |> to get maximum performance of a system. Not really. If writes are async to the part of the server doing the work, they don't have much of an impact. Spreading i/o (across devices/controllers) is certainly important, when possible. That is a big advantage with 7.10's fragmentation. |> Finally, I don't think Informix uses sorted writes for LRU page writes even |> in 7.1 as the cost of sorting randomly updated pages may be worth it. Anyway |> I will check on it sometime soon. I work for Informix. I am telling you this is a feature we put in to 7.10. It is not speculation. Dave Kosenko Disclaimer: All opinions expressed in this message are well-reasoned and insightful; needless to say, they are not those of Informix Software, its partners or lackeys. Anyone who says otherwise is itching for a fight. **************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney