Re: Logical Logs -Explain

Mark Collins wrote: > > > Regarding Flushing of logs/buffers to disk > > 1)What is a FOREGROUND WRITE? > A foreground write is a very bad thing for performance. Basically, there are three ways > for modified pages to be written back to disk from the buffer cache: > 1. Chunk writes > 2. LRU writes > 3. Foreground writes > Chunk writes occur at checkpoint time. All of the modified pages are queued up in > page-number order and written in ascending order to minimize back and forth seeking of the > disk read/write head. > LRU writes occur between checkpoints, depending on the values of LRU_MAX_DIRTY and > LRU_MIN_DIRTY onconfig parameters. The least recently used dirty pages are written back to > disk in order of age within LRU queue, so there can be a lot of head seeks. > Foreground writes happen when a user thread needs a buffer to read a page from disk but > there are no free pages in the buffer. The user thread will interrupt its normal > processing and find a buffer that isn't locked by another thread, then write that buffer > back to disk, then it will resume the read operation and bring the page it was fetching > into the buffer it just freed. Almost, Mark. It would be more accurate to say that a foreground write happens when an application thread needs a buffer to write into and cannot get access to one after a specified number of tries. What happens is that a thread that needs a buffer randomly (actually using a hash algorithm) selects one of the LRU queues and tries a number of times, spinning between, to get a lock on that LRU queue. If it still has not gotten the LRU queue's lock, or if the queue has not clean buffers, it will rehash to another LRU queue. This rehash is tried a number of times, but, following W.C. Fields suggestion, eventually it gives up, picks the least recently used dirty buffer from its currently selected LRU queue pair (an LRU queue pair is one clean buffer list and one dirty buffer list) and your sqlexec thread cleans it itself by writing it out, marking it clean and passing it to the normal I/O mechanism to be refilled. This is bad for performance mainly because it is a synchronous write so the CPU VP is tied up by the I/O request, as is your query, and no other thread can execute on that VP either. Art S. Kagel