How Many APW’s

Dear All,

How many APW’s we should have with a database?

Our database size is around 800GB and currently we have 12 APW’s running. As per below Promon activities, writes by APW seems fine but rest values should be lower (I think).

Code:
Writes by APW                  99 %
Buffs Flushed                    345569 /* buffs are flushed in memory and not written to DB or Transaction lost, is this correct?*/
Checkpoints                      82164
Total BI writes                   166981K           4767            79.46           0.34
Busy buffer waits              619140             17                  0.29            0.00
Empty buffer waits           3404067           95                  1.58            0.01
Before-image block size:                              8192 bytes
Before-image cluster size:                           16384 kb (16777216 bytes)
Number of before-image extents:              6
Please help on how many APW’s should we have with a DB.

Regards
 

Cringer

ProgressTalk.com Moderator
Staff member
12 APWs is rather a lot, even for such a big DB. Those Promon numbers are somewhat useless as you don't tell us what timeframe they are for. They look like they're for a large time range though. You'd be better off posting the data from a 5 minute interval during a busy time on the system. Even better, post multiple five minute intervals.
That being said, from my limited experience, your BI blocksize should be 16k, and you will probably benefit from increasing your BI cluster size also.
 

Cringer

ProgressTalk.com Moderator
Staff member
Please also post the Progress version, and the startup params from the DB. Easiest way to get these is to look for (333) in the logfile, and then post the next 75-100 lines or so.
 

TomBascom

Curmudgeon
A million years ago, when “good” disk access was 20ms or so and when a disk device really was **a** disk rather than a LUN made up of who knows how many slices spread over who knows how many devices all at the wrong end of a cable... there was a “rule of thumb” that you should have 1 APW per disk. Times have changed and that thinking has been obsolete for roughly 20 years.

Today’s recommendation is that everyone needs at least 1 APW. **If** you see hundreds or more “buffers flushed at checkpoint” on a regular basis then you might want a second APW.

I don’t know anyone who needs more than 2.

As Cringer says, you may also need to adjust bi block and cluster size and the number of -bibufs.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
/* buffs are flushed in memory and not written to DB or Transaction lost, is this correct?*/
No. The full label here is "Buffers flushed at checkpoint". High numbers here can indicate a database tuning problem or an I/O subsystem that can't keep up with the load on it. But it does not mean data loss.

A checkpoint is the process that takes place from the time one BI cluster is filled until the time the next one is filled. There are different phases within that period of time and different tasks done by database processes within those phases.

One of the jobs done by the APW(s) is to scan the buffer pool for modified ("dirty") buffers and queue them to be written to their location in the data extents, so that the contents of memory are gradually synchronized with the contents on disk. The APW's goal is to have all of the queued buffers written out to disk before the end of the checkpoint (i.e., before the current BI cluster fills to 100%). If the end of the cluster is reached and not all of the queued buffers have been written out already, they must be written at that time; it can't be delayed any longer. These are counted as "buffers flushed at checkpoint". By contrast, if it does succeed in writing all queued buffers prior to the end of the checkpoint, "buffers flushed" is zero for that checkpoint.

(Technically, the buffers aren't being "flushed", or emptied; the contents remain in the buffer. The contents are just being written to disk, and the buffer is no longer marked as modified. For this reason, in later releases (11.6+, I think) you will see the label in promon changed from "Buffers flushed" to "DB writes".)

These labels make more sense when you understand the various memory and disk I/O processes that take place within the database. This is a very good overview of database I/O from Rich Banville of Progress Software, from the 2013 PUG Challenge Americas conference:

slides: http://pugchallenge.org/downloads2013/321_Database_IO.pptx
audio: http://pugchallenge.org/downloads2013/audio/321_Database_IO.mp3
 
Thanks Tom and Cringer all for this information.

Thanks Rob for the clarification and shared slides, it helped.

Regards,
 
Top