Page size x Row Size

Hi everybody, What are the consequences for a Informix Instance that have a lot of table containing row size bigger than page size? Is that bad ? Thanks, André Luiz Rufino

If it's bad? Yes. In order to read a record, you need to access a page, find out where's the rest of the record and read another page (what may imply a different I/O). How bad? Hard to tell... The consequences is performance impact... Do you feel it? Of course if you are on v10+ you can define different page sizes. That implies different buffer pools and some more configuration and monitoring work. Regards On Mon, Jul 23, 2012 at 10:43 PM, André Luiz Rufino <andre_rufino@msn.com>wrote: > Hi everybody, > What are the consequences for a Informix Instance that have a lot of table > containing row size bigger than page size? Is that bad ? > Thanks, > André Luiz Rufino > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00248c6a66d6ba07bd04c5863d5c

>Hi everybody, >What are the consequences for a Informix Instance that have a lot of table >containing row size bigger than page size? Is that bad ? >Thanks, >André Luiz Rufino Senhior Rufino, What hardware and IDS version are you using? Most IDS page sizes are 4K, but some are 2K depending on the hardware and IDS version. What is the row size of your largest table? If the row size exceeds the page size, IDS will require another page, or more, depending on whether the page size is 2K or 4K, thus requiring addition read(s), write(s) and additional memory consumption.

I once did a project for a large telecom optimizing a 4GL report that was running for 4 hours. The first thing I did was to break up the 2100 byte table into two tables one with <200 bytes that contained the data needed for this and other major reports and a second with just over 1900 bytes. Without doing anything else the report then ran in under 10 minutes. Further schema changes and code optimization got it down to ~4 minutes, but the big gain was not accessing those very wide rows. You tell me if your wide records are hurting you. One thing you can try if all of the data in the table is needed all of the time, is to create a dbspace with a wider pagesize so the row can be contiguous. Calculate the pagesize to also minimize waste if possible. 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 Mon, Jul 23, 2012 at 5:43 PM, André Luiz Rufino <andre_rufino@msn.com>wrote: > Hi everybody, > What are the consequences for a Informix Instance that have a lot of table > containing row size bigger than page size? Is that bad ? > Thanks, > André Luiz Rufino > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec51a758eeca32e04c5880bf9

Actually Frank, only AIX, Windows, and MAC OS-X use 4K pages as the default. All other platforms use a 2K page as the default. You can define larger page sizes in multiples of the platform default page up to 32K pages for all versions from 10.00 and later. 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 Mon, Jul 23, 2012 at 7:18 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > >Hi everybody, > >What are the consequences for a Informix Instance that have a lot of table > >containing row size bigger than page size? Is that bad ? > >Thanks, > >André Luiz Rufino > > Senhior Rufino, > > What hardware and IDS version are you using? > > Most IDS page sizes are 4K, but some are 2K depending on the hardware and > IDS > version. What is the row size of your largest table? If the row size > exceeds > the page size, IDS will require another page, or more, depending on whether > the page size is 2K or 4K, thus requiring addition read(s), write(s) and > additional memory consumption. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf303ea622fe53c104c5882471

> > On Mon, Jul 23, 2012 at 7:18 PM, FRANK J. COMPUTER > <frank_in_pr@hotmail.com>wrote: > > Most IDS page sizes are 4K, but some are 2K depending on the hardware and > > IDS version. > On Mon, Jul 23, 2012 at 5:07 PM, Art Kagel <art.kagel@gmail.com> wrote: > Actually Frank, only AIX, Windows, and MAC OS-X use 4K pages as the > default. All other platforms use a 2K page as the default. You can define > larger page sizes in multiples of the platform default page up to 32K pages > for all versions from 10.00 and later. > Up to 16 K, I think you'll find. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --bcaec554da9cf8913104c58921c8

> > On Mon, Jul 23, 2012 at 7:18 PM, FRANK J. COMPUTER > <frank_in_pr@hotmail.com>wrote: > > Most IDS page sizes are 4K, but some are 2K depending on the hardware and > > IDS version. > >On Mon, Jul 23, 2012 at 5:07 PM, Art Kagel <art.kagel@gmail.com> wrote: > Actually Frank, only AIX, Windows, and MAC OS-X use 4K pages as the > default. All other platforms use a 2K page as the default. You can define > larger page sizes in multiples of the platform default page up to 32K pages > for all versions from 10.00 and later. > >Up to 16 K, I think you'll find. Thanks for the correction, I'll remember that. Since I'm testing 11.70.TC5 on Windows, would there be any benefit in increasing the pagesize to 16K? Does a larger pagesize mean that I can fit more rows read into a larger page size to increase performance? My hardisk was formatted 64K cluster size.

