Re: Checkpoint Stall problem?

Michael Mueller wrote: > This is quite incorrect and very misleading. There is a problem only for > some huge systems and it is not as dramatic as this article sounds. The > tunig tips are also wrong. As far as I have heard it will be fixed in > 9.40.UC7 and 10.0.UC2. We're talking about different problems Michael. Colin may indeed be seeing the problem you describe, but the one he quotes (which is from me) is no longer a problem because I proved its existence to the IDS development team and they redesigned the checkpoint process. BTW, you are just wrong about certain details, see below: > There is a problem at checkpoint time for online systems with huge > buffer pools, something like > 8GB of buffers. > > Systems with "more than 500 dirty buffers at checkpoint time" are NOT > affected at all if they don't have a huge buffer pool. That was a configurable threshhold (environment variable to adjust) built in to versions through 7.30. > In those systems that are affected a poll or sql listener thread on cpu > 1 cannot run while the checkpoint code is collecting all dirty pages. Exactly. This was supposed to have been fixed! Is it still there. > This lasts for maybe a few seconds with 8GB of buffers (depending on cpu Actually it average 1/2 of the checkpoint duration. > speed) and increases linearly with buffer pool size. If there is no poll > thread on cpu vp 1, connected clients are still not affected (They DO > migrate to other vps!). If there is a sql listener waiting, new > connections must wait for that time. > > The problem will NOT go away by tuning LRU_MAXDIRTY, etc.It happens even > with 0 dirty buffers. > > This is what happens: At checkpoint time the main_loop thread loops > through the whole buffer pool inspecting the DIRTY flag in every page's Makes no sense. Each LRU has a separate DIRTY queue and CLEAN queue. The checkpoint code only has to collect every buffer registered in a dirty queue with a timestamp earlier than the checkpoint. The size of the total buffer pool should not affect the checkpoint duration. If they recoded that way they should be shot. I KNOW it was not coded that way through 7.30 because I had hours of dicussions with the guys who were maintaining that code to try to help them duplicate the problem. > buffer header and collects a list of dirty pages in memory which is > later used by the flushers. This is done in a single thread and without > yielding. No other thread can run on cpu 1 for that time. When the list True. I asked them to just shift the checkpoint thread to the ADM VP and the problem goes away. Alternatively I suggested migrating all threads in CPU VP #1 to other VPs before starting the checkpoint. Did they listen to me? No. > of dirty pages is collected the problem is over. This is BEFORE the > first flusher even starts doing anything. This is done twice per > checkpoint. There are two flushes. Never heard of two flushes. Where is this documented? > This algorithm has been about like this even in old turbo (version 5) Yes, but in TURBO each user had his/her own copy of sqlturbo so when the coordinator process (sqlinit? don't remember anymore) performed the checkpoint no users were affected by a block. > times and it has never been a problem until recently when customers can > afford to configure buffer pools in the GB range. What used to take > milliseconds even in systems that were huge at the time can now take a > couple of seconds. It blocks cpu vp 1 and it increases checkpoint times. > > The fix will go through the dirty LRU queues to collect all dirty pages > if less than 1% of all buffers are dirty. This is much faster. > > Michael Art S. Kagel > > Colin Dawson wrote: > >> Whilst searching for information on DS_HASHSIZE, I found the >> following, I went in search of the results and summary on CDI but >> couldn't find it. Two questions arise: 1) Which versions does this >> apply to? and 2) Does anyone know where the results are located? A >> search on CDI archive for Checkpoint Stall returned 54 pages of >> results!!!!!!! >><SNIP>