IMPROVING TRANSACTION THROUGHPUT THROUGH AVOIDING CFLOCK REGISTRATION AVOIDING LATCHES AVOIDING SLOC

1
IMPROVING TRANSACTION THROUGHPUT THROUGH
AVOIDING CF-LOCK REGISTRATION (?)AVOIDING
LATCHES (?)AVOIDING S-LOCKS (LOCK AVOIDANCE)(!)

2
About FORTIS Bank

• Some DB2 statistics - as of march 2003
• daily IMS trx rate 12.000.000 - 14.000.000
• gt daily IMSDB2 trxs 3.000.000 - 3.500.000
• gt peak IMSDB2 trx rate 100 trx / sec
• over 3500 tables in Production ( 450 GB)
• over 4300 indexes in Production ( 350 GB)
• /- 750.000.000 SQL statements / day
• /- 200.000.000 SQL-statements during online
  hours
• total lock wait time 5 - 6 of total elapsed,
  on 24h base

3
Problem transaction

• Timeouts in branches due to heavy load of one
  transaction, with impact on other transactions
• proprietary branch-protocol based on IMS DEDB
• contention on the DEDB-root occasional timeouts
  for other transactions triggered by the same
  branch
• Trx-goal logging of branch-activity
• up to 400.000 executions/day (higher than
  expected)
• avg elapsed / CPU time 0,12 sec / 0,005 sec
• and heavier load expected in near future

4
Investigation

• Most of the elapsed time of trx in 1 insert
• Only 1 Insert per trx ( logging)
• Fortis in 2-way data sharing the branches are
  equally spread over the 2 IMS systems all
  activity of 1 branch dedicated to 1 IMS
• data inserted on 1 non-partitioned table
• gt table GBP-dependent all the time
• gt all locks registered to the CF-lock
  structures
• gt random insert (cfr index table layout)
• gt space map pages updated by the 2 IMSs

5
Investigation (2)

• Table/index design
• ID_KLANT INTEGER
• ID_USER CHAR(x)
• ID_KANTOOR CHAR(x)
• DATE DATE
• TIME TIME
• TYPE_BERICHT CHAR(x)
• . ..
• Text_field VARCHAR(x)
• max number of rows in the table 3.000.000

6
Solution

• Possible SOLUTIONS
• MEDIUM TERM use of MQSeries (daily upload)
• (MQSeries not available in the branches at time
  of design of the trx)
• SHORT TERM
• add more IMS regions (not a real solution)
• Design Changes - cfr next foil

7
Solution (2)

• Phase 1 Partitioning of the tablespace INSERT
  at the end still GBP-dependent
• if branch behaviour changes (batch-insert instead
  of individual inserts), number of locks could
  strongly be reduced
• gt already very strong improvement in elapsed
• Phase 2 set Membercluster ON (each DB2 his own
  space map) less lock contention detection ?
• gt no noticeable effect on elapsed
• Phase 3 Avoid GBP-dependency - cfr next foil

8
Solution (3)
No inter-DB2 interest on same pages i
VGR
IMS1/ DB21
IMS2/ DB22
1 / 3 / 5 / 7 / 9
part
part
0 / 2 / 4 / 6 / 8
9
Solution (3)

• Phase 3 - continued
• partitioning based on last position of terminalid
• each Partition becomes non-GBP-dependent
• gt locks no longer kept in the CF-structures
• gt No noticeable impact on elapsed, nor on lock
  waits
• Phase 4 set TRACKMOD NO (space map pages no
  longer updated ---gt makes Incremental Image
  Copies imossible) PREFORMAT
• gt No noticeable impact on elapsed

10
Result

• avg elapsed time of Insert
• 0,045 sec -gt 0,008 sec gain of 80
• avg CPU time insert
• 0,0013 sec -gt 0,0009 sec gain of 30
• MAINLY result of inserting at the end no
  noticeable effect of all the other trials
• avoid GBP-dependency CF-lock structures
• avoid latches on the space maps

11
Enforce LOCK Avoidance

• Standard BIND-parameters
• ISOLATION(CS)
• CURRENTDATA(NO)
• makes LOCK avoidance possible (for S-Locks,
  related to Cursors)
• Important that updating BMPs commit frequently,
  even more important in a data sharing environment
• gt How can we enforce checkpointing in BMPs ?
• gt impact of Lock Escalation ?

12
Lock escalation-decision table
(1)
(1) lock escalation can also occur with
locksize page or row
13
Lock Avoidance Strategy

• Current situation (still mix of ex-A/ex-G)
• ex-A default ANY - SYSTEM (60)
• gt lock escalation possible (chance of reaching
  NUMLKUSlow)
• gt not-enough-checkpointing BMP may go on (and
  end normally ?)
• gt favouring the offender (other BMPs will
  probably timeout)
• ex-G default PAGE - 0 (32)
• gt no lock escalation possible (chance of
  reaching NUMLKUShigh)
• FORTIS target PAGE - 0
• gt NUMLKUS on QA lower than on PROD
• gt forcing all BMPs to do appropriate
  checkpointing
• gt promotes the possible use of Lock Avoidance,
  especially in data sharing