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.
DB Must-Do's: Review DB2 Settings to Avoid Latching and Performance Problems
SQL statements may execute with reasonable performance in isolation but may show signs of slowness when executed under load or concurrently (possibly latch contention) there is latch contention. Latches are internal DB2 locks used to serialize access to shared data structures, and can be identified by using db2pd -latches command. For more information refer to Identifying Bufferpool Latch Contention.
This blog has a list of settings that have been identified from experience to prevent latching and performance problems when using WebSphere Commerce with DB2. You may also notice that most of the information is relevant to earlier versions of DB2. This is why the general rule is to have DB2 at the latest version and fix pack level as there are constant enhancements in the areas mentioned in this blog.
The following cover advanced topics in DB2. If you are having problems identifying if any of the following apply to your database, contact
DB2 latching's implementation uses AIX thread_lock() / thread_unlock() APIs;. Under heavy workloads where hundreds of threads are waiting on the latch or lock the CPU may spike impacting DB2's performance (primarily the @sys CPU)
If you're running DB2 9.7 on AIX, you must set the DB2_ENABLE_THREAD_UNLOCK_EXTENDED variable that is enabled by DB2 9.7 APAR IC79285 (which is included in DB2 9.7 Fix Pack 6 or above).
To enable this APAR fix on db2, the following db2 registry needs to be set:
To ensure that the AIX part of the fix is present for DB2 use this fix, and make sure that DB2 is running on either of the following AIX levels:
Refer to APAR IC79285 for specific details and instructions.
When experiencing CPU spike (%sys) with latch contention on SMempool_latch, and APAR IC79285 is applied, check if the SORTHEAP value is controlled by STMM. If it is and it is very volatile (grep "STMM CFG" db2diag.log), then follow the steps below.
If STMM is turned on and you are on DB2 9.5 and 9.7, take SORTHEAP out of STMM and use the following recommended values.
SORTHEAP = 50,000
SHEAPTHRES_SHR = 2,000,000
The parameters are dynamic, but still its suggested to recycle the DB2 instance to get rid of the existing memory fragmentation.
To avoid workspace_lru_latch on DB2 9.5, set:
This is a setting where the cache effect of the SQL Workspace is increased by 500% and the interval of workspace clean up is increased to 5 minutes (default is 1 minute). For very large environment you may need to increase it to its maximum value of 2000.
If you are on DB2 9.7 or 10.1, use the following
Note that the above values are required only for high query throughput and the changes require restarting the database.
There have been design changes in DB2 10.5 that eliminate this issue.
For more information on how to identify workspace_lru_latch refer to Resolve contention on "workspace_lru_latch".
Highly performing WebSphere Commerce environments using multiple JVM's may show signs of LHSH_xhshlatch contention (identified by the output of db2pd -latches).
If you are on DB2 10.1 the regvar DB2_APM_PERFORMANCE=17 needs to be set in order to get past this latch contention.
If you are on DB2 9.5 or 9.7, we recommend multiple collection sets to reduce contention. This can happen due to either one of the following two reasons.
Rule of thumb on number of collections
Depending on the number of JVMs the suggested number of collections are 1 collection for 25 to 30 JVMs. However, this is a suggestion and will still require monitoring the impact of the number of collections on the system for some time.
Implementing Multiple Collections
/opt/IBM/db2/V9.5/java/jdk64/bin/java com.ibm.db2.jcc.DB2Binder -url jdbc:db2://<db server>:<port>/sample -user xxx -password yyy -collection APPS1
/opt/IBM/db2/V9.5/java/jdk64/bin/java com.ibm.db2.jcc.DB2Binder -url jdbc:db2://<db server>:<port>/sample -user xxx -password yyy -collection APPS2
/opt/IBM/db2/V9.5/java/jdk64/bin/java com.ibm.db2.jcc.DB2Binder -url jdbc:db2://<db server>:<port>/sample -user xxx -password yyy -collection APPS3
/opt/IBM/db2/V9.5/java/jdk64/bin/java com.ibm.db2.jcc.DB2Binder -url jdbc:db2://<db server>:<port>/sample -user xxx -password yyy -collection APPS10
This will affect only SQLJ/JDBC packages in the database.
Go to the WAS admin console, and under the Custom Properties of the datasource (Data Source > [datasource] > Custom Properties) add the following name-value pair.
Type 2 driver (for WebSphere Commerce v6)
Type 4 driver (for WebSphere Commerce v7)
Set this property on the JVM containers where you want to use the same collection.
Validating the changes
The CLI locks have names prefixed with 5359534C564C3031 ( db2pd -locks output ) and the next 4 bytes have got the collection name. You should see multiple unique locks in the db2pd -locks output. However, in order to confirm that multiple collections are at work, take a db2pd -static -db <dbname> output and in the Packages section you should see something as follows.
Address Schema PkgName Version UniqueID NumSec UseCount NumRef Iso QOpt Blk Lockname
0x0700000527CFF760 CHASVT SYSSN200 SYSLVL01 0 0 189 CS 5 B 5359534C564C30314B0D0348C1
0x0700000527C10A60 CHASVT SYSSH200 SYSLVL01 0 0 70 CS 5 B 5359534C564C303128EFECDCC1
0x0700000527C10080 NULLID SQLDEFLT Default Pa CONTOKN1 0 0 573 UR 5 U 434F4E544F4B4E310763DD28C1
0x0700000527E3C0A0 CHASVT SYSSN200 SYSLVL01 0 0 171 CS 5 B 5359534C564C303156E9CA0EC1
0x0700000527CFE920 CHASVT SQLL9I1L AAAAAZIZ 0 0 425 UR 5 B 41414141415A495A2F7B1128C1
0x0700000527E3FC80 CHASVT SYSSN300 SYSLVL01 0 0 70 RS 5 B 5359534C564C3031976715CEC1
0x0700000527C1E2A0 CHASVT SYSLH202 SYSLVL01 0 0 6 CS 5 B 5359534C564C3031956EB7B4C1
If you see the same schema name/package name with different lock name, then you know mutliple collections are working.
When anchor_stmt_latch contention is observed, that means a statement or a group of statements which are non-parameterized are flooding the package. Consequently, it drives the contention up. The resolution is to identify the statement and fix it using parameter markers.
For more information on identifying anchor_stmt_latch refer to Identifying Bufferpool Latch Contention.
In the short term you can use the following workaround.
db2 "FLUSH PACKAGE CACHE DYNAMIC"
However, this has performance implications as it will clear the whole package cache including all other statements that are not affecting the high contention.
Note that you can attempt using the above call but there is not guarantee it will fix the problem. You will have to determine the frequency of issuing the above statement based on how quickly the statements are flooding the package cache.
Another workaround, would be to use statement concentrator.
Fore more information on using parameter markers and statement concentrator refer to DB Must-Do's: Ensure Queries are Parameterized.