一个由于应用程序逻辑设计的失误导致的性能问题

jayciedede

用户的DB2是什么版本的，是9的话，那两个字段用XML类型应该好很多

unixnewbie

If just for overflow problem, I would change those two columns to CLOB then CREATE TABLE .... LONG IN a seperate 32K SMSTBS.

wangzhonnew

原帖由 diablo2 于 2008-10-27 11:58 发表
why not try altering PCTFREE of the table?

缺点是，浪费存储

it's a good one but the problem is, when data first insert into page it was very small, and then application will give the record a big VARCHAR.... that means, even if we set PCTFREE to 50%, there's still high chance fragmentation happens, maybe will not so bad as before, but may need to do reorg often as well....

but it's a good one, really didn't thought about this

wangzhonnew

原帖由 jayciedede 于 2008-10-27 12:20 发表
用户的DB2是什么版本的，是9的话，那两个字段用XML类型应该好很多

v8 fp8 unfortunatly...

sorry didn't include this info in the description

wangzhonnew

原帖由 unixnewbie 于 2008-10-27 19:57 发表
If just for overflow problem, I would change those two columns to CLOB then CREATE TABLE .... LONG IN a seperate 32K SMSTBS.

CLOB or LF are large objects, there's no prefetching or pagecleaning happens to large objects...
in this case, all queries requires direct I/O when reading these 2 columns, and all INSERT/UPDATE/DELETE requires direct I/O as well.... that will cause negative performance issue for sure, we don't know how badly it will affect the system before doing any benchmark

unixnewbie

原帖由 wangzhonnew 于 27/10/2008 22:13 发表

CLOB or LF are large objects, there's no prefetching or pagecleaning happens to large objects...
in this case, all queries requires direct I/O when reading these 2 columns, and all INSERT/UPDATE/DELETE requires direct I/O as well.... that will cause negative performance issue for sure, we don't know how badly it will affect the system before doing any benchmark

Yes, but that's where FILE SYSTEM CACHING has its tribute.

wangzhonnew

原帖由 unixnewbie 于 2008-10-28 09:14 发表


Yes, but that's where FILE SYSTEM CACHING has its tribute.

hmm... that's really an interesting idea, use FS cache to handle large object...
never thought about that, have you used this approach in any project before?

unixnewbie

原帖由 wangzhonnew 于 28/10/2008 12:56 发表

hmm... that's really an interesting idea, use FS cache to handle large object...
never thought about that, have you used this approach in any project before?

Using FS caching is recommended for LOB in infocenter. You may need to tune AIX's minclient/maxclient as well. Use 'vmstat -v' or 'nmon' to track how much memory the system is using for FS caching.

myfriend2010

新搞一个常驻内存的表，或者新建一个bufferpool，我觉得都不很合适！因为实际系统中，往往内存都是吃紧的！
clob可以考虑，但是这样即使没有over ，select 的速度应该会受到影响！

我还是觉得要提高select速度，最好是将这3列消失掉，这样要么对表增加索引！走全索引，要么就搞一个对照表，就像行压缩一样！呵呵！速度肯定很好！

wangzhonnew

恩，myfriend的思路是经典思路，改变程序流程从根本上解决overflow
尽管这个是客户将要做的咚咚，不过思路不算创新

unixnewbie，如果你建议用FS cache作为缓冲的话，从aix来讲minperm和maxperm应该怎样调整才算合理？假设现在是20/80