Asynchronous Page Writers (APW's) are background processes whose job is to write updated database blocks to disk as needed so that servers do not have to take the time to do these writes. This gives them more time to do useful work on behalf of clients.
Page writers do three things and their priority is as listed below, from highest to lowest:
1. Clean buffers on the APW Queue
The highest priority function of the page writer processes is to write any buffers that are on the APW queue as soon as possible after they have been queued, and then put them back on the LRU chain as the oldest buffers.
Dirty buffers are placed on the APW queue when some process is looking for a buffer to read a new block into and finds dirty buffers on the old end of the LRU chain. By removing them from the LRU chain and placing them on the APW queue, the next process that needs a buffer to read into will not see them.
The entire APW queue is cleaned out when there are buffers queued. The benefits of doing this are:
- The LRU chain is less likely to become clogged with very old modified buffers when there is a heavy load on the database. The search time for finding an unlocked unmodified buffer is reduced.
- Contention for the LRU chain lock is reduced because of the reduced search time.
The startup parameter -pwqdelay determines how often the APW queue is checked, in milliseconds. The default is 100 milliseconds.
2. Clean buffers that were marked for checkpoint
Write buffers from the checkpoint queue to disk, scheduling the writes at a rate that will cause the queue to become empty at the same time the current bi cluster becomes 95 % full. Thus, when the cluster is closed and a checkpoint ends, there will be no scheduled buffers left to write. The number of buffers that need to be written, and the pace, are computed automatically and cannot be controlled through startup parameters.
The benefits of doing this are:
Checkpoints can be performed asynchronously in the background in an orderly fashion and they will nearly always complete in the required time.
Database write operations are spread out over time, reducing their impact on disk readers.
The startup parameter -pwsdelay controls how often the checkpoint queue is looked at, in seconds. The default is 1 second.
3. Clean old modified buffers
If there is nothing else to do, the page writers will examine (scan) a few buffers in the buffer pool and write out any that have been modified. This way when the system is idle or lightly loaded, modified buffers will slowly be written to disk in the background.
The startup parameter -pwsdelay controls how often part of the buffer pool is scanned, in seconds. The scan operation is performed only if there were no buffers written from the checkpoint queue. The default is 1 second.
The startup parameter -pwscan controls how many buffers are examined in one scan. The default is computed such that the entire buffer pool will be examined once every 10 minutes, by all the APWs working together.
The startup parameter -pwwmax limits the number of buffers that can be written in one scan. The default is 25, distributed among all the APW's
Any time an APW writes a buffer to disk for one of the three reasons above, it must first make sure that all bi notes recording the changes made to that modified the buffer have been written to disk first. If they have not, the page writer may write the bi buffers as well. They do not have to wait for the BIW to do it.
When there is nothing else to do, APW's sleep for -pwqdelay milliseconds.
Several parameters to control the operation of the page writers have been mentioned above. You should not have to change their values from the defaults because the page writers pace themselves as described in point 2 above.
We have ample evidence that shows that the default values give correct behaviour and that there is NO reason to override them. The parameters are still there just in case, but we recommend you do not use them at all unless you are instructed to do so by the Database development team. They should not give you such instructions.
The choice of how many APW's to start is the only thing you have to worry about and you choose that by starting with a small number (1 or 2). Then let the system run for awhile and look at the Checkpoint display in promon. It shows what happened during the last 8 checkpoints.
If you are using raw extents of direct i/o, then you should start with one page writer per disk rather than with 1 or 2 page writers.
See if the number of buffers flushed (the rightmost column) is consistently 0 or close to zero. If it is, you have enough APW's and they are keeping up with the load. If you see 1 digit numbers, you are close to the edge and should be happy. If you see higher numbers, then start another APW to see if it is enough.
The buffers flushed column indicates if any buffers were NOT written in the background during the asynchronous checkpoint. When a checkpoint ends (which happens at the same time that a cluster fills), any buffers left on the checkpoint queue must be written immediately. NO database changes can occur until those writes have been completed. This is because there is no space available to write additional bi notes into until the next cluster is opened.
Having buffers left to write at the end of a checkpoint can only happen if the APW's cannot do all the scheduled writes. There are 3 major causes:
0. You are not using APW's in the first place.
1. The bi cluster size is too small. Checkpoints might occur so close together that the APW's don't have enough time to do their work.
2. You don't have enough APW's to do the required i/o's.
3. The disk subsystem can't sustain the required i/o rate. For example, a database stored on a single disk is likely to suffer from this problem under heavy load.
Copyright 1997, Progress Software Corp., All Rights Reserved
