Informix

Migrating from an older release of Informix on a different OS, so everything will be different in the new environment.  New Informix, new OS, new hardware.  So, this is a great time to consider changing some of the things that have remained in place for so long just because they were already in place.

Among those changes, I would like to change the default page size for indexes, and possibly for some tables.  

My recollection from discussions at past IIUG meetings and here on the forum are that indexes should be on 16k pages.  Is that still the case?  Is it a universal rule, i.e., all indexes should be on 16k pages?  Or are there guidelines for which indexes should be on 2k, 16k, and other size pages?

  Similar question for tables - any guidelines would be appreciated.  I would suspect that for tables, one rule might be that tables with row size > 2k should be on a page size large enough to hold at least one row, but beyond that, I'm not sure what other considerations there might be.  And if a row can be larger than 2k in length, but has several VARCHAR/LVARCHAR columns and most rows are shorter than 2k, does that impact the choice of page size for a table?

Related to this, unless I'm mistaken, we would want a bufferpool for each page size that we use within our system.  So, if we stick with 2k pages for tables and 16k pages for indexes, we'd need a 2k bufferpool and a 16k bufferpool.  But if we have some tables that need to go on 4k pages, we'd also need a 4k bufferpool. Correct so far?

If we've got dbspaces and bufferpools set up with these other page sizes, do we also need to create any dbspaces for temp tables with those other page sizes?

Any other things to consider with these other page sizes?

Migrating from an older release of Informix on a different OS, so everything will be different in the new environment.  New Informix, new OS, new hardware.  So, this is a great time to consider changing some of the things that have remained in place for so long just because they were already in place.

Among those changes, I would like to change the default page size for indexes, and possibly for some tables.  

My recollection from discussions at past IIUG meetings and here on the forum are that indexes should be on 16k pages.  Is that still the case?  Is it a universal rule, i.e., all indexes should be on 16k pages?  Or are there guidelines for which indexes should be on 2k, 16k, and other size pages?

  Similar question for tables - any guidelines would be appreciated.  I would suspect that for tables, one rule might be that tables with row size > 2k should be on a page size large enough to hold at least one row, but beyond that, I'm not sure what other considerations there might be.  And if a row can be larger than 2k in length, but has several VARCHAR/LVARCHAR columns and most rows are shorter than 2k, does that impact the choice of page size for a table?

Related to this, unless I'm mistaken, we would want a bufferpool for each page size that we use within our system.  So, if we stick with 2k pages for tables and 16k pages for indexes, we'd need a 2k bufferpool and a 16k bufferpool.  But if we have some tables that need to go on 4k pages, we'd also need a 4k bufferpool. Correct so far?

If we've got dbspaces and bufferpools set up with these other page sizes, do we also need to create any dbspaces for temp tables with those other page sizes?

Any other things to consider with these other page sizes?

Migrating from an older release of Informix on a different OS, so everything will be different in the new environment.  New Informix, new OS, new hardware.  So, this is a great time to consider changing some of the things that have remained in place for so long just because they were already in place.

Among those changes, I would like to change the default page size for indexes, and possibly for some tables.  

My recollection from discussions at past IIUG meetings and here on the forum are that indexes should be on 16k pages.  Is that still the case?  Is it a universal rule, i.e., all indexes should be on 16k pages?  Or are there guidelines for which indexes should be on 2k, 16k, and other size pages?

  Similar question for tables - any guidelines would be appreciated.  I would suspect that for tables, one rule might be that tables with row size > 2k should be on a page size large enough to hold at least one row, but beyond that, I'm not sure what other considerations there might be.  And if a row can be larger than 2k in length, but has several VARCHAR/LVARCHAR columns and most rows are shorter than 2k, does that impact the choice of page size for a table?

Related to this, unless I'm mistaken, we would want a bufferpool for each page size that we use within our system.  So, if we stick with 2k pages for tables and 16k pages for indexes, we'd need a 2k bufferpool and a 16k bufferpool.  But if we have some tables that need to go on 4k pages, we'd also need a 4k bufferpool. Correct so far?

If we've got dbspaces and bufferpools set up with these other page sizes, do we also need to create any dbspaces for temp tables with those other page sizes?

Any other things to consider with these other page sizes?