This blog promotes knowledge sharing through experience and collaboration. For more product information, visit our WebSphere Commerce CSE page. For easier navigation, utilize the Categories to find posts that match your interest.
Stagingprop Performance: The Live Site
Managing the impact of the propagation on the live site can sometimes be as important as ensuring that the process completes during the allocated window. There are a number of steps that can be taken that should help you reduce or mitigate the impact:
Lock contention between staging and the storefront
As stagingprop runs, it updates tables and holds locks on rows that might be being read from the storefront. If two connections attempt to read and write the same data, the database might force one connection to wait.
Storefront queries not using currently committed
The currently committed feature applies to queries running with the CS isolation level. If you have this feature and still see locking, it might be because the query is running with the higher RS isolation level.
If the queries involved in locking are running with RS, and they are not queries that were explicitly set to run in that mode ( they don't add WITH RS), then you might benefit from changing the default isolation level for the WebSphere Commerce datasource from RS to CS by following this techonote: Changing the default isolation level using webSphereDefaultIsolationLevel.
The steps are as follows:
Finally, note that although currently committed replaces registries variables such as DB2_EVALUNCOMMITTED, it is still a best practice to have the group registry variable DB2_WORKLOAD=WC set (this is the default).
Use of caching during propagations
Using a CDN / edge caching is ideal. You can clear the cache once the propagation completes.
If you are not using a CDN, and as most customers you use invalidation triggers, when stagingprop commits changes on the production database, triggers records the necessary invalidations on the CACHEIVL table which are executed by the DynaCacheInvalidation job the next time it runs. If store pages are invalidated while stagingprop is still running, this can increase the chances of waits and contention.
DB2 configuration tuning
Next I discuss two tuning configurations relevant to staging propagations:
Avoiding error SQL0964C with big transaction sizes
It's not uncommon for untuned databases to see error SQL0964C - Transaction log for database is full during large propagations. Before trying larger transaction sizes, validate the logfilsiz, logprimary and logsecond settings. Estimating these values up front is not an easy task, but you can monitor and adjust as necessary. The transaction log can be monitored with db2pd. The db2report tool makes available the output of MON_GET_TRANSACTION_LOG in the tranlog Excel sheet.
Improving performance of currently committed look ups
The currently committed feature performs better when the values are found in the transaction log buffer instead of disk. review the log buffer size (logbufsz) and the heap size (dbheap), which needs to be set to AUTOMATIC or larger than logbufsz. The ratio of the transaction logs disk and buffer reads can be found in the db2report, tool under Locking and Performance > Transaction Logs. For more details see: DB2 Currently Committed and Why It is a Big Deal.