12.10 From 11.50 Using 40% More Space

Migrating from 11.50 FC6 to 12.10 FC5 AIX 7.1 and noticed after importing the database with dbimport that the storage space used on the 12.10 instance increased by more than 40 percent. The export did not specify extent sizes (-ss option) for the tables. Any ideas why this might be? Do I need to specify a different option with dbimport? I did not see anything new with the dbimport documentation. Here is the (before and after) onstat -d from both instances. Compare the chunk /dev/rd/d5dbs (11.50) to /dev/rd/d1210dbs (12.10). Both are 256gb raw devices and the only chunk in the ddbs space... IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 1 days 16:57:09 -- 22271696 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 700000463275028 1 0x60001 1 1 4096 N B informix rdbs 700000463275e38 2 0x60001 2 1 4096 N B informix pdbs 700000465037028 3 0x60001 3 1 4096 N B informix ldbs 7000004650371c0 4 0x42001 4 1 4096 N TB informix tdbs 700000465037358 5 0x60001 5 1 4096 N B informix ddbs 5 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname 7000004632751c0 1 1 0 262144 122779 PO-B- /dev/rd/r5dbs 7000004650374f0 2 2 0 524288 524235 PO-B- /dev/rd/p5dbs 7000004650376e0 3 3 0 2097152 1048523 PO-B- /dev/rd/l5dbs 7000004650378d0 4 4 0 4194304 4191617 PO-B- /dev/rd/t5dbs 700000465037ac0 5 5 0 67108864 24434487 PO-B- /dev/rd/d5dbs 5 active, 32766 maximum NOTE: The values in the "size" and "free" columns for DBspace chunks are displayed in terms of "pgsize" of the DBspace to which they belong. Expanded chunk capacity mode: always IBM Informix Dynamic Server Version 12.10.FC5AEE -- On-Line -- Up 00:31:36 --22079792 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 700000033320028 1 0x4070001 1 1 4096 N B informix rdbs 7000000340f3590 2 0x4070001 2 1 4096 N B informix pdbs 7000000340f37c0 3 0x4060001 3 1 4096 N B informix ldbs 7000000340f39f0 4 0x42001 4 1 4096 N TB informix tdbs 7000000340f3c20 5 0x4060001 5 1 4096 N B informix ddbs 5 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname 700000033320258 1 1 0 262144 253563 PO-B-- /dev/rd/r1210dbs 7000000354da028 2 2 0 524288 393163 PO-B-- /dev/rd/p1210dbs 7000000354db028 3 3 0 2097152 1048523 PO-B-- /dev/rd/l1210dbs 7000000354dc028 4 4 0 4194304 4194243 PO-B-- /dev/rd/t1210dbs 7000000354dd028 5 5 0 67108864 6042128 PO-B-- /dev/rd/d1210dbs 5 active, 32766 maximum NOTE: The values in the "size" and "free" columns for DBspace chunks are displayed in terms of "pgsize" of the DBspace to which they belong. Expanded chunk capacity mode: always

Part of it could be variable length columns. If you had varchars and lvarchars in the 11.50 server that had grown over time, they may fit <N> rows on a page but when you reloaded into v12.10 those tables may have taken up more pages with fewer rows on a page. Especially if you did not set MAX_FILL_DATA_PAGES in your onconfig before loading the data. When variable length rows grow in place they will stay where they started as long as there is enough space on the page to hold the longer length. But at load/insert time a variable length row will not be placed on a page unless there is enough free space after the insert to allow for future growth. Without MAX_FILL_DATA_PAGES set that free space is room for the maximum length of the new row before the insert. With this variable set the requirement is for 10% of the page free after the actual length is deducted from the amount of free space on the page. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 21, 2015 at 12:58 PM, GREGG WALKER <gregg@rrmca.com> wrote: > Migrating from 11.50 FC6 to 12.10 FC5 AIX 7.1 and noticed after importing > the > database with dbimport that the storage space used on the 12.10 instance > increased by more than 40 percent. > > The export did not specify extent sizes (-ss option) for the tables. Any > ideas > why this might be? Do I need to specify a different option with dbimport? I > did not see anything new with the dbimport documentation. > > Here is the (before and after) onstat -d from both instances. Compare the > chunk /dev/rd/d5dbs (11.50) to /dev/rd/d1210dbs (12.10). Both are 256gb raw > devices and the only chunk in the ddbs space... > > IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 1 days > 16:57:09 > -- 22271696 Kbytes> > Dbspaces > address number flags fchunk nchunks pgsize flags owner name > 700000463275028 1 0x60001 1 1 4096 N B informix rdbs > 700000463275e38 2 0x60001 2 1 4096 N B informix pdbs > 700000465037028 3 0x60001 3 1 4096 N B informix ldbs > 7000004650371c0 4 0x42001 4 1 4096 N TB informix tdbs > 700000465037358 5 0x60001 5 1 4096 N B informix ddbs > 5 active, 2047 maximum > > Chunks > address chunk/dbs offset size free bpages flags pathname > 7000004632751c0 1 1 0 262144 122779 PO-B- /dev/rd/r5dbs > 7000004650374f0 2 2 0 524288 524235 PO-B- /dev/rd/p5dbs > 7000004650376e0 3 3 0 2097152 1048523 PO-B- /dev/rd/l5dbs > 7000004650378d0 4 4 0 4194304 4191617 PO-B- /dev/rd/t5dbs > 700000465037ac0 5 5 0 67108864 24434487 PO-B- /dev/rd/d5dbs > 5 active, 32766 maximum > > NOTE: The values in the "size" and "free" columns for DBspace chunks are > > displayed in terms of "pgsize" of the DBspace to which they belong. > > Expanded chunk capacity mode: always > > IBM Informix Dynamic Server Version 12.10.FC5AEE -- On-Line -- Up 00:31:36 > --> 22079792 Kbytes > > Dbspaces > address number flags fchunk nchunks pgsize flags owner name > 700000033320028 1 0x4070001 1 1 4096 N B informix rdbs > 7000000340f3590 2 0x4070001 2 1 4096 N B informix pdbs > 7000000340f37c0 3 0x4060001 3 1 4096 N B informix ldbs > 7000000340f39f0 4 0x42001 4 1 4096 N TB informix tdbs > 7000000340f3c20 5 0x4060001 5 1 4096 N B informix ddbs > 5 active, 2047 maximum > > Chunks > address chunk/dbs offset size free bpages flags pathname > 700000033320258 1 1 0 262144 253563 PO-B-- /dev/rd/r1210dbs > 7000000354da028 2 2 0 524288 393163 PO-B-- /dev/rd/p1210dbs > 7000000354db028 3 3 0 2097152 1048523 PO-B-- /dev/rd/l1210dbs > 7000000354dc028 4 4 0 4194304 4194243 PO-B-- /dev/rd/t1210dbs > 7000000354dd028 5 5 0 67108864 6042128 PO-B-- /dev/rd/d1210dbs > 5 active, 32766 maximum > > NOTE: The values in the "size" and "free" columns for DBspace chunks are > > displayed in terms of "pgsize" of the DBspace to which they belong. > > Expanded chunk capacity mode: always > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113fe42805ac9b051b65e8e7

Art, Thank you for your quick response. That would seem to fit. MAX_FILL_DATA_PAGES was indeed turned off and we have some large tables with variable length columns. I will turn it on, re-import the data and report back the results. Sincerely, Gregg

Also, just thought of this one, set the FILLFACTOR high (say 90-95) so that your indexes do not have lots of empty space in them and make sure the indexes are created after data is loaded. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 21, 2015 at 1:37 PM, GREGG WALKER <gregg@rrmca.com> wrote: > Art, > > Thank you for your quick response. That would seem to fit. > > MAX_FILL_DATA_PAGES was indeed turned off and we have some large tables > with > variable length columns. > > I will turn it on, re-import the data and report back the results. > > Sincerely, > Gregg > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113fe428d06817051b66409a

Art, I have the FILLFACTOR set to 90 and the indices are set to be created after all the tables are loaded. Thank you! Gregg

I am sorry, but I missed if someone said if this is the space inside the tables/Index or is this just that the extents are not sized correctly and when they grow (double) the remaining free space is a lot less. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 07/21/2015 11:36:17 AM: > From: "GREGG WALKER" <gregg@rrmca.com> > To: ids@iiug.org > Date: 07/21/2015 11:36 AM > Subject: Re: 12.10 From 11.50 Using 40% More Space [35507] > Sent by: ids-bounces@iiug.org > > Art, > > I have the FILLFACTOR set to 90 and the indices are set to be created after > all the tables are loaded. > > Thank you! > > Gregg > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >

Gregg, one possible reason would be what is described in IT00771. Taken from its description: "When dbexport is used without the option "-ss" the create table statements do not include the "EXTENT SIZE" "NEXT SIZE" clauses. In this case dbimport will try to calculate an appropriate first extent size by using the maximum row size information multiplied with the number of rows for each table. When having tables with large maximum row sizes, for example when including columns of type LVARCHAR, this can end up in large first extent sizes..." The APAR was 'fixed' in 12.10.xC4 by adding an option to dbimport (-D) which tells Informix to use the default extent sizes.

Art, Unfortunately none of the settings changes (MAX_FILL_DATA_PAGES=1, FILL_FACTOR=90) made a difference. Result was the same exact storage usage. I'm going to pursue using the -D dbimport option as Wolfgang has suggested. I will report those results tomorrow. Thank you, Gregg > Part of it could be variable length columns. If you had varchars and > lvarchars in the 11.50 server that had grown over time, they may fit <N> > rows on a page but when you reloaded into v12.10 those tables may have > taken up more pages with fewer rows on a page. Especially if you did not > set MAX_FILL_DATA_PAGES in your onconfig before loading the data. > When variable length rows grow in place they will stay where they started > as long as there is enough space on the page to hold the longer length. > But at load/insert time a variable length row will not be placed on a page > unless there is enough free space after the insert to allow for future > growth. Without MAX_FILL_DATA_PAGES set that free space is room for the > maximum length of the new row before the insert. With this variable set > the requirement is for 10% of the page free after the actual length is > deducted from the amount of free space on the page.

Wofgang, Trying the import with the -D option right now. The other suggestions unfortunately did not make a difference. Thank you for explaining the -D option. Sincerely, Gregg > The APAR was 'fixed' in 12.10.xC4 by adding an option to dbimport (-D) which > tells Informix to use the default extent sizes.

John, It would appear to be an issue with over estimating the size of the initial extent for the tables being imported. I'm trying the import again with the -D option. Gregg > I am sorry, but I missed if someone said if this is the space inside the > tables/Index or is this just that the extents are not sized correctly and > when they grow (double) the remaining free space is a lot less.

Wolfgang, The results were unexpected when running dbimport with the -D option. The space required is half of the orginal space usage in the 11.50 database and over 2 1/2 times less than the space usage after importing to 12.10 without the -D option. Here's a summary of the dbspace storage usage in 11.50 and after dbimport with and without -D option: 11.50: 166,697 gb 12.10 (dbimport without -D): 238,542 gb 12.10 (dbimport with -D): 88,526 gb Many thanks to you and Art for helping out! Gregg > one possible reason would be what is described in IT00771. > Taken from its description: > "When dbexport is used without the option "-ss" the create table > statements do not include the "EXTENT SIZE" "NEXT SIZE" clauses. > In this case dbimport will try to calculate an appropriate first > extent size by using the maximum row size information multiplied > with the number of rows for each table. > When having tables with large maximum row sizes, for example > when including columns of type LVARCHAR, this can end up in > large first extent sizes..." > The APAR was 'fixed' in 12.10.xC4 by adding an option to dbimport (-D) > which tells Informix to use the default extent sizes.

