Monday, October 26, 2009

Load Resume Shrlevel Change Behavior - The Results

Hi

As promised, here are the answers to the questions from the previous post.
For my experiment I have created the following tables:

CREATE TABLE MOMI.MOMITEST (
ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 CACHE 10000 ),
TMSTAMP TIMESTAMP NOT NULL WITH DEFAULT ,
-- DMY1 CHAR(250) NOT NULL WITH DEFAULT 'MOMI',
-- DMY2 CHAR(250) NOT NULL WITH DEFAULT 'MOMI',
-- DMY3 CHAR(250) NOT NULL WITH DEFAULT 'MOMI',
-- DMY4 CHAR(200) NOT NULL WITH DEFAULT 'MOMI',
DMY CHAR(36) NOT NULL
) IN DB.MOMI;


CREATE TABLE MOMI.LOAD_MAX_ID (
ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 CACHE 10000 ),
REC_TMSTAMP TIMESTAMP NOT NULL WITH DEFAULT,
MAX_ID INT NOT NULL
) IN DB.MOM4;

Load was performed against the momitest table and only the dmy columns were loaded (id and timestamp generated automatically by DB2)

1) Which part of the data is available for concurrent access? only the data that existed in the table prior to the beginning of the utility, or the data that was actually being loaded?

The data that is being loaded is available for update. As the utility progresses, more data is available for update. I executed a REXX script that performed the following update statement in a loop against the table that is being loaded (momi.momitest):
UPDATE MOMI.MOMITEST
SET TMSTAMP = CURRENT TIMESTAMP,
DMY = 'UPDATED'
WHERE MOD(ID,5120) = 0
AND DMY <> 'UPDATED'

The results showed clearly that the update statement had executed several times, updating different records. Also, records that were loaded early on, were updated before the later records were loaded (that is, the timestamp generated by the update statement was earlier than the one generated as the default value). The conclusion is that data is available for update as it is being loaded. The LOAD utility takes internal commits which makes the data available (the data is not available immediately, my REXX did wait for locks).

2) Is the loaded data available for read only or also for updates?
that question was also answered by the first answer

3) Does DB2 perform internal commits to make the data available? if so, what is the frequency of these commits and how is the frequency calculated?

DB2 performs internal commits in order to make the data available.
Test description: A REXX scripts runs a loop that performs

INSERT INTO MOMI.LOAD_MAX_ID (MAX_ID)
SELECT MAX(ID) FROM SDMOMI.MOMITEST

while the table MOMITEST is being loaded. This statement will provide me with the maximum committed ID at each point in time (the table has a timestamp column with default value of current timestamp).

(actually, when I first designed the test, I thought I would record current timestamp against the MAX of the timestamps that are generated in the loaded table, but then I faced a problem with the values generated by current timestamp - see Timestamp post for more details)

Test Results: (the test was executed several times for different row sizes and different number of rows to load)
Record Size - table record size in bytes
Num Of Row - number of rows in load input file
Cpu / Elp time - Cpu / Elapsed time to run the LOAD utility
Check Interval - number of seconds to sleep in the REXX script loop
Min / Avg / Max Rows - Minimum, Average and Maximum amount of rows between two records in the control table (or in other words - the number of records that were committed by the load utility)
Min / Avg / Max tm diff - Different between two consecutive records insert timestamps

For Row Level Locking with MaxLocks 1000

Record Size
Num Of Row
Cpu Time
Elp Time
Check Interval
Min Rows
Avg Rows
Max Rows
Min Tm Diff
Avg tm diff
Max tm diff
50
2500
0.16
0.42
0
2500
2500
2500
0.00359
0.00359
0.00359
50
25K
0.66
1.22
0
4520
5120
10240
0.00162
0.16598
0.36
50
250K
5.57
11.53
1
15360
26738
30720
50
250K
5.53
11.5
0
4240
5120
5120
0.00179
0.22088
0.54724
50
500K
11.16
22.99
1
18720
23810
30720
1.0032
1.0931
1.2434
50
500K
11
21.89
0
3360
5120
10240
0.00227
0.21512
0.4225
50
2.5M
54.72
01:55.37
0
1440
5120
10240
0.00151
0.22882
0.64663
150
2.5M
53.87
01:49.77
0
1440
5120
10240
0.00111
0.21624
0.56245
300
2.5M
56.42
02:14.24
0
1440
5120
10240
0.00133
0.26629
0.65413
1000
1.9M
52.84
04:06.45
0
5120
5120
5120
0.00128
0.64057
16.39

