Actual row size of an index
Mike wanted to find the actual average index key/row size so he could choose an optimal page (bufferpool) size for indexes in IDS 10, worrying about the 255-rows-per-page limit. Art Kagel replied that the 255 limit applies only to data pages (it stems from the one-byte slot number in a ROWID) and not to index pages, so larger page sizes can be used freely; dbschema/myschema report key sizes. Alexey Sonkin confirmed index pages do have slots but allow up to 65535 entries, and another poster said the IBM slide claiming 255 keys per index page was simply wrong. Testing with 16K index pages gave good OLTP gains.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello,
We are in the process of testing the use of alternate page/bufferpool sizes
for our indexes. I am trying to determine what the best size would be. Since
the maximum number of rows that can be stored is still 255 in IDS10, I do not
want to pick a page size that would cause too much space to be wasted. I can
estimate the row size, but I would like to verify my estimate against an
actual production database. Many of my indexes are non-unique, and I cannot be
certain my estimates are correct. I was wondering if anyone has a utility, or
knows of an onstat, oncheck command, or SMI query, that would obtain the
actual average rowsize for an index? Thanks!
Mike
MICHAEL FAVOLE wrote:
> Hello,
>
> We are in the process of testing the use of alternate page/bufferpool sizes
> for our indexes. I am trying to determine what the best size would be. Since
> the maximum number of rows that can be stored is still 255 in IDS10, I do not
> want to pick a page size that would cause too much space to be wasted. I can
> estimate the row size, but I would like to verify my estimate against an
> actual production database. Many of my indexes are non-unique, and I cannot
be
> certain my estimates are correct. I was wondering if anyone has a utility, or
> knows of an onstat, oncheck command, or SMI query, that would obtain the
> actual average rowsize for an index? Thanks!
>
Dbschema, and my dbschema replacement utility myschema, both report the
table's rowsize and the combined total index key sizes. However, you
don't need to worry about the keysize when determining pagesize for
indexes. IDS doesn't use the slot table (which is where the 255 data
row per page limit comes from) for index nodes and leaves. Each page
has only one entry on it, either a node or a leaf, the individual keys
are incorporated into that node/leaf not separate entries in the slot
table, so it's irrelevant for indexes. You can use as large a pagesize
as you want for indexes to get the performance you need.
Art S. Kagel
Oninit
> Mike
================================================================================
===========
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.oninit.com/home/disclaimer.php
================================================================================
===========
Thanks for the reply Art. We tested with a 16k size for our critical indexes and had positive results. I am a bit confused about one thing though. In a chat with the labs presentation (IBM Informix Dynamix Server 2005-11-02 slide 10), Jonathan Leffler indicates that there is "Still a maximum of 255 keys per index page". So this is probably not correct, agree? Thanks again, Mike
MICHAEL FAVOLE wrote: > Thanks for the reply Art. We tested with a 16k size for our critical indexes > and had positive results. > > I am a bit confused about one thing though. In a chat with the labs > presentation (IBM Informix Dynamix Server 2005-11-02 slide 10), Jonathan > Leffler indicates that there is "Still a maximum of 255 keys per index page". > So this is probably not correct, agree? > Dunno now. That's not my understanding, but I've been wrong before - mostly when I contradict Jonathan ;-) Jonathan? Comments? Art S. Kagel Oninit > Thanks again, > Mike > > > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========
That slide was wrong. I have tested this and you can fit a large number of index rows on a 16K page. Data rows are still 255! Huge performance increase for OLTP queries. FYI Kernoal "Art S. Kagel (Oninit LLC)" <art@oninit.com> Sent by: ids-bounces@iiug.org 01/18/2008 11:50 AM Please respond to ids@iiug.org To ids@iiug.org cc Subject Re: Actual row size of an index [10995] MICHAEL FAVOLE wrote: > Thanks for the reply Art. We tested with a 16k size for our critical indexes > and had positive results. > > I am a bit confused about one thing though. In a chat with the labs > presentation (IBM Informix Dynamix Server 2005-11-02 slide 10), Jonathan > Leffler indicates that there is "Still a maximum of 255 keys per index page". > So this is probably not correct, agree? > Dunno now. That's not my understanding, but I've been wrong before - mostly when I contradict Jonathan ;-) Jonathan? Comments? Art S. Kagel Oninit > Thanks again, > Mike > > > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ =========== ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!
That being said, would there be an advantage - if you have a large rowsize - to sizing large pages to get close to 255 rows on them for OLTP? Art? JM3? Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of kernoal.stephens@autozone.com Sent: Friday, January 18, 2008 12:59 PM To: ids@iiug.org Subject: Re: Actual row size of an index [10996] That slide was wrong. I have tested this and you can fit a large number of index rows on a 16K page. Data rows are still 255! Huge performance increase for OLTP queries. FYI Kernoal "Art S. Kagel (Oninit LLC)" <art@oninit.com> Sent by: ids-bounces@iiug.org 01/18/2008 11:50 AM Please respond to ids@iiug.org To ids@iiug.org cc Subject Re: Actual row size of an index [10995] MICHAEL FAVOLE wrote: > Thanks for the reply Art. We tested with a 16k size for our critical indexes > and had positive results. > > I am a bit confused about one thing though. In a chat with the labs > presentation (IBM Informix Dynamix Server 2005-11-02 slide 10), Jonathan > Leffler indicates that there is "Still a maximum of 255 keys per index page". > So this is probably not correct, agree? > Dunno now. That's not my understanding, but I've been wrong before - mostly when I contradict Jonathan ;-) Jonathan? Comments? Art S. Kagel Oninit > Thanks again, > Mike > > > ======================================================================== =================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ======================================================================== =================== ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!
Hi, everybody,
About the number of entries on index page...
Actually, index page does have a slot structure (one can easily see
it by dumping index page with oncheck -pP).
Nevertheless, for index page there is no limitation of 255 slots per
page:
this limitation actually comes from slot number usage in ROWID's
(one byte for slot number, 3 bytes for page number), not from a
a page design (the 'number of slots' entry in a page header is INT2).
Index entries are never referenced by ROWID, this is why there is
no need to limit the number of slots on index page by 255.
The actual limit is 65535
-Alexey
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art S.
> Kagel (Oninit LLC)
> Sent: Friday, January 18, 2008 12:23 PM
> To: ids@iiug.org
> Subject: Re: Actual row size of an index [10993]
>
> MICHAEL FAVOLE wrote:
> > Hello,
> >
> > We are in the process of testing the use of alternate
page/bufferpool sizes
> > for our indexes. I am trying to determine what the best size would
be. Since
> > the maximum number of rows that can be stored is still 255 in IDS10,
I do
> not
> > want to pick a page size that would cause too much space to be
wasted. I can
> > estimate the row size, but I would like to verify my estimate
against an
> > actual production database. Many of my indexes are non-unique, and I
cannot
> be
> > certain my estimates are correct. I was wondering if anyone has a
utility,
> or
> > knows of an onstat, oncheck command, or SMI query, that would obtain
the
> > actual average rowsize for an index? Thanks!
> >
>
> Dbschema, and my dbschema replacement utility myschema, both report
the
> table's rowsize and the combined total index key sizes. However, you
> don't need to worry about the keysize when determining pagesize for
> indexes. IDS doesn't use the slot table (which is where the 255 data
> row per page limit comes from) for index nodes and leaves. Each page
> has only one entry on it, either a node or a leaf, the individual keys
> are incorporated into that node/leaf not separate entries in the slot
> table, so it's irrelevant for indexes. You can use as large a pagesize
> as you want for indexes to get the performance you need.
>
> Art S. Kagel
> Oninit
>
> > Mike
>
>
>
========================================================================
======
> =============
> Please access the attached hyperlink for an important electronic
> communications disclaimer:
>
> http://www.oninit.com/home/disclaimer.php
>
>
>
========================================================================
======
> =============
>
>
>
************************************************************************
******
> *
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
Robert Roussey(IT) wrote: > That being said, would there be an advantage - if you have a large > rowsize - to sizing large pages to get close to 255 rows on them for > OLTP? > That's a leap that's tough to make. In OLTP most accesses are to single or a small number of rows so the benefits of having more rows per page are difficult to guess. IF you can determine that the rowsize is causing a large waste of space on the page, then clearly a larger page that may permit an additional full row on a page and a smaller waste space and that will tend to perform better. Art S. Kagel Oninit > Art? JM3? > > Bob Roussey > Unix / Informix Administration > Spirit Airlines > Robert.Roussey@SpiritAir.com > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > kernoal.stephens@autozone.com > Sent: Friday, January 18, 2008 12:59 PM > To: ids@iiug.org > Subject: Re: Actual row size of an index [10996] > > That slide was wrong. > > I have tested this and you can fit a large number of index rows on a 16K > > page. Data rows are still 255! > Huge performance increase for OLTP queries. > > FYI > Kernoal > > "Art S. Kagel (Oninit LLC)" <art@oninit.com> > Sent by: ids-bounces@iiug.org > 01/18/2008 11:50 AM > Please respond to > ids@iiug.org > > To > ids@iiug.org > cc > > Subject > Re: Actual row size of an index [10995] > > MICHAEL FAVOLE wrote: > >> Thanks for the reply Art. We tested with a 16k size for our critical >> > indexes > >> and had positive results. >> >> I am a bit confused about one thing though. In a chat with the labs >> presentation (IBM Informix Dynamix Server 2005-11-02 slide 10), >> > Jonathan > > >> Leffler indicates that there is "Still a maximum of 255 keys per index >> > > page". > >> So this is probably not correct, agree? >> >> > > Dunno now. That's not my understanding, but I've been wrong before - > mostly when I contradict Jonathan ;-) > > Jonathan? Comments? > > Art S. Kagel > Oninit > > >> Thanks again, >> Mike >> > > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========