Outrageous Extent sizes

Hi All, Yesterday, I added a 5GB chunk to a 76GB dbspace. The dbspace usually uses 5GB in about a year. This morning, I received warnings that the dbspace was nearly full, and when I checked, the *entire* dbspace had been allocated -- 0 bytes free. 5 GB in ONE DAY! Using Server Studio, I discovered that 5 tables had eaten up all the space. This made no sense to me, since only one of these tables is a heavy hitter. For example: Table A was allocated 1.25 GB. The table was 9.5 GB before the additional space, and has Next Extent set to 2.5 MB, with a total of 286 extents (yes, we should fix that at some point). Table B was allocated 1.91 GB. The table was 15 GB before the additional space, and has Next Extent set to 976.5 MB MB, with a total of 188 extents. After 'table shrink', Table A now has an extent on the new chunk of 30.4 MB! Table B also dropped to 35 MB --- but almost immediately was extended back out to 1.91 GB. (Table A did not get resized back up to 1.25 GB, holding steady at 30.4 MB) Anyone have an idea as to why Informix is ignoring the Next Extent size parameter, and how it is deciding to size these new extents on its own? If Informix is just running rampant on its own, how can we as DBAs make any accurate predictions on space allocation and best use of fragmentation and dbspaces? Thanks, Michael Hoffman

My apologies, it's been a rough morning.... Informix 12.10.FC4 running on Solaris 10.