So, the most obvious things that pops right out is - DB2 committed every 5120 records.
I also measured the average time between insert operations of the REXX script when the load utility was not running and I saw that the average wait time in seconds was
for 150 bytes rows - 0.21456 which is 99.2% of the time
for 300 bytes rows - 0.26432 which is 99.3% of the time
for 1000 bytes rows - 0.63843 which is 99.6% of the time


With Page Level locking and maxlocks 1000, these were the results:

Record Size
Num Of Row
Cpu Time
Elp Time
Check Interval
Min Rows
Avg Rows
Max Rows
Min Tm Diff
Avg tm diff
Max tm diff
50
250K
5.16
10.26
0
4240
5120
10240
0.00267
0.19626
0.36561
300
2.5M
58.06
02:49.86
0
1440
5120
10240
0.00156
0.3354
16.574
1000
2.5M
01:10.59
04:56.98
0
1440
5120
10240
0.00145
0.59305
7.236

And the insert wait time was roughly 99% also.

With Row Level locking and maxlocks 100, these were the results:

Record Size
Num Of Row
Cpu Time
Elp Time
Check Interval
Min Rows
Avg Rows
Max Rows
Min Tm Diff
Avg tm diff
Max tm diff
50
250K
7.81
34.74
0
8
59*
584
0.00118
0.00803
0.19021
300
2.5M
01:21.48
06:55.43
0
8
65**
1332
0.00115
0.01083
0.38246
1000
2.5M
01:31.14
08:31.13
0
8
90
1296
0.00123
0.01836
0.39555

* out of 4214 intervals, 2638 had a value of 36, 881 had 72, 372 had 108 and 139 had 144, so probably, 36 is the commit interval.
* out of 38090 intervals, 21459 had a value of 36, 8696 had 72, 3897 had 108 and 1717 had 144, so probably, 36 is the commit interval.

With Row Level locking and maxlocks 100000, these were the results:

Record Size
Num Of Row
Cpu Time
Elp Time
Check Interval
Min Rows
Avg Rows
Max Rows
Min Tm Diff
Avg tm diff
Max tm diff
50
250K
6.21
11.02
0
4240
5120
5120
0.00161
0.20642
0.37181
1000
2.5M
01:14.69
04:50.63
0
1440
5120
5120
0.00136
0.58
17.302


So what can we actually learn from all these numbers?
1) For default settings (page level locking with maxlocks that defaults to NUMLKTS parameter) DB2 will commit every 5120 rows, which in this test, was rather quickly even for a big table with a 1000 bytes per row and several indexes. Even when using a much higher value for maxlocks, DB2 will not change the commit frequency.
2) When constrained by maxlocks,  DB2 will commit more frequently (our test showed every 36 rows), and performance will be impacted severely.
3) It seems that the commit frequency does not depend on the amount of records we load
4) It seems that the commit frequency does not depend on the record length of the table being loaded

That sums up answer number 3.
As for question number 4:
4) Is it possible that the load utility will experience a lock escalation?

The Load utility will not cause lock escalation, no matter what is the maximum number of locks it is allowed to acquire. This conclusion is based on 3 facts:
1) No lock escalation message was issued to the DB2MSTR and no lock escalation appeared in the lock escalation counter in omegamon.
2) If lock escalation had took place, there would be no reason for the LOAD operation to take significantly longer to execute as we have seen
3) The omegamon lock counters show that when the lockmaxs parameter is set to a small value, the LOAD utility had many lock requests and many lock change request (which will be redundant if it had escalated to a table lock).

And one last nugget - the LOAD utility acquires locks at the same level the application does, so if your tablespace is defined with locksize tablespace, the LOAD utility will also acquire a S tablespace lock, which will cause any user application that tried to update the table to wait.

That was a long one, hope it answered all your questions.

Till next time
Momi

1 comment:

  1. Many thanks for your insightful analysis! You managed to help me solve a high-priority, high-focus problem that was happening today at work: LOAD RESUME SHRLEVEL CHANGE was running for hours and hours because it seemed to be running COMMIT every row or two. Your post prompted me to look at our SQL - turns out we coded LOCKMAX 1 on these tables! Thanks!

    ReplyDelete