Feedback
Did this article resolve your question/issue?

   

Article

What does the Writes By APW percentage value in promon indicate?

« Go Back

Information

 
TitleWhat does the Writes By APW percentage value in promon indicate?
URL Name000045856
Article Number000165496
EnvironmentProduct: OpenEdge
Version: 10.x,11.x
OS: All Supported Operating Systems
Other: APW, performance
Question/Problem Description
How to use the Writes By APW percentage value in PROMON for performance tuning?

Writes By APW percentage metrics access from either:
  • promon -> 5. Activty
  • promon -> R&D -> 2. Activity Displays -> 1. Summary
 
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
The documentation describes the Writes By APW value as being the percentage of database blocks written to disk by the APW; this is a percentage of the total number of database blocks written by the database engine.
 
However the Writes By APW % metric in itself is not overly useful.
 
The storage management and I/O architecture is such that any process (a few utilities excepted) that is connected to a database may find a need to write a block from shared memory to the on-disk data extents. Even a proutil dbanalysis may have to write something. Generally, it is undesirable for a self-serving client or a server acting behalf of a network client to have to write modified database blocks to disk. It is undesirable because we want to do as much useful work as possible on behalf of the clients and the applications they are executing. Even though they can't be avoided and we try to minimize that as much we can, we might consider database writes to be an overhead.
 
The purpose of the APW's is to write modified database blocks back to disk as a background activity, relieving the servers and the self-serving clients of that task. But there are rules for the buffer replacement policy, how writing has to be performed so that we can maintain the so-called "ACID" transaction properties, and so that before-image log space can be reused, and other things. The APW's do not do all the data block writing even when things are operating normally. For example, when a self-serving client needs a data block that is not resident in memory, it has to bring it in from disk. If the buffer it chooses to store the new block has been modified, it may write the modified buffer to disk. When a server has a bi note to write, it may find that the current bi cluster is full and has to be closed so a new one can be opened. This may cause some modified blocks to be written by the server.
 
The best metric to use to determine whether the page writers are being effective is the "Buffers flushed" value that is reported in several places by PROMON and in VST tables. When this number is consistently larger than a few, it means database writes are not keeping up with demand. During the flush operation no database changes or transaction state changes can be performed. When it takes too long, users perceive a temporary "freeze" in their application. One or two writes take just a few milliseconds but thousands can take a minute or more.
 
A problem that production sites sometimes experience is that the storage devices they are using do not have sufficient write bandwidth to handle the load placed on them by the application and the database. So when the disks are 100% busy, or close to it, then the page writers fall behind, resulting in buffers flushed. Adding more page writers will not help in such a case.
 
To sum up, the percentage of writes by APW's by itself is not all that useful because it cannot be used as a predictor of anything. A low number is not always a cause for concern, and a low number does not mean the APWs are not working. At best it is a clue that suggests you should investigate further and look at other things.
 
For further information in determining the number of APWs optimally required, review Article  Basic guide to determine how many APWs are required for a database?   
Workaround
Notes
References to other documentation:

OpenEdge Data Management: Database Administration, Chapter 21; "Promon Utility".

Progress Articles: 

Basic guide to determine how many APWs are required for a database?   
How to decrease Buffers Flushed?   
Database hangs due to excessive bi checkpointing   
Why More APWs are Needed When Using -directio?   
 
Last Modified Date11/20/2020 7:15 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.