Informix used to double the NEXT SIZE setting for a table/partition after the allocation of every 16th extent. At some point they changed that to a more aggressive algorithm: - Each extent doubles the NEXT SIZE until the table's total alocation reaches 128KB or more, at that point: - NEXT SIZE doubles before the current space completely fills whenever the free space within the existing allocated space becomes less than 10% free. See this link: https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_ adr_0299.htm That's probably what's happening. 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, Aug 24, 2017 at 11:00 AM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > Hi All, > Yesterday, I added a 5GB chunk to a 76GB dbspace. The dbspace usually uses > 5GB > in about a year. > > This morning, I received warnings that the dbspace was nearly full, and > when I > checked, the *entire* dbspace had been allocated -- 0 bytes free. 5 GB in > ONE > DAY! > > Using Server Studio, I discovered that 5 tables had eaten up all the space. > This made no sense to me, since only one of these tables is a heavy hitter. > > For example: > Table A was allocated 1.25 GB. The table was 9.5 GB before the additional > space, and has Next Extent set to 2.5 MB, with a total of 286 extents > (yes, we > should fix that at some point). > > Table B was allocated 1.91 GB. The table was 15 GB before the additional > space, and has Next Extent set to 976.5 MB MB, with a total of 188 extents. > > After 'table shrink', Table A now has an extent on the new chunk of 30.4 > MB! > Table B also dropped to 35 MB --- but almost immediately was extended back > out > to 1.91 GB. > (Table A did not get resized back up to 1.25 GB, holding steady at 30.4 MB) > > Anyone have an idea as to why Informix is ignoring the Next Extent size > parameter, and how it is deciding to size these new extents on its own? > > If Informix is just running rampant on its own, how can we as DBAs make any > accurate predictions on space allocation and best use of fragmentation and > dbspaces? > > Thanks, > Michael Hoffman > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Also check sysmaster:sysextents to see the current list of extents for each table. Regards, David. > On 24 August 2017 at 16:17 Art Kagel <art.kagel@gmail.com> wrote: > > > Informix used to double the NEXT SIZE setting for a table/partition after > the allocation of every 16th extent. At some point they changed that to a > more aggressive algorithm: > > - Each extent doubles the NEXT SIZE until the table's total alocation > > reaches 128KB or more, at that point: > > - NEXT SIZE doubles before the current space completely fills whenever > > the free space within the existing allocated space becomes less than 10% > > free. > > See this link: > > https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_ adr_0299.htm > > That's probably what's happening. > > 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, Aug 24, 2017 at 11:00 AM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > > > Hi All, > > Yesterday, I added a 5GB chunk to a 76GB dbspace. The dbspace usually uses > > 5GB > > in about a year. > > > > This morning, I received warnings that the dbspace was nearly full, and > > when I > > checked, the *entire* dbspace had been allocated -- 0 bytes free. 5 GB in > > ONE > > DAY! > > > > Using Server Studio, I discovered that 5 tables had eaten up all the space. > > This made no sense to me, since only one of these tables is a heavy hitter. > > > > For example: > > Table A was allocated 1.25 GB. The table was 9.5 GB before the additional > > space, and has Next Extent set to 2.5 MB, with a total of 286 extents > > (yes, we > > should fix that at some point). > > > > Table B was allocated 1.91 GB. The table was 15 GB before the additional > > space, and has Next Extent set to 976.5 MB MB, with a total of 188 extents. > > > > After 'table shrink', Table A now has an extent on the new chunk of 30.4 > > MB! > > Table B also dropped to 35 MB --- but almost immediately was extended back > > out > > to 1.91 GB. > > (Table A did not get resized back up to 1.25 GB, holding steady at 30.4 MB) > > > > Anyone have an idea as to why Informix is ignoring the Next Extent size > > parameter, and how it is deciding to size these new extents on its own? > > > > If Informix is just running rampant on its own, how can we as DBAs make any > > accurate predictions on space allocation and best use of fragmentation and > > dbspaces? > > > > Thanks, > > Michael Hoffman > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks Art. That helps explain part of the issue -- especially with the larger table. 976.5 MB *2 = 1.90 GB. However, the smaller table still makes no sense. And now I have evidence of another table having the same issue. Table C: 4.3 GB needing another extent. NEXT size is 2.5 MB. According to the document, the NEXT size should be doubled, up to 128KB, and once there, doubled only when "the remaining space in the table is less than 10% of the total allocated space in the table". So the NEXT extent should be 5 MB, right? Well then, why is the new extent **640 MB**? That is 15% of the table size. Is Informix continuing to double the NEXT size until it crosses the 10% threshold before actually allocating the extent? That would actually explain the behavior for Table A as well (1.25GB is 13% of 9.5GB) The documentation is definitely not clear on this. (Not to mention the potentially wasted Free space for tables that may be front-loaded with data, but barely grow in the future.) Thanks, Michael On Thu, Aug 24, 2017 at 9:17 AM, Art Kagel <art.kagel@gmail.com> wrote: Informix used to double the NEXT SIZE setting for a table/partition after the allocation of every 16th extent. At some point they changed that to a more aggressive algorithm: - Each extent doubles the NEXT SIZE until the table's total alocation reaches 128KB or more, at that point: - NEXT SIZE doubles before the current space completely fills whenever the free space within the existing allocated space becomes less than 10% free. See this link: https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_ adr_0299.htm That's probably what's happening. 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, Aug 24, 2017 at 11:00 AM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > Hi All, > Yesterday, I added a 5GB chunk to a 76GB dbspace. The dbspace usually uses > 5GB > in about a year. > > This morning, I received warnings that the dbspace was nearly full, and > when I > checked, the *entire* dbspace had been allocated -- 0 bytes free. 5 GB in > ONE > DAY! > > Using Server Studio, I discovered that 5 tables had eaten up all the space. > This made no sense to me, since only one of these tables is a heavy hitter. > > For example: > Table A was allocated 1.25 GB. The table was 9.5 GB before the additional > space, and has Next Extent set to 2.5 MB, with a total of 286 extents > (yes, we > should fix that at some point). > > Table B was allocated 1.91 GB. The table was 15 GB before the additional > space, and has Next Extent set to 976.5 MB MB, with a total of 188 extents. > > After 'table shrink', Table A now has an extent on the new chunk of 30.4 > MB! > Table B also dropped to 35 MB --- but almost immediately was extended back > out > to 1.91 GB. > (Table A did not get resized back up to 1.25 GB, holding steady at 30.4 MB) > > Anyone have an idea as to why Informix is ignoring the Next Extent size > parameter, and how it is deciding to size these new extents on its own? > > If Informix is just running rampant on its own, how can we as DBAs make any > accurate predictions on space allocation and best use of fragmentation and > dbspaces? > > Thanks, > Michael Hoffman > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Evidence of runaway extents for Table C: From sysmaster.sysptext table, you see the sizes double at regular intervals (nearly every 16th, as expected). Then the extent sizes are all over the place due to lack of contiguous free space to hold a larger extent. Understandable! But then the final extent allocated is so incredibly large compared to any others (80MB --> 640MB!). But now I see .... even though the document says the doubling happens at *every allocation*, it still seems to have an "every 16th" pattern. At 128 extents, the size has doubled to 640MB. So at extent 160, the doubling could take it to 2.5GB, but since 640MB would provide more than 10% free of the overall table size, Informix stops there. This aggressive approach can be a real headache for DBAs running on systems that are space-constrained. A large table that rarely grabs a new extent can all of a sudden snag a HUGE amount of space, even if the table is rarely written to. And does the NEXT EXTENT have *any* meaning at all anymore? pe_extnum pe_chunk pe_size ---------- -------- -------- 0 30 8 1 48 796160 2 30 1280 3 30 1280 4 30 1280 5 30 1280 6 30 1280 7 30 1280 8 30 1280 9 30 1280 10 30 1280 11 30 1280 12 30 1280 13 30 1280 14 30 1280 15 30 1280 16 30 2560 17 30 2560 18 30 2560 19 30 2560 20 30 2560 21 30 2560 22 30 2560 23 30 2560 24 30 2560 25 30 2560 26 30 2560 27 30 2560 28 30 5120 29 30 2560 30 30 5120 31 30 2560 32 30 5120 33 38 5120 34 38 5120 35 38 5120 36 38 5120 37 38 5120 38 38 5120 39 38 5120 40 38 5120 41 38 5120 42 38 5120 43 38 5120 44 38 5120 45 38 5120 46 38 5120 47 38 5120 48 38 10240 49 38 10240 50 38 10240 51 38 10240 52 48 10240 53 48 10240 54 48 10240 55 48 10240 56 48 10240 57 48 10240 58 48 10240 59 48 10240 60 48 10240 61 48 10240 62 48 10240 63 48 10240 64 48 20480 65 48 20480 66 55 20480 67 55 20480 68 55 20480 69 55 20480 70 55 20480 71 55 20480 72 55 20480 73 55 20480 74 55 20480 75 55 20480 76 30 20480 77 30 20480 78 30 20480 79 30 20480 80 38 40960 81 48 40960 82 48 40960 83 55 40960 84 55 40960 85 55 40960 86 55 40960 87 55 40960 88 55 40960 89 55 40960 90 55 40960 91 48 30976 92 38 19484 93 48 18048 94 48 16416 95 38 16023 96 55 15182 97 48 13823 98 38 13048 99 38 12288 100 48 11248 101 38 10876 102 48 9846 103 48 9524 104 38 8262 105 48 8040 106 48 7765 107 38 6854 108 48 6391 109 30 6263 110 48 6152 111 38 5760 112 55 5572 113 38 5442 114 38 5146 115 38 4842 116 55 4704 117 55 4589 118 48 4464 119 48 4272 120 30 4096 121 55 4096 122 48 3978 123 48 3936 124 38 3530 125 38 3264 126 48 3121 127 30 3087 128 38 3084 129 38 3072 130 38 3008 131 48 2868 132 55 2680 133 48 2640 134 48 2608 135 38 2495 136 48 2372 137 38 2280 138 48 2235 139 38 2158 140 38 2024 141 30 1870 142 38 1715 143 38 1681 144 30 1628 145 48 1608 146 48 1600 147 48 1540 148 48 1534 149 38 1450 150 48 1419 151 48 1408 152 30 1376 153 48 1328 154 38 1282 155 30 1206 156 30 1144 157 48 1069 158 30 1068 159 38 1068 160 76 327680 > -------------------------------------------- > Thanks Art. That helps explain part of the issue -- especially with the > larger table. 976.5 MB *2 = 1.90 GB. > > However, the smaller table still makes no sense. And now I have evidence > of another table having the same issue. > > Table C: 4.3 GB needing another extent. NEXT size is 2.5 MB. > > According to the document, the NEXT size should be doubled, up to 128KB, > and once there, doubled only when "the remaining space in the table is > less than 10% of the total allocated space in the table". > > So the NEXT extent should be 5 MB, right? Well then, why is the new extent > **640 MB**? That is 15% of the table size. > > Is Informix continuing to double the NEXT size until it crosses the 10% > threshold before actually allocating the extent? > > That would actually explain the behavior for Table A as well (1.25GB is 13% > of 9.5GB) > > The documentation is definitely not clear on this. (Not to mention the > potentially wasted Free space for tables that may be front-loaded with data, > but barely grow in the future.) > > Thanks, > Michael

