Large LOB Segment impacting DB Performance
In a partner-led performance test, we are seeing the PZPVSTREAM column for the table, PCA_WORK, grow to almost 10 GB after 3 hours of running the test for simulating 6,000 users.
The AWR Report for the DB is indicating a large amount of Buffer Busy Wait signals as well as direct path writes. Here is an excerpt from the AWR report showing the Buffer Busy Wait signals:
The entire AWR Report is also attached (html file).
I realize that PZPVSTREAM column will likely grow to be very large in such a performance test, but 10 GB sounds like a little much. Are there any database tuning measures that should be implemented in this case?
The partner did find a document on the PDN (https://pdn.pega.com/documents/db-lob-sizing-v54) which suggested that they create a separate tablespace for the LOB segment with a larger block size and to set the chunk size equal to the average BLOB size. The document applies to version 5.4 and I was not sure if it is still applicable to Pega 7.
In a partner-led performance test, we are seeing the PZPVSTREAM column for the table, PCA_WORK, grow to almost 10 GB after 3 hours of running the test for simulating 6,000 users.
The AWR Report for the DB is indicating a large amount of Buffer Busy Wait signals as well as direct path writes. Here is an excerpt from the AWR report showing the Buffer Busy Wait signals:
The entire AWR Report is also attached (html file).
I realize that PZPVSTREAM column will likely grow to be very large in such a performance test, but 10 GB sounds like a little much. Are there any database tuning measures that should be implemented in this case?
The partner did find a document on the PDN (https://pdn.pega.com/documents/db-lob-sizing-v54) which suggested that they create a separate tablespace for the LOB segment with a larger block size and to set the chunk size equal to the average BLOB size. The document applies to version 5.4 and I was not sure if it is still applicable to Pega 7.
Does anyone have experience with LOB sizing that can chime in?
Thanks in advance.
***Updated by moderator: Lochan to close post***
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Pegasystems Inc.
GB
Hi Jai,
For general database performance issue, the below PDN article is a good starting point:
https://pdn.pega.com/performance/performance-guidance-for-production-applications-database
Regarding the BLOB Size, there is an option to compress the BLOB Columns:
https://community.pega.com/sites/default/files/help_v718/procomhelpmain.htm
Extract:
Values of properties with a mode other than Single Value (such as mode Page or Page List) are stored in a special column named pzPVStream. This column is known as the Storage Stream. Typically, this column has a BLOB (Binary Large Object) data type.
The size of the Storage Stream values can have a significant impact on memory demand, network traffic, and overall performance. To help you detect and address issues related to BLOB size, multiple types of alerts (PEGA0004, PEGA0025, PEGA0039, and PEGA0040) monitor database activity and report exceptional conditions involving Storage Stream values.
Hi Jai,
For general database performance issue, the below PDN article is a good starting point:
https://pdn.pega.com/performance/performance-guidance-for-production-applications-database
Regarding the BLOB Size, there is an option to compress the BLOB Columns:
https://community.pega.com/sites/default/files/help_v718/procomhelpmain.htm
Extract:
Values of properties with a mode other than Single Value (such as mode Page or Page List) are stored in a special column named pzPVStream. This column is known as the Storage Stream. Typically, this column has a BLOB (Binary Large Object) data type.
The size of the Storage Stream values can have a significant impact on memory demand, network traffic, and overall performance. To help you detect and address issues related to BLOB size, multiple types of alerts (PEGA0004, PEGA0025, PEGA0039, and PEGA0040) monitor database activity and report exceptional conditions involving Storage Stream values.
You can set up data compression for this BLOB column using the DeflateStreams setting in the prconfig.xml file or Dynamic System Settings. When enabled, compression and decompression occur on the Pega 7 server and uses the java.util.ZIP class. The PDN article How to compress BLOB values in the PegaRULES database presents this feature for Version 4; the capability is similar for Version 5 using the prconfig.xml syntax.
Hope this help
Simon
