page size of chunk in fragmented table

Version: IDS 10 We are fragmenting table which currently is in one dbspace with 8K as page size. Now since we are fragmenting I wanted to ask: 1. Should we reduce the page size or keep it same 2. Which one is better, small page size or large page size This table grows and will eventually have 50 M rows. We also have blobs in tablespace.

On Aug 6, 3:25 pm, mohitanch...@gmail.com wrote: > Version: IDS 10 > > We are fragmenting table which currently is in one dbspace with 8K as > page size. Now since we are fragmenting I wanted to ask: > 1. Should we reduce the page size or keep it same > 2. Which one is better, small page size or large page size > > This table grows and will eventually have 50 M rows. We also have > blobs in tablespace. Pagesize versus performance is a tricky thing, in no small part because it's such a new feature. Larger pages tend to have a significantly positive effect on index performance. For data pages, it mostly depends on the rowsize. If you have rows wider than 2016 bytes then absolutely larger pages will improve performance, sometimes several times. If your rowsize is such that each page wastes close to a row in unused space, then again, if your can use a pagesize that reduces that waste to only a small part of a row or even if you can reduce the number of rowsize wasted spaces from 1 out of N rows to 1 out of 2N or 4N rows then also there will be a measurable gain for SOME queries processing large numbers of rows. There have even been discussions about whether it makes sense to use larger pages for historical fragments of tables whose newer rows are only (or mainly) accessed by OLTP style queries but whose historical fragments are accessed more in a DSS or DW style environment. No solid answers on that one. Mark Stanton is a HUGE fan of larger page sizes, perhaps he can weight in with his perspective if he's monitoring. Art S. Kagel

On Aug 6, 4:21 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Aug 6, 3:25 pm, mohitanch...@gmail.com wrote: > > > Version: IDS 10 > > > We are fragmenting table which currently is in one dbspace with 8K as > > page size. Now since we are fragmenting I wanted to ask: > > 1. Should we reduce the page size or keep it same > > 2. Which one is better, small page size or large page size > > > This table grows and will eventually have 50 M rows. We also have > > blobs in tablespace. > > Pagesize versus performance is a tricky thing, in no small part > because it's such a new feature. > > Larger pages tend to have a significantly positive effect on index > performance. For data pages, it mostly depends on the rowsize. If > you have rows wider than 2016 bytes then absolutely larger pages will > improve performance, sometimes several times. If your rowsize is such > that each page wastes close to a row in unused space, then again, if > your can use a pagesize that reduces that waste to only a small part > of a row or even if you can reduce the number of rowsize wasted spaces > from 1 out of N rows to 1 out of 2N or 4N rows then also there will be > a measurable gain for SOME queries processing large numbers of rows. > > There have even been discussions about whether it makes sense to use > larger pages for historical fragments of tables whose newer rows are > only (or mainly) accessed by OLTP style queries but whose historical > fragments are accessed more in a DSS or DW style environment. No > solid answers on that one. Mark Stanton is a HUGE fan of larger page > sizes, perhaps he can weight in with his perspective if he's > monitoring. OUCH!!! SORRY MARK! Mark Scranton is who I meant to mention. My fingers moved too fast. > Art S. Kagel

It depends. Benchmark with the different page sizes and see what floats your boat. Don't forget to also modify the bufferpool settings in your $ONCONFIG to reflect your choices. j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of mohitanchlia@gmail.com Sent: Monday, August 06, 2007 3:26 PM To: informix-list@iiug.org Subject: page size of chunk in fragmented table Version: IDS 10 We are fragmenting table which currently is in one dbspace with 8K as page size. Now since we are fragmenting I wanted to ask: 1. Should we reduce the page size or keep it same 2. Which one is better, small page size or large page size This table grows and will eventually have 50 M rows. We also have blobs in tablespace. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list