rowsize versus pagesize
Hi, If a table's lockmode is row level and its rowsize is 3.5K, then what is better in regard of performance: 1- place the table in a tablespace with 2K pagesize 2- place the table in a tablespace with 4K pagesize 3- place the table in a tablespace with pagesize bigger than 4K for an OTLP enviornment. Also can we make more than one bufferpool of same pagesize. We are using IDS 11.5 FC4 on Solaris sparc. regards, Kamran
Hi, The major question is "what problem are you trying to solve?" If things are working acceptably well, why do you think a change will improve things? In general, getting the entire row on a single page will improve performance as long as that change does not interfere with other things. So you must have enough memory to allocate a second buffer pool without affecting the operation of the current buffer pool or other processes on the system. Without that extra memory, the change may very well make things worse. Only one buffer pool of each page size is allowed at this point. I hope that will change in some future release, but for now only one pool of each page size is allowed. I think I'd choose 4KB pages and live with whatever space is wasted because each row access would require only a single I/O. For 2KB pages, each row access requires at least 2 (possibly more) I/O's. If the memory is available for the second buffer pool, that I/O reduction ought to improve things some. However, if you're not I/O-bound now, then you may not see any improvement. I might also consider no change to the table, but rather put the indexes in 4 KB pages to make the index operations more efficient. But whether that improves things depends on whether or not your indexes are not optimal right now. So it all depends on exactly what problem you're attempting to solve. Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "KAMRAN HAQ" <khaq@i2cinc.com> To: ids@iiug.org Date: 07/08/09 08:18 AM Subject: rowsize versus pagesize [16258] Sent by: ids-bounces@iiug.org Hi, If a table's lockmode is row level and its rowsize is 3.5K, then what is better in regard of performance: 1- place the table in a tablespace with 2K pagesize 2- place the table in a tablespace with 4K pagesize 3- place the table in a tablespace with pagesize bigger than 4K for an OTLP enviornment. Also can we make more than one bufferpool of same pagesize. We are using IDS 11.5 FC4 on Solaris sparc. regards, Kamran ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
We get a lot of hits on some of tables mostly select,insert and updates. A very few deletions. One of them is around 5K in size. Where dbspaces are 2K in size. We 've tuned the databases in DB2 by assinging different bufferpools to different tablespaces (like separate bufferpool for reference tables). And DB2 do not allow to grow the rowsize of a table greater than pagesize of table (we used DB2 LUW 9). That's why I asked these questions. thanks for telling your choice for tablespace and answering why.