The general idea is to monitor this and not let the number of extents get out of hand. Plan space usage and increase the next extent size as needed. Also use alter fragment to rebuild the table with larger extents then if it is known the table will not grow much then reduce the next extent size. Regards, David. > On 24 August 2017 at 18:48 MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > > > Evidence of runaway extents for Table C: > >From sysmaster.sysptext table, you see the sizes double at regular intervals > (nearly every 16th, as expected). Then the extent sizes are all over the place > due to lack of contiguous free space to hold a larger extent. Understandable! > But then the final extent allocated is so incredibly large compared to any > others (80MB --> 640MB!). > > But now I see .... even though the document says the doubling happens at > *every allocation*, it still seems to have an "every 16th" pattern. At 128 > extents, the size has doubled to 640MB. So at extent 160, the doubling could > take it to 2.5GB, but since 640MB would provide more than 10% free of the > overall table size, Informix stops there. > > This aggressive approach can be a real headache for DBAs running on systems > that are space-constrained. A large table that rarely grabs a new extent can > all of a sudden snag a HUGE amount of space, even if the table is rarely > written to. > > And does the NEXT EXTENT have *any* meaning at all anymore? > > pe_extnum pe_chunk pe_size > ---------- -------- -------- > > 0 30 8 > > 1 48 796160 > > 2 30 1280 > > 3 30 1280 > > 4 30 1280 > > 5 30 1280 > > 6 30 1280 > > 7 30 1280 > > 8 30 1280 > > 9 30 1280 > > 10 30 1280 > > 11 30 1280 > > 12 30 1280 > > 13 30 1280 > > 14 30 1280 > > 15 30 1280 > > 16 30 2560 > > 17 30 2560 > > 18 30 2560 > > 19 30 2560 > > 20 30 2560 > > 21 30 2560 > > 22 30 2560 > > 23 30 2560 > > 24 30 2560 > > 25 30 2560 > > 26 30 2560 > > 27 30 2560 > > 28 30 5120 > > 29 30 2560 > > 30 30 5120 > > 31 30 2560 > > 32 30 5120 > > 33 38 5120 > > 34 38 5120 > > 35 38 5120 > > 36 38 5120 > > 37 38 5120 > > 38 38 5120 > > 39 38 5120 > > 40 38 5120 > > 41 38 5120 > > 42 38 5120 > > 43 38 5120 > > 44 38 5120 > > 45 38 5120 > > 46 38 5120 > > 47 38 5120 > > 48 38 10240 > > 49 38 10240 > > 50 38 10240 > > 51 38 10240 > > 52 48 10240 > > 53 48 10240 > > 54 48 10240 > > 55 48 10240 > > 56 48 10240 > > 57 48 10240 > > 58 48 10240 > > 59 48 10240 > > 60 48 10240 > > 61 48 10240 > > 62 48 10240 > > 63 48 10240 > > 64 48 20480 > > 65 48 20480 > > 66 55 20480 > > 67 55 20480 > > 68 55 20480 > > 69 55 20480 > > 70 55 20480 > > 71 55 20480 > > 72 55 20480 > > 73 55 20480 > > 74 55 20480 > > 75 55 20480 > > 76 30 20480 > > 77 30 20480 > > 78 30 20480 > > 79 30 20480 > > 80 38 40960 > > 81 48 40960 > > 82 48 40960 > > 83 55 40960 > > 84 55 40960 > > 85 55 40960 > > 86 55 40960 > > 87 55 40960 > > 88 55 40960 > > 89 55 40960 > > 90 55 40960 > > 91 48 30976 > > 92 38 19484 > > 93 48 18048 > > 94 48 16416 > > 95 38 16023 > > 96 55 15182 > > 97 48 13823 > > 98 38 13048 > > 99 38 12288 > > 100 48 11248 > > 101 38 10876 > > 102 48 9846 > > 103 48 9524 > > 104 38 8262 > > 105 48 8040 > > 106 48 7765 > > 107 38 6854 > > 108 48 6391 > > 109 30 6263 > > 110 48 6152 > > 111 38 5760 > > 112 55 5572 > > 113 38 5442 > > 114 38 5146 > > 115 38 4842 > > 116 55 4704 > > 117 55 4589 > > 118 48 4464 > > 119 48 4272 > > 120 30 4096 > > 121 55 4096 > > 122 48 3978 > > 123 48 3936 > > 124 38 3530 > > 125 38 3264 > > 126 48 3121 > > 127 30 3087 > > 128 38 3084 > > 129 38 3072 > > 130 38 3008 > > 131 48 2868 > > 132 55 2680 > > 133 48 2640 > > 134 48 2608 > > 135 38 2495 > > 136 48 2372 > > 137 38 2280 > > 138 48 2235 > > 139 38 2158 > > 140 38 2024 > > 141 30 1870 > > 142 38 1715 > > 143 38 1681 > > 144 30 1628 > > 145 48 1608 > > 146 48 1600 > > 147 48 1540 > > 148 48 1534 > > 149 38 1450 > > 150 48 1419 > > 151 48 1408 > > 152 30 1376 > > 153 48 1328 > > 154 38 1282 > > 155 30 1206 > > 156 30 1144 > > 157 48 1069 > > 158 30 1068 > > 159 38 1068 > > 160 76 327680 > > > -------------------------------------------- > > Thanks Art. That helps explain part of the issue -- especially with the > > larger table. 976.5 MB *2 = 1.90 GB. > > > > However, the smaller table still makes no sense. And now I have evidence > > of another table having the same issue. > > > > Table C: 4.3 GB needing another extent. NEXT size is 2.5 MB. > > > > According to the document, the NEXT size should be doubled, up to 128KB, > > and once there, doubled only when "the remaining space in the table is > > less than 10% of the total allocated space in the table". > > > > So the NEXT extent should be 5 MB, right? Well then, why is the new extent > > **640 MB**? That is 15% of the table size. > > > > Is Informix continuing to double the NEXT size until it crosses the 10% > > threshold before actually allocating the extent? > > > > That would actually explain the behavior for Table A as well (1.25GB is 13% > > of 9.5GB) > > > > The documentation is definitely not clear on this. (Not to mention the > > potentially wasted Free space for tables that may be front-loaded with data, > > but barely grow in the future.) > > > > Thanks, > > Michael > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Still every 16th, interesting. Explains why I have not noticed a series of extent size jumps over and over since I read that doc change. Thought it was just coincidence. 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, Aug 24, 2017 at 12:01 PM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > Thanks Art. That helps explain part of the issue -- especially with the > larger > table. 976.5 MB *2 = 1.90 GB. > > However, the smaller table still makes no sense. And now I have evidence of > another table having the same issue. > > Table C: 4.3 GB needing another extent. NEXT size is 2.5 MB. > > According to the document, the NEXT size should be doubled, up to 128KB, > and > once there, doubled only when "the remaining space in the table is less > than > 10% of the total allocated space in the table". > > So the NEXT extent should be 5 MB, right? Well then, why is the new extent > **640 MB**? That is 15% of the table size. > > Is Informix continuing to double the NEXT size until it crosses the 10% > threshold before actually allocating the extent? > > That would actually explain the behavior for Table A as well (1.25GB is > 13% of > 9.5GB) > > The documentation is definitely not clear on this. (Not to mention the > potentially wasted Free space for tables that may be front-loaded with > data, > but barely grow in the future.) > > Thanks, > Michael > > On Thu, Aug 24, 2017 at 9:17 AM, Art Kagel <art.kagel@gmail.com> wrote: > Informix used to double the NEXT SIZE setting for a table/partition after > the allocation of every 16th extent. At some point they changed that to a > more aggressive algorithm: > > - Each extent doubles the NEXT SIZE until the table's total alocation > > reaches 128KB or more, at that point: > > - NEXT SIZE doubles before the current space completely fills whenever > > the free space within the existing allocated space becomes less than 10% > > free. > > See this link: > > > https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/co > m.ibm.adref.doc/ids_adr_0299.htm > > That's probably what's happening. > > 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, Aug 24, 2017 at 11:00 AM, MICHAEL HOFFMAN <offdisc@gmail.com> > wrote: > > > Hi All, > > Yesterday, I added a 5GB chunk to a 76GB dbspace. The dbspace usually > uses > > 5GB > > in about a year. > > > > This morning, I received warnings that the dbspace was nearly full, and > > when I > > checked, the *entire* dbspace had been allocated -- 0 bytes free. 5 GB in > > ONE > > DAY! > > > > Using Server Studio, I discovered that 5 tables had eaten up all the > space. > > This made no sense to me, since only one of these tables is a heavy > hitter. > > > > For example: > > Table A was allocated 1.25 GB. The table was 9.5 GB before the additional > > space, and has Next Extent set to 2.5 MB, with a total of 286 extents > > (yes, we > > should fix that at some point). > > > > Table B was allocated 1.91 GB. The table was 15 GB before the additional > > space, and has Next Extent set to 976.5 MB MB, with a total of 188 > extents. > > > > After 'table shrink', Table A now has an extent on the new chunk of 30.4 > > MB! > > Table B also dropped to 35 MB --- but almost immediately was extended > back > > out > > to 1.91 GB. > > (Table A did not get resized back up to 1.25 GB, holding steady at 30.4 > MB) > > > > Anyone have an idea as to why Informix is ignoring the Next Extent size > > parameter, and how it is deciding to size these new extents on its own? > > > > If Informix is just running rampant on its own, how can we as DBAs make > any > > accurate predictions on space allocation and best use of fragmentation > and > > dbspaces? > > > > Thanks, > > Michael Hoffman > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

