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

unixnewbie · 发表于 2008-10-30 08:30

原帖由 wangzhonnew 于 30/10/2008 00:01 发表
恩，myfriend的思路是经典思路，改变程序流程从根本上解决overflow
尽管这个是客户将要做的咚咚，不过思路不算创新

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

http://publib.boulder.ibm.com/infocenter/pseries/v5r3/index.jsp?topic=/com.ibm.aix.prftungd/doc/prftungd/jfs_cache_limit_maxclient.htm

wangzhonnew · 发表于 2008-10-30 08:53

恩……下一个关于LOB得问题就是，一般的数据库服务器不会留出很多无用的内存的，这样的话实际上留给OS FS Cache得并不是很多。
这个表本身大概1百到1百五十万左右的数据，如果两列VARCHAR(15000)都转成LOB以后，在理想情况下如果所有数据都驻留FS CACHE则需要20G以上的空闲内存，恐怕一般的生产系统不会有那么多空闲。而且FS CACHE是一个global得，不可能只对于这两个LOB列CACHE，所以那样的话则需要更多的内存……对于这个问题有没有什么解决方法呢？

diablo2 · 发表于 2008-10-30 11:27

BTW，我到目前为止，怎么都没有模拟出overflow. ... 继续模拟

wangzhonnew · 发表于 2008-10-30 12:24

原帖由 unixnewbie 于 2008-10-30 10:26 发表

You're not caching every LOBs at the same time, are you? My understanding of your case is that the SP is suffering from excessfive overflow, so you don't want those VARCHAR fields to adversely affect the SP. The access to VARCHAR fields is limited.

that's right, the current major bottleneck is the overflow for varchar columns. HOWEVER, it may not a good idea if the change will cause performance degradation for other operations such like INSERT or UPDATE.
Of course this approach will resolve the slowness for SELECT (because FETCH or TBSCAN will not read LOB data), however it could cause negative effect to insert/delete.

Note that the slowness will happen several days after reorg. that means right after reorg the data is organized. In that case, insert/delete/update may not have to take multiple I/Os if it's VARCHAR. After changing to LOB the performance might be affected in this "good case" (right after reorg).

But yeah, I agree that if the table is really messy and more than 60-70% records are overflowed, LOB won't make performance worse for insert/update/delete, and it will make performance good for SELECT

guess this is the coolest one i've heard in this thread (of course the best/correct approach is redesign the application, but changing to LOB is a cool one that I didn't think too much into). I just quickly thought about it when discussing with cust and thought changing to LOB would significantly decrease I/O performance. But after thinking more into it now, it's not THAT bad as it looked like....