How to detect when total BLOB column sizes in an a single HTTP interaction exceed a threshold (PEGA0004 alert)

Values of the Storage Stream column (pzPVStream) may require a large number of bytes of storage, and extracting or updating a property value from this column requires that the entire value be transmitted from the PegaRULES database to the server. A PEGA0004 alert is triggered when, in a single HTTP interaction, the total size of BLOBs queried or written to the PegaRULES database exceed a per-interaction size threshold. The alert can take the form of a warning or an error. Monitoring this database activity helps you recognize when interactions are inefficiently designed or when data is being loaded indiscriminately.

Note that the alert monitors the total size at the interaction level, not at the query level. The threshold is cumulative across all reads and writes for one interaction. For example, if one interaction has one query that returns a BLOB of 60MB (assuming a 50MB threshold), it triggers an alert. The alert is also triggered if the interaction has 60 queries that each return 1MB.

The PEGA0039 alert, on the other hand, monitors the size of a single BLOB for one query. For instance, if a query with a BLOB size is 4MB (greater than the 3MB threshold), it triggers the PEGA0039 alert but not the PEGA0004 alert. Therefore, if one interaction contains ten 4MB queries, ten PEGA0004 alerts are triggered but there is no PEGA0039 alert. An interaction with one query of 60 MB will trigger both alerts. In general, PEGA0004 alerts indicate systemic issues, whereas PEGA0039 alerts point to individual database transactions that are inefficient or poorly designed.

Alert message description

Here is an example of a PEGA0004 alert warning.

The number of database bytes input for this interaction has exceeded the "warning" level for Requestor HF940FD8761E5A3A67F32F892CD68954B, operator the [email protected], Maximum bytes: 100 Actual bytes: 600

Two interactionByteThreshold settings can appear in the prconfig.xml file, WarnMB and ErrorMB, that either convey a warning, or halt processing when the thresholds are exceeded.

To disable this alert for a single interaction, add this first Java step to the activity: tools.getRequestor().setIgnoreDBByteGovernor(true);

Data returned from columns other than the pzPVStream column does not count towards the threshold limits.

During upload of a ZIP archive, this alert setting is bypassed by design. BUG-4159 BUG-4637

Building initial indexes for the full-text search facility naturally requires reading every rule, data instance, or work object. In some cases, a PEGA0004 alert threshold (alerts/database/interactionByteThreshold/errorMB) can cause the indexing process to fail before completing. Before building initial indexes on a new installation, make sure no value is set for the errorMB setting. REALLY? SR-18642

Changing defaults

Edit the prconfig.xml file as follows:

Toggles the alert on ("true") or off ("false").

When WarnMB is exceeded, the system generates an alert to the Alert log file. Processing does not stop. Set value="0" for the errorMB element or warnMB to indicate no limit. GENTJ 3/27/06SR-4981 B-22125 not warnmb If you omit the warnMB line, the default value for warnMB is 50MB. GUESS 5.2

When ErrorMB is exceeded, the query stops the service requestor and displays an error message and a stack trace in the user interface and writes them to the Alert log.

By default, this entry is disabled with a value of -1 MB. As a best practice, enable this setting during testing and troubleshooting to prevent runaway, unbounded queries from consuming all available memory and crashing the system.

env name="alerts/database/interactionbytethreshold/warntraceback" value="false" />

Displays stack trace information in the log file when the warnMB threshold is exceeded. Set to "true" to enable the setting.

Undeploy and redeploy the server to make the prconfig.xml file changes effective.

Monitor the Alert log for PEGA0004 alerts. Each PEGA004 alert line contains values for three performance statistics, identified by a property name (in the Log-Usage class). These may aid in diagnosing and addressing the issue: PROJ-505

pxListRowWithoutStreamCount — Number of rows retrieved from the PegaRULES database that contained only properties exposed as columns, and no values from the Storage Stream (BLOB) column.
pxListRowWithUnfilteredStreamCount — Number of entire rows retrieved from the PegaRULES database, containing all exposed columns and the Storage Stream (BLOB) column.
pxListRowWithFilteredStreamCount — Number of rows retrieved from the PegaRULES database that included some properties from the Storage Stream (BLOB) column.

Monitor the Pega log for stack trace exceptions from the class:

com.pega.pegarules.engine.database.DatabaseImpl

If the amount of data returned from Storage Stream elements (after any needed decompression of the Storage Stream) exceeds the warnMB limit, the entry has an ALERT severity and appears in the Alert log, with a Java stack trace.

If the errorMB limit is exceeded, the entry has an ERROR severity, and the activity containing the database request ends.

Each log entry identifies the Requestor ID, the operator, and the currently executing activity.

Enhancing the application

Where possible, research reported exceptions and modify the application rules to eliminate them. These strategies may be appropriate, depending on the situation:

Modify the report so that it retrieves only information from exposed columns, and does not need the blob column.
Add more criteria.
Reduce the size of the expanded Storage Streams. Cap the rows returned and/or reduce the number of data columns returned.
Reduce the size of property values.
Redesign the class to split values into two or more classes, if analysis shows this is beneficial. CLINB 3/13/06

Using the alert in development and production

In a development environment, such monitoring allows you to identify and isolate those database operations that retrieve a large volume of data that does not reside in exposed properties. Such operations affect JVM memory demand and may affect overall system performance.
In a production setting, disable these facilities, or enable them only for short intervals of focused troubleshooting. Monitoring is somewhat costly in terms of system resources even when few measurements exceed the threshold values. Also, the errorMB setting terminates the activity, which can affect application results. GENTJ 4.2SP5

PDN Articles

For additional examples and explanation of these facilities, consult the Pega Developer Network articles:

PRKB-25335 Understanding the PEGA0004 Alert
PRKB-25362 Understanding the PEGA0025 Alert
PRKB-23116 How to detect and remedy the performance impact of Obj-List methods and List View reportswas PRKB-19879

	exposed property, Pega log, PegaRULES database, Storage Stream
	How to detect lengthy PegaRULES database operations Working with the PegaRULES database How to detect that size of a BLOB column read from exceeds a threshold (PEGA0039 alert) How to detect when size of a BLOB column saved exceeds a threshold (PEGA0040 alert) Understanding alerts

SysAdmin category