David, And there's the rub. The new allocation method says that doubling happens "on every allocation", not related to the number of extents. Setting the NEXT size provides a minimum allocation for Informix to use, but it will still check the "10% free of new overall table size" once the NEXT size is greater than 128KB. This is great for really small tables (how many of your tables are under 128KB these days?), but for large tables, no matter how much you plan, any new extents are going to be extreme! For a 30GB table, your auto-generated next extent will be at least 3GB! That's outrageous for a slowly growing table. Unless a DBA is prepared to set NEXT size to always be just over 10% of the table size, that value will be ignored whenever Informix creates a new extent. BTW, on large tables that are frequently written to, running "alter fragment" to rebuild tables is not really a valid option in a 24/7/365 world. Even though it runs in the background, it does put exclusive locks on the records it is moving around, and it eats logical logs like candy! Thanks, Michael =================================================== > The general idea is to monitor this and not let the number of extents get > out of hand. > > Plan space usage and increase the next extent size as needed. > > Also use alter fragment to rebuild the table with larger extents then if it > is known the table will not grow much then reduce the next extent size. > > Regards, > David.

On the last point, you can use the DEFRAGMENT API function which does not lock the rows since it moves a page at a time and only locks that single 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 Thu, Aug 24, 2017 at 2:13 PM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > David, > And there's the rub. The new allocation method says that doubling happens > "on > every allocation", not related to the number of extents. Setting the NEXT > size > provides a minimum allocation for Informix to use, but it will still check > the > "10% free of new overall table size" once the NEXT size is greater than > 128KB. > > This is great for really small tables (how many of your tables are under > 128KB > these days?), but for large tables, no matter how much you plan, any new > extents are going to be extreme! For a 30GB table, your auto-generated next > extent will be at least 3GB! That's outrageous for a slowly growing table. > > Unless a DBA is prepared to set NEXT size to always be just over 10% of the > table size, that value will be ignored whenever Informix creates a new > extent. > > BTW, on large tables that are frequently written to, running "alter > fragment" > to rebuild tables is not really a valid option in a 24/7/365 world. Even > though it runs in the background, it does put exclusive locks on the > records > it is moving around, and it eats logical logs like candy! > > Thanks, > Michael > > =================================================== > > The general idea is to monitor this and not let the number of extents get > > out of hand. > > > > Plan space usage and increase the next extent size as needed. > > > > Also use alter fragment to rebuild the table with larger extents then if > it > > is known the table will not grow much then reduce the next extent size. > > > > Regards, > > David. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Art, Aren't Page Locks *worse* than Row locks? That would lock every row on the page, wouldn't it? Besides, it still doesn't affect the logical log usage; which in my case means not being able to run it overnight -- no one staffed to change the tapes. :-( Thanks, Mike

