DB2 CLOB performance V8.2.5 vs V9.1

[Otto Carl Marte]

I have noticed an incredible difference in performance between DB2 LUW
v8.2.5 and V9.1.1 with regards to CLOBs.

I will sketch the scenario i have tested.
I created a table with 1 Integer column, 50 Varchar(20) columns and 4
CLOB(1M) columns. These were all created on a REGULAR USER TABLESPACE
for both DB2 v8.2.5 an Db2 V9.1.1
I created an index on the integer column. I created two stored
procedures one which inserted into the table. The other just had the
signature and did NO inserting.

I used a simple Java single threaded test to do a 1000 inserts using
the stored procedures.

The results are astonishing: (measurements in inserts per second)

DB2 V8.2.5 DB2 V9.1.1
with_body 70 17
no_body 140 22

The no_body test is particularly interesting. No "work" is being done
in the database here and yet DB2 9 is incredibly slow.
What is especially interesting is that there are a large amount of
page faults for DB2 v9. (+- 20 000) in comparison with < 1000 for DB2
v8.

Can anybody help explain this behaviour? Had the handling of CLOBS
(perhaps the binding of CLOBS) changed between DB2 V8 and V9? Am I
perhaps misconfiguring something? What do all the page faults mean?

[Ray]

I'm really ignorant of this so apply a lot of salt but those results
suggest more to me about stored procedure activation than CLOB
handling.

[Phil Sherman]

Your page fault counts indicate you are running on a storage constrained
system; especially with the V9 system. Assuming that you ran these tests
on an unloaded system to eliminate other workload effects, you should
see no or very little paging for both versions. The paging difference
indicates that differences on storage utilization between the versions
have pushed your system into the area where paging has become a
significant factor in performance.

Examine your buffer pools and see if you can shrink them, on the V9
system, to free up 50-100mb of storage. Rerun your "no body" test and
see if performance improves and/or the paging rate decreases. If you see
significant improvements, you've verified that you are running in a
storage constrained environment. Your real work will be to determine
what, on the V9 system, is using more storage than V8.

The simple and usually the cheapest (maybe not the best) solution to
this problem is to add memory to the system.

Phil Sherman
Otto Carl Marte wrote:

I have noticed an incredible difference in performance between DB2 LUW
v8.2.5 and V9.1.1 with regards to CLOBs.

I will sketch the scenario i have tested.
I created a table with 1 Integer column, 50 Varchar(20) columns and 4
CLOB(1M) columns. These were all created on a REGULAR USER TABLESPACE
for both DB2 v8.2.5 an Db2 V9.1.1
I created an index on the integer column. I created two stored
procedures one which inserted into the table. The other just had the
signature and did NO inserting.

I used a simple Java single threaded test to do a 1000 inserts using
the stored procedures.

The results are astonishing: (measurements in inserts per second)

DB2 V8.2.5 DB2 V9.1.1
with_body 70 17
no_body 140 22

The no_body test is particularly interesting. No "work" is being done
in the database here and yet DB2 9 is incredibly slow.
What is especially interesting is that there are a large amount of
page faults for DB2 v9. (+- 20 000) in comparison with < 1000 for DB2
v8.

Can anybody help explain this behaviour? Had the handling of CLOBS
(perhaps the binding of CLOBS) changed between DB2 V8 and V9? Am I
perhaps misconfiguring something? What do all the page faults mean?

[Otto Carl Marte]

Thanks for your reply Phil.
I have some reservations however. I was under the impression that
CLOBs did not make use of the bufferpools due to their varying (and
large) memory requirements. I believe they bypass the bufferpools
completely. Is this assumption incorrect?

Despite my reservations, I have followed your recommendations :-). I
added 512Mb to a partition and re-ran my tests. Unfortunately this had
no affect on performance. Db2 V9.1.1 still produces a large amount of
page faults when calling stored procedures with CLOBs.

Any idea why there are page faults even when no work is being done?
Can anybody give me insight on what exactly happens when a stored
procedure is called with CLOBS as stored procedure parameters? How is
memory allocated when CLOB variables are passed to a DB2 stored
procedure?

[Serge Rielau]

Otto Carl Marte wrote:

Thanks for your reply Phil.
I have some reservations however. I was under the impression that
CLOBs did not make use of the bufferpools due to their varying (and
large) memory requirements. I believe they bypass the bufferpools
completely. Is this assumption incorrect?

Despite my reservations, I have followed your recommendations :-). I
added 512Mb to a partition and re-ran my tests. Unfortunately this had
no affect on performance. Db2 V9.1.1 still produces a large amount of
page faults when calling stored procedures with CLOBs.

Any idea why there are page faults even when no work is being done?
Can anybody give me insight on what exactly happens when a stored
procedure is called with CLOBS as stored procedure parameters? How is
memory allocated when CLOB variables are passed to a DB2 stored
procedure?

Otto,

To the best of my knowledge CLOBs are materialized in SQL Procedures.
That is true for sure for local variables, but I imagine the same is
true for parameters.
The memory for that comes from the application heap.
In DB2 9 overall(!) performance of SQL Procedures has been greatly
improved (we think an average 20%) by pushing processing for logic and
SQL very close together. So I'm not entirely surprised that the behavior
may have changed fro LOBs.
Try increasing the application heap and see where that leaves you.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Otto Carl Marte]

Serge,

Thanks for your explanation. Indeed, we have seen an increase in
performance for stored procedures (that don't use CLOBs) for DB2 v9 vs
Db2 v8. So, some good work has been done here :-)

I'm not sure what you mean by materialized? Could you explain?
I have increased the application heap ( I assume this was the
APPLHEAPSZ database configuration parameter), but this makes little
difference. I have written my tests in CLP scripts (to take Java out
of the equation) but sadly Java can't be blamed for this. So, there
must be something going on with DB2.

I have run the vmstat -s command which gives me:
0 paging space page ins
0 paging space page outs

which usually indicates some sort of memory bottleneck.

As a reference DB2 8.2.7 gave me:
8327 paging space page ins
28357 paging space page outs

So, i suppose my question is, what db2 utility could i use to track
down where my memory bottleneck is?

[cwahlmeier]

Otto,

I don't know if my experience pertains to your problem or not, but I
will share it. After migration to V9.1, our queries that select BLOBS
via ADO suddenly went from sub-second elapsed times to over a minute.
I opened a PMR with IBM, but that wasn't going well. Among the many
traces I did, I noticed in an event trace that the statement was
spending all of its time in NULLID.SYSSTAT. I researched it and found
that it is part of the CLI. It gets bound when we bind DB2CLI.LST.
So, I dropped that package and rebound DB2CLI.LST. Surprisingly, that
fixed my problem. I am not sure why it fixed it! However, I do know
that they started using a BIND ADD instead of a BIND REPLACE on the
SYSSTAT bind. Thus, it would not be rebound if it already existed.
Again, maybe this is off base, but our problems bear some
similarities.

Best of luck,

Craig Wahlmeier

[Otto Carl Marte]

Just an update on this. I opened a PMR with IBM on this performance
issue. The result of the investigation of this was that there is a
memory allocation problem for CLOBS with DB2 v9.1.0 which results in
degraded performance. This has been fixed and will be included in
FixPak 3.