Thanks for everyone, Take a look at this: create table "suporte".wb_teste_email ( mensagem lvarchar(30000) ) extent size 742 next size 76 lock mode row; That's a ERP table. But a got a lot of tables aus_???? and ph_???? (sysadmin tables) that shows the same thing?!?!? Ok, I got it. Now I know what I can do with this table above. But how can I manage the sysadmin tables like those? Thanks again André Luiz Rufino > To: ids@iiug.org > From: frank_in_pr@hotmail.com > Subject: Re: Page size x Row Size [27775] > Date: Mon, 23 Jul 2012 21:34:30 -0400 > > > > > On Mon, Jul 23, 2012 at 7:18 PM, FRANK J. COMPUTER > > <frank_in_pr@hotmail.com>wrote: > > > Most IDS page sizes are 4K, but some are 2K depending on the hardware and > > > IDS version. > > > > >On Mon, Jul 23, 2012 at 5:07 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > Actually Frank, only AIX, Windows, and MAC OS-X use 4K pages as the > > default. All other platforms use a 2K page as the default. You can define > > larger page sizes in multiples of the platform default page up to 32K pages > > for all versions from 10.00 and later. > > > > >Up to 16 K, I think you'll find. > > Thanks for the correction, I'll remember that. Since I'm testing 11.70.TC5 on > Windows, would there be any benefit in increasing the pagesize to 16K? Does a > larger pagesize mean that I can fit more rows read into a larger page size to > increase performance? My hardisk was formatted 64K cluster size. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Keep in mind that VARCHAR and LVARCHAR are variable length, so the actual row sizes are likely to be much smaller than the defined column length and if you have MAX_FILL_DATA_PAGES set to 1 in your ONCONFIG file then rows will continue to be added to a page as long as the actual length of the row fits until more than 90% of the page would be consumed by the insert (with MAX_FILL_DATA_PAGES set to 0 or unset rows are only added to a page if the maximum length of the row would fit). On one system I know, aud_cmd_list, which has an LVARCHAR(8192) column, the actual average lenght of the aus_cmd_exe column in the table is 95 bytes with a minimum of 44 and a maximum of 1114 bytes, so there are from 2 to 45 rows on a page even though the maximum length of the variable length row would take up almost four full pages at 2K/page. That said, you can move the sysadmin database to a dbspace with wider pages if your experience is different. You can find a link to a developer works article about moving sysadmin in the forums if you search. 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 Tue, Jul 24, 2012 at 8:25 AM, André Luiz Rufino <andre_rufino@msn.com>wrote: > Thanks for everyone, > Take a look at this: > create table "suporte".wb_teste_email ( mensagem lvarchar(30000) ) extent > size > 742 next size 76 lock mode row; > That's a ERP table. But a got a lot of tables aus_???? and ph_???? > (sysadmin > tables) that shows the same thing?!?!? > Ok, I got it. Now I know what I can do with this table above. But how can I > manage the sysadmin tables like those? > > Thanks again > André Luiz Rufino > > To: ids@iiug.org > > From: frank_in_pr@hotmail.com > > Subject: Re: Page size x Row Size [27775] > > Date: Mon, 23 Jul 2012 21:34:30 -0400 > > > > > > > > On Mon, Jul 23, 2012 at 7:18 PM, FRANK J. COMPUTER > > > <frank_in_pr@hotmail.com>wrote: > > > > Most IDS page sizes are 4K, but some are 2K depending on the hardware > and > > > > IDS version. > > > > > > > >On Mon, Jul 23, 2012 at 5:07 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > Actually Frank, only AIX, Windows, and MAC OS-X use 4K pages as the > > > default. All other platforms use a 2K page as the default. You can > define > > > larger page sizes in multiples of the platform default page up to 32K > pages > > > for all versions from 10.00 and later. > > > > > > > >Up to 16 K, I think you'll find. > > > > Thanks for the correction, I'll remember that. Since I'm testing > 11.70.TC5 > on > > Windows, would there be any benefit in increasing the pagesize to 16K? > Does > a > > larger pagesize mean that I can fit more rows read into a larger page > size > to > > increase performance? My hardisk was formatted 64K cluster size. > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf30244ca3b1670504c59306e4

Hello, Andre. You could get the scripts needed to rebuild your sysadmin database, in the path below: $INFORMIXDIR/etc/sysadmin/ There are scripts made to build the tasks, monitors, and also for AUS tables. You could easily change them to your needs, including rebuiliding it in another dbspace ;) There is a tutorial about sysadmin rebuild, in Developerworks website. It´s very easy to go. Hope it helps. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator - Cleartech Ltda BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: andre_rufino@msn.com > Subject: RE: Page size x Row Size [27787] > Date: Tue, 24 Jul 2012 08:25:50 -0400 > > Thanks for everyone, > Take a look at this: > create table "suporte".wb_teste_email ( mensagem lvarchar(30000) ) extent size > 742 next size 76 lock mode row; > That's a ERP table. But a got a lot of tables aus_???? and ph_???? (sysadmin > tables) that shows the same thing?!?!? > Ok, I got it. Now I know what I can do with this table above. But how can I > manage the sysadmin tables like those? > > Thanks again > André Luiz Rufino > > To: ids@iiug.org > > From: frank_in_pr@hotmail.com > > Subject: Re: Page size x Row Size [27775] > > Date: Mon, 23 Jul 2012 21:34:30 -0400 > > > > > > > > On Mon, Jul 23, 2012 at 7:18 PM, FRANK J. COMPUTER > > > <frank_in_pr@hotmail.com>wrote: > > > > Most IDS page sizes are 4K, but some are 2K depending on the hardware > and > > > > IDS version. > > > > > > > >On Mon, Jul 23, 2012 at 5:07 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > Actually Frank, only AIX, Windows, and MAC OS-X use 4K pages as the > > > default. All other platforms use a 2K page as the default. You can define > > > larger page sizes in multiples of the platform default page up to 32K > pages > > > for all versions from 10.00 and later. > > > > > > > >Up to 16 K, I think you'll find. > > > > Thanks for the correction, I'll remember that. Since I'm testing 11.70.TC5 > on > > Windows, would there be any benefit in increasing the pagesize to 16K? Does > a > > larger pagesize mean that I can fit more rows read into a larger page size > to > > increase performance? My hardisk was formatted 64K cluster size. > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >