Re: onstat -u, B-flag

Alexander Selg wrote: > > Hello All ! > > The command "onstat -u" gives some information about the userthreads. In > the 2.column you can see the flags to each thread. The 1.position > indicates the resource a thread is waiting for. > My question is: if there is a "B" on the 1.position, which means the > thread is waiting for a buffer, which buffer is meant - physical log > buffer, logical log buffer, regular buffers ? > > Background: > I have threads, which make deletes of about 3000 rows in a tables of > 3.000.000 rows. Therefor they need 3 - 10 minutes. There are 2 indexes > on each of these tables and no constraints. With onstat -u i see, that > they are sometimes waiting on buffers; so my idea is that waiting for > buffers makes the threads using so much time - any other ideas ? It looks like you either do not have enough buffers, so that you must wait for some to be flushed in order to get a buffer, or you do not have enough LRU queues so that the various processes/threads are blocking each other from getting at the free buffers that there are. Do you have only a few thousand buffers for example? Run onstat -F. Are there more than a FEW Foreground Writes? This is BAD BAD! It is a definite sign that there needs to be more buffers as your processes are having to flush dirty buffers themselves. I recommend a minimum of 32 LRUs with AT LEAST 16 cleaners (the 5.0x rule of thumb of 1 CLEANER per LRU seems to no longer hold because of the Asynchronous I/O in 7.xx). If you have a large number of buffers you will want to increase LRUs so that there are <=1000 Buffers per LRU if possible (since there are a maximum of 128 LRUs this is not always possible). Also decreasing LRU_MAX_DIRTY and LRU_MIN_DIRTY will keep the LRU queues cleaner so that more buffers are available at any time. If onstat -F reports a majority of the writes are CHUNK WRITES then this will definitely help, I try to keep at least 75% of writes as LRU WRITES. OH OH OH! I just remembered another possibility. Avoid having exactly 64 LRUs there seems to be a bug in the hash used to select the next LRU if the first is locked. It causes excessive bufwaits when there are exactly 64 LRU queues. I solved this by increasing to 128 LRUs and I know another shop that changed to 32 LRUs and the bufwaits problem went away for them also. The bug may be a multipeak problem but I only know of problems at 64 LRUs. Art S. Kagel