GREAT! Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Thu, Jul 23, 2015 at 12:22 PM, GREGG WALKER <gregg@rrmca.com> wrote: > Wolfgang, > > The results were unexpected when running dbimport with the -D option. The > space required is half of the orginal space usage in the 11.50 database and > over 2 1/2 times less than the space usage after importing to 12.10 without > the -D option. > > Here's a summary of the dbspace storage usage in 11.50 and after dbimport > with > and without -D option: > > 11.50: 166,697 gb > 12.10 (dbimport without -D): 238,542 gb > 12.10 (dbimport with -D): 88,526 gb > > Many thanks to you and Art for helping out! > > Gregg > > > one possible reason would be what is described in IT00771. > > Taken from its description: > > > "When dbexport is used without the option "-ss" the create table > > statements do not include the "EXTENT SIZE" "NEXT SIZE" clauses. > > In this case dbimport will try to calculate an appropriate first > > extent size by using the maximum row size information multiplied > > with the number of rows for each table. > > > When having tables with large maximum row sizes, for example > > when including columns of type LVARCHAR, this can end up in > > large first extent sizes..." > > > The APAR was 'fixed' in 12.10.xC4 by adding an option to dbimport (-D) > > which tells Informix to use the default extent sizes. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01182bb0fe6f98051b8ea95a

While it would have taken much longer, remember that you can run the online command repack and shrink on your tables and indexes to reclaim the unused space in the tables and indexes. This is often very beneficial on tables sysadmin:task("table repack shrink", "table=5Fname","database=5Fname") Version 12 has been enhanced to have a more efficient repack operation, a new parallel option, an option to remove ipa (in-place alters), the ability to online re-balance indexes. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 07/23/2015 09:22:45 AM: > From: "GREGG WALKER" <gregg@rrmca.com> > To: ids@iiug.org > Date: 07/23/2015 09:23 AM > Subject: Re: 12.10 From 11.50 Using 40% More Space [35519] > Sent by: ids-bounces@iiug.org > > Wolfgang, > > The results were unexpected when running dbimport with the -D option. The > space required is half of the orginal space usage in the 11.50 database and > over 2 1/2 times less than the space usage after importing to 12.10 without > the -D option. > > Here's a summary of the dbspace storage usage in 11.50 and after > dbimport with > and without -D option: > > 11.50: 166,697 gb > 12.10 (dbimport without -D): 238,542 gb > 12.10 (dbimport with -D): 88,526 gb > > Many thanks to you and Art for helping out! > > Gregg > > > one possible reason would be what is described in IT00771. > > Taken from its description: > > > "When dbexport is used without the option "-ss" the create table > > statements do not include the "EXTENT SIZE" "NEXT SIZE" clauses. > > In this case dbimport will try to calculate an appropriate first > > extent size by using the maximum row size information multiplied > > with the number of rows for each table. > > > When having tables with large maximum row sizes, for example > > when including columns of type LVARCHAR, this can end up in > > large first extent sizes..." > > > The APAR was 'fixed' in 12.10.xC4 by adding an option to dbimport (-D) > > which tells Informix to use the default extent sizes. > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks for the heads up on that John! > While it would have taken much longer, remember that you can run the online > command repack and shrink on your tables and indexes to reclaim the unused > space in the tables and indexes. This is often very beneficial on tables > sysadmin:task("table repack shrink", "table=5Fname","database=5Fname") > Version 12 has been enhanced to have a more efficient repack operation, a > new parallel option, an option to remove ipa (in-place alters), the ability > to online re-balance indexes.