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: Tuning the Staging DB
Continuing with the stagingprop performance series, in this post I will discuss performance tuning on the staging database. In particular, I will discuss tuning the queries to find the data to be propagating, and DBCleaning the STAGLOG table.
Slow STAGLOG queries
To fetch the data to propagate, stagingprop joins the STAGLOG table with the actual table that contains the data to be propagated (ATTR,CATENTRY, etc). This is one of the main queries used by stagingprop in the staging database. If the query is slow, it can have a considerable impact on the duration of the propagation.
After tuning the -transaction parameter, If the query is slow, you will notice big time gaps in the stagingprop log, as follows:
20160517-062622| Attempting propagation of up to 25000 rows
Using a db2report from the staging database, the query will likely show as the most expensive query under "Execution Time". In the following screen shot, you can see that the query ran 4 times and it took 2.5 minutes to execute each time ( 610,356 milliseconds / 4 ):
The query does fetch a lot of data, and it is executing to be expensive, but it shouldn't take more than a number of seconds. In some cases, I've noticed that an existing index is not used and the database uses a non-optimal execution plan.
CREATE INDEX I0001538 ON STAGLOG (STGTABLE,STGPROCESSED,STGRFNBR,STGSTMP);
After creating the index, you might want to get an explain plan to prove the index is used during query execution. See this blog post for instructions on how to get an explain: Tips for Troubleshooting Slow Queries (DB2).
DBClean the STAGLOG table
The STAGLOG database table in staging records all data changes, and as such it can grow quickly. A large table impacts the queries Stagingprop uses to find the data to propagate and slows down the process.
DBClean the STAGLOG table frequently, such as after every successfully propagation.
The query used by DBCLean to find the records to remove is as follows:
DELETE FROM STAGLOG WHERE STGPROCESSED = 1 AND STGSTMP <= (CURRENT TIMESTAMP - (? DAYS))
Although this delete should be fine in most cases, remember that depending on the actionOnError option used, stagingprop can flag certain records with a negative STGPROCESSED value and continue. This values might need manual intervention and will not be deleted by DBClean.
Use this query to find the number of STAGLOG rows with negative STGPROCESSED. If you decide no action needs to be taken, you can manually remove those records.
SELECT STGPROCESSED, COUNT(*) TOTAL FROM STAGLOG GROUP BY STGPROCESSED ORDER BY 2 DESC
For reference, the values of the STGPROCESSED are as follows: