Re: Keeping clean pages in shared memory

In article <3j7t1r$dbh@infmx.informix.com>, David Kosenko <davek@informix.com> wrote: > >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. I totally disagree. There can be a large cost for flushing dirty blocks to disk if you have any type of working set at all (now if your data is completely random and over a large dataset, than this may not be the case). The number of physical writes per second that the database does to disk can be greatly increased by a small value for MAX_DIRTY. You will be constantly cleaning pages that don't need to be cleaned. For example, imagine that your dirty working set is 20,000 pages and you have a 100,000 entry buffer pool. Now if you set MAX_DIRTY to 10% then you will be constantly flushing pages to disk (doing physical disk writes). If your MAX_DIRTY level was 25%, then you would be doing ZERO physical writes to disk because the number of different dirty buffers would not be greater than 20,000. Any time you can reduce the total number of I/Os per second that you are writing to disk you will greatly increase your performance of the database engine. In addition, when you clean a dirty page, that "cleaned" page is placed on the "clean" side of an LRU. I imagine it is inserted at the LRU end of the "clean" side since it was at the LRU side of the "dirty" side if it was cleaned. This means that if you have a small MAX_DIRTY, some hot pages that tend to get modified every once in a while may actually get removed from the buffer cache completely (the buffer is cleaned, then placed on the LRU end of a "clean" LRU. Now you have a cache miss and choose that buffer to use to read new data from disk. Now your "warm" data page has been flushed from the cache, so you need to read it from disk next time you access it.) This means that you may actually do MORE read I/Os from disk with a small MAX_DIRTY value in addition to the more writes you are doing. I would love to run with a high MAX_DIRTY value, but the cost of checkpointing becomes extremely large because of the lack of a lazy checkpoint in Informix. Ideally, I would run OLTP applications at 50% dirty to keep a good mix between not pushing data pages that are read often and modified seldomly from the cache and to keep modified often data in the cache. However, with a large buffer pool (say 500,000 entries) doing a checkpoint of 250,000 buffers can take a while (up to 2 minutes depending on how well your data is distributed and how many disks you have). If Informix had some sort of lazy checkpoint that did not stop all processing during the checkpoint, then I could use the larger MAX_DIRTY value. > >|> 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. > There might be a small number of writes done in a DSS application that might be highly localized, so keeping a large LRU_MAX_DIRTY probably guarantees that no physical disk writes occur except for at checkpoint time. >|> 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. > The advantage of async disk I/O is that it allows the database program to have more control over how it does I/O and when it has to block. Async I/O does not have any magic property in that it reduces the number of physical disk I/O that an application has to perform. So in either case, minimizing the amount of disk I/O done is a good thing (less work for the OS means more CPU that can be used by Informix). >|> 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. > I don't even believe that for the majority of applications, that sorting the dirty buffers before checkpoint is even a win (sorting LRU writes for buffer cleaning is most likely a complete waste of time). I claim that sorting the buffers actually results in a performance loss for most systems! For a large buffer cache, sorting before the checkpoint can take a long long time even with multiple fast processors (say 15 seconds for over 500,000 entry buffer cache). In the 15 seconds that was wasted sorting data, the database could have written out 120,000 pages (assuming 100 disks at 80 I/Os per second per disk). Sorting the buffers cost me 15 seconds on my already long checkpoint. With today's technology most OSes and most disks do their own sorting and will override any sorting done by Informix. So the only value sorting has is if Informix can merge adjacent buffers together. I imagine in OLTP applications this does not happen very often (maybe 10% of the time, max). In addition, most often at checkpoint you are I/O bound and not cpu bound, so doing more work (via writing more pages to the OS) may not be bad. --Chris These views are of course my own.