overhead of large pagesize

Hi, In OLTP system,Most SQL use the index, I could choose to use 4K,8K or 16k pagesize, large pagesize could help to save space, Do anyone know whether 8K or 16K pagesize has additional overhead except ids need to read more pages into the memory? thanks for your time.

Dig through the presentations of the 2010 IIUG meeting on the IIUG website, there was a presentation about pagesizes and the does and dont's. Maybe there was one in 2011 too, but I was not able to attend. Regards Joerg Volz Am 29.01.2012 um 10:00 schrieb "CHUAN LU" <luchuan@cn.ibm.com>: > Hi, > > In OLTP system,Most SQL use the index, I could choose to use 4K,8K or 16k > pagesize, large pagesize could help to save space, Do anyone know whether 8K > or 16K pagesize has additional overhead except ids need to read more pages > into the memory? > thanks for your time. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > IT Handel und Beratung Jorg Volz Bernhard-Fruh-Str. 7 77855 Achern GERMANY Tel: +49 (0)7841-681651 Fax: +49 (0)7841-681654 Mobil: +49 (0)170-2989757 VAT-ID: DE201383541 http://www.it-volz.de

Larger page size dbspaces have less overhead, proportionally, than smaller page sizes not more. Putting indexes into wider page dbspaces does not save much space, but it will improve performance of the index by flattening the btree structure - ie reducing the number of tree levels above the node level. Most of the space savings from wider page sizes results from moving data pages for wide row tables and tables whose row size wastes nearly a full row on each page. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Jan 29, 2012 at 4:00 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > Hi, > > In OLTP system,Most SQL use the index, I could choose to use 4K,8K or 16k > pagesize, large pagesize could help to save space, Do anyone know whether > 8K > or 16K pagesize has additional overhead except ids need to read more pages > into the memory? > thanks for your time. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec5186cbeb2461d04b7ac80db

I too am looking at page size currently, and I cannot find anything from the convention for 09/10/11 that discusses page sizes with any specificity (beyond a one or two page slide mention)

Honestly, with large page size, what I have seen so far : All are positive gains on both performance and Disk space...!! I can give you a example. I have a table with Collections types of 180,000 rows, in 2k page size, it took 200,000 pages and the data loading time is about 10 minutes. After I migrate it to a 8k page size, it ONLY uses half of the disk space( about 100,000 pages) and the data loading time is reduced to about 2 minutes...... Thanks, Frank On Sun, Jan 29, 2012 at 4:00 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > Hi, > > In OLTP system,Most SQL use the index, I could choose to use 4K,8K or 16k > pagesize, large pagesize could help to save space, Do anyone know whether > 8K > or 16K pagesize has additional overhead except ids need to read more pages > into the memory? > thanks for your time. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d043be2562ba9cd04b7da3d6e

Keep in mind that there is a limit of 255 rows per page. If you have = tables with smaller rowsizes, you will be wasting space in large page = dbspaces. Otherwise good to hear of your outcome. j. On Jan 31, 2012, at 5:11 PM, FRANK wrote: > Honestly, with large page size, what I have seen so far : All are = positive=20 > gains on both performance and Disk space...!!=20 >=20 > I can give you a example. I have a table with Collections types of=20 > 180,000 rows, in 2k page size, it took 200,000 pages and the data=20 > loading time is about 10 minutes. After I migrate it to a 8k page = size,=20 > it ONLY uses half of the disk space( about 100,000 pages) and the data=20= > loading time is reduced to about 2 minutes......=20 >=20 > Thanks,=20 > Frank=20 >=20 > On Sun, Jan 29, 2012 at 4:00 AM, CHUAN LU <luchuan@cn.ibm.com> wrote:=20= >=20 >> Hi,=20 >>=20 >> In OLTP system,Most SQL use the index, I could choose to use 4K,8K or = 16k=20 >> pagesize, large pagesize could help to save space, Do anyone know = whether=20 >> 8K=20 >> or 16K pagesize has additional overhead except ids need to read more = pages=20 >> into the memory?=20 >> thanks for your time.=20 >>=20 >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >>=20 >=20 > --f46d043be2562ba9cd04b7da3d6e=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

That might be the exactly the reason why I got big gains! The row size is BIG as the Collection type LIST( varchar(255) not null)...., a quite number of very long elements in the List. Thanks, Frank On Tue, Jan 31, 2012 at 5:17 PM, Jack Parker <jack.parker4@verizon.net>wrote: > Keep in mind that there is a limit of 255 rows per page. If you have = > tables with smaller rowsizes, you will be wasting space in large page = > dbspaces. Otherwise good to hear of your outcome. > > j. > > On Jan 31, 2012, at 5:11 PM, FRANK wrote: > > > Honestly, with large page size, what I have seen so far : All are = > positive=20 > > gains on both performance and Disk space...!!=20 > >=20 > > I can give you a example. I have a table with Collections types of=20 > > 180,000 rows, in 2k page size, it took 200,000 pages and the data=20 > > loading time is about 10 minutes. After I migrate it to a 8k page = > size,=20 > > it ONLY uses half of the disk space( about 100,000 pages) and the > data=20= > > > loading time is reduced to about 2 minutes......=20 > >=20 > > Thanks,=20 > > Frank=20 > >=20 > > On Sun, Jan 29, 2012 at 4:00 AM, CHUAN LU <luchuan@cn.ibm.com> > wrote:=20= > > >=20 > >> Hi,=20 > >>=20 > >> In OLTP system,Most SQL use the index, I could choose to use 4K,8K or = > 16k=20 > >> pagesize, large pagesize could help to save space, Do anyone know = > whether=20 > >> 8K=20 > >> or 16K pagesize has additional overhead except ids need to read more = > pages=20 > >> into the memory?=20 > >> thanks for your time.=20 > >>=20 > >>=20 > >>=20 > >>=20 > > = > **************************************************************************= > *****=20 > >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > > >>=20 > >>=20 > >=20 > > --f46d043be2562ba9cd04b7da3d6e=20 > >=20 > >=20 > > = > **************************************************************************= > *****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20= > > >=20 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec53f37dfb29e1004b7da9981