Re: chekcpoints, BUFFER settings, and optimization

David Williams wrote: [Prior discussion and details SNIPPED] > Checkpoints are faster and LRU cleaners faster with more cleaners > on a single cpu vp machine. > Art was right, I was wrong. Experience over deductive reasoning. > Well I live and learn..one for the FAQ! > PS Where do I go for to raise a feature request to get cleaners to > asynchronously submit I/O requests during checkpoints? > [We have aio and kaio request queues, cio request queue?]. [He preens a bit but resists crowing ;-)] You can put in a feature request from the Informix Web Site if you have registered access or submit it to IIUG for inclusion in their next feature survey. The problem would be that currently all Informix "process" level threads block on I/Os and relinquish their VP to other threads while awaiting I/O completion. Because of this the notification of completion is probably not tagged so the Cleaner thread would not know which of the multitude of requests he queued had and had not completed. This would have to be dealt with so that the cleaner could notify the checkpoint manager thread (currently tying up CPU VP #1 exclusively! My own FEATURE request AARRGGHH!) that his last assignment was completed successfully. Even so, the manager will not assign another disk chunk to that cleaner until it has finished what it started in case another cleaner finishes first and can handle the next chunk more quickly. So even if the cleaner<=>AIO VP/KAIO thread relationship were made purely asynchronous the checkpoint<=>cleaner relationship is still synchronous. To clean that up the checkpoint would have to queue the chunks for the next available cleaner to pick up on its own as it finished submitting I/O requests for the last chunk. And on and on. This probably could be done but in order to get the performance gain that you seek, but, it is not as simple as it seems at first blush. BTW: Re my comment above about VP #1. I recently proved to Informix that any query whose thread or listener is assigned to CPU VP #1 is frozen during the checkpoint until the Checkpoint manager thread relinquishes control to a cleaner. So if you have checkpoints ocurring when only a small number of buffers are dirty (for us that is always) to speed checkpoint times and a large buffer cache a large percentage of running queries (because lower number VPs tend to do the bulk of the work and also if you have only one listener all queries) will freeze at the beginning of a checkpoint while the checkpoint manager thread scans the cache collecting and sorting dirty buffers. If your buffer pool is smaller (the scan is faster) or dirtier (the manager launches a cleaner and relinquishes the VP as soon as it has collected enough, 8? 16?, buffers from a single chunk). Just an FYI in case you also see unexplained slowdowns during checkpoints. I have a feature request in to force a redesign so this does not happen. Anyone who wants to add their voice to the feature request is welcome, reference case #658239 from Bloomberg LP. Art S. Kagel