What is the -pica database startup parameter used for?OpenEdge Replication uses the -pica queue.
The -pica value is used to specify the amount of storage in KBytes allocated for inter-process communications between the Replication Server (RPLS) and Replication Agent (RPLA). The communications area allocated is utilized to queue messages as AI blocks fill on the source database to be sent over TCP/IP to the listening RPLA process, often referred to as the
RPLS-Q. A finite number of IPC message can fit into the memory allocated by setting -pica and the maximum number of message varies by version.
The minimum value for -pica is 4.
The maximum value for -pica varies by Progress Version:
- 256 (9.1E, 10.0B, 10.1A),
- 1024 (9.1E04),
- 512 (10.0B05),
- 8192 (10.1A02, 10.1B01),
- 1000000 (10.2B08,11.2, and 11.3)
For further information refer to Article Clarification of the -pica replication enabled database startup parameter
Calculating the -pica value for site specific Replication
When the RPLS-Q fills, source database activity stalls. For example due to heavy writes on the source database when replication communication over the network cannot keep up with processing these AI blocks to the target database(s).
The following information can be used to estimate the -pica value that will be required for OpenEdge Replication to run optimally during periods of high transaction activity.
1. Gather sample metrics by taking PROMON snapshots:
PROMON > R&D > Option 2 - Activity Displays > Option 6 - AI Log
2. Capture AI Writes metrics over the busiest period of database activity (Creates, Updates, Deletes)
As with any performant metric gathering, sampling AI Writes can be taken in a variety of interval formats. The first method is the preferred method as the calculated pica value compensates for Replication Bandwidth shortfalls.
a) Capture the AI writes for the complete time of a known busy sample period, then record these as the Total AI Writes (TAIW) for the total time period.
For example, if the busy period for end of day processing typically lasts 1 hour. Capture the AI Writes for the complete hour over several days. Use the mean average of AI Writes metrics gathered as the Total AI Writes (TAIW) to determine the optimal pica queue.
b) Capture smaller samples over a set sample period within known busy or high write activity periods.
For example, if the busy period lasts 1 hour every day, capture a 10 minute sample at the beginning, middle, and end of the busy period over several days. Add these values up and divide by the appropriate number of 10 minutes samples collected. Then record these as the Total AI Writes (TAIW) to determine the optimal pica queue.
3. Once the Total AI writes (TAIW) metrics are available, use the following formula:
-pica = (TAIW / BlockCount) * 1.25
Where:
- TAIW: Total AI Writes
- Block Count: Block count for OpenEdge Version (Progress 9 = 18.2, OpenEdge 10, 11 = 9.16)
Sample Table: Optimium pica value for an hour TAIW sampling period.
|
TAIW Value
|
OpenEdge Version
|
Block Count
|
Multiplier
|
-pica value (*)
|
|
5,000
|
10 | 11 | 12
|
9.16
|
1.25
|
682
|
|
20,000
|
10 | 11 | 12
|
9.16
|
1.25
|
2,729
|
|
100,000
|
10 | 11 | 12
|
9.16
|
1.25
|
13,646
|
| | | | | |
|
5,000
|
9
|
18.2
|
1.25
|
343
|
|
20,000
|
9
|
18.2
|
1.25
|
1,374
|
|
100,000
|
9
|
18.2
|
1.25
|
6,868
|
(*) Notes
- This is the pica buffer value that must be added to the source database startup parameters. It is the RPLS-Q buffer size needed to accommodate the period of high transaction activity in the application environment without leading to the queue being filled and thereby stalling source database activity.
- This formula does not take into account that the Replication Server will be removing blocks from the RPLS-Q queue during realtime processing which would lower the real size of the pointers to filled AI blocks needed in the pica buffer. This information is not available and would be over-engineering the intention of the exercise.
- This formula does not consider the Replication Bandwidth required which accelerates or throttles the freeing up of pointers in the RPLS-Q depending on the transfer rate of the filled AI blocks from the RPLS to the RPLA over the TCP/IP network layers, for the RPLA to apply to the target database. Refer to Articles:
00011663, How to calculate the bandwidth requirements for OpenEdge Replication
How to monitor the message replication queue set with the -pica parameter. - This estimated value may be limited by the maximum allowed value of -pica based on the OpenEdge Replication version (see above).
For example, 13,646 is the ideal value for 100,000 Total AI Writes in OpenEdge 10/11, however the maximum allowed value is 8192 prior OpenEdge 10.2B08, 11.2. - Skipping this exercise and simply setting the -pica value to the highest value the OpenEdge version allows, specifically since it was raised to 1 million KB in 10.2B08, 11.2, is not advisable. The larger the Replication Server Queue (ie -pica), the less synchronised the target database(s) will be in the event of source database/machine failure, as there are more AI block(s) in transit between the source (RPLS) and the target (RPLA) at the time which need to be reconciled during the synchronization process once the databases are restarted and replication communications resume.
- AI performance needs to be tuned and sufficient ai file space available to avoid running out of ai files to write transaction notes:
How to determine the amount of AI file space required?
How to improve After Imaging (AI) performance - Contention with replication server over PICA queue has been further enhanced in OpenEdge 11.7.5, 12.0. For further information refer to Article:
Significant batch job performance drop once database enable replication