Art & David, Seeing as the issue really pertains to slowly-growing large tables, is there a way to turn OFF Extent Size doubling? Either on the database or table level? I'd even take instance level. As David points out, if I am doing my DBA job, I have already done due diligence and worked out how fast my tables grow, and what the appropriate NEXT size should be based on that growth. There should be a way to force Informix to acknowledge that work, to acknowledge that maybe the DBA knows their data patterns better than IBM does. Thanks, Michael

Mike: OK, several points: 1. It only locks one page at a time, versus alter fragment which has to lock either the whole table or if ONLINE then all pages touched. 2. You should have enough logical logs to get through a long weekend without archiving the logs. That's my rule of thumb that I impress on all of my clients. 3. You should have logical logs backup up to disk automatically as they fill using the ALARMPROGRAM with ontape -a or onbar -b and then have the file system holding those backups archived to take or offline disk storage one to a few times a day. Either that or use a storage manager like Netbackup etc. to auto archive again using the ALARMPROGRAM and onbar. 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, Aug 24, 2017 at 2:30 PM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > Art, > Aren't Page Locks *worse* than Row locks? That would lock every row on the > page, wouldn't it? > Besides, it still doesn't affect the logical log usage; which in my case > means > not being able to run it overnight -- no one staffed to change the tapes. > :-( > > Thanks, > Mike > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

I'm not aware of any way to disable the doubling. 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, Aug 24, 2017 at 2:53 PM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > Art & David, > Seeing as the issue really pertains to slowly-growing large tables, is > there a > way to turn OFF Extent Size doubling? Either on the database or table > level? > I'd even take instance level. > > As David points out, if I am doing my DBA job, I have already done due > diligence and worked out how fast my tables grow, and what the appropriate > NEXT size should be based on that growth. > > There should be a way to force Informix to acknowledge that work, to > acknowledge that maybe the DBA knows their data patterns better than IBM > does. > > Thanks, > Michael > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Art, Apologies for the confusion about the DEFRAGMENT versus Alter Fragment. My mind was in a different place -- I was running a Table Repack Shrink at the time, which locks record by record and I was forced to abort due to interference with searches. As to the logical logs, oh yes, you are preaching to the choir. In fact, I am passing your notes up my chain of command to help enforce what my throat has grown raspy trying to convince them to do. Unfortunately, disk space is at a bit of a premium (thus not enough logs for a long weekend) and they still do not trust logging to disk. I can only shake my head and stare blankly. :-( Sometimes that's the price of working for a Gov't agency, everything moves in slow motion. Thanks, Michael