DB2 Insert Performance due to FSCR and how to identify that
Biswarup(Bis)Mukherjee 120000HKTY Visits (8774)
It's well documented that one of the major reasons for DB2's slow insert performance is how the Free Space Cache Record (FSCR) are searched before a record is inserted.
This Technote explains it very well,
Also, a nice description in the DB2's Knowledge Center,
But, how to identify if this FSCR issue is being experienced ?
A collection of db2's stacks will help to identify that the issue could be related to FSCR.
Focusing on a specific INSERT might not help.
That is the reason a wider stack "db2pd -stack all" should be collected and reviewed.
Look for stacks showing up with function sqld
Any stack with sqldSearchPageCache is "normal", and means that we're trying to find free space, likely for an insert. The problem is when lots of agents are in the same stack, then it likely means that they're contending on the actual page that has free space. (ie, many agents trying to insert into the same page). This can be fixed by enabling append mode as pointed out in the documents referred.
Another scenario is similar, but all the agents are spread out over different pages. This is more of an efficiency statement, in that we're constantly scanning for (and not finding) free space, and will end up in temporary append mode.
The following stack indicates a first case -- we've found free space but the page with the free space isn't in memory. Agents will be waiting on the I/O.
0x090000000003AD18 pread64 + 0x38
The following stack is similar to the first, but we are one step behind -- we are stuck trying to find a victim slot to use when we read the page from disk,
0x0900000000110E94 thread_wait + 0x94
These are just potential indicators of "slow insert" - waiting on I/O and waiting on victim slot to read a page that might end up getting used for the insert. And, actions suggested against FCDR documentations above should help.