Nasty IDS 10.00.UC6 bug

FYI, we've recently encountered a particularly nasty bug that seems to first appear in IDS 10.00.UC6. It involves tables where the record size is larger than the page size. When a session that's accessing a table causes the table to need to add an extent, that session goes into a critical section and stays there for quite some time. When the table is small, it's just for a few seconds, but as the table grows (to around 200 MB, for example) this time increases dramatically. And the problem becomes more acute when a checkpoint hits. Since the checkpoint can't clear until all sessions leave their critical sections, the checkpoint hangs up waiting on the session. In my testing, when my test table got to around 200 MB, we had an 87 second (!) checkpoint. To my knowledge, IBM has not yet assigned a bug # to this issue, but we still have a PMR open. Downgrading to UC5 eliminated this problem. I also suspect you could work around this by using configurable page size to put the table in pages where a whole record fits on a page, but I have not yet tested this approach. (It took several hours just to get to a point where we realized adding extents was the problem). On systems with 2K pages, tables whose row size is < 2,020 bytes seem to work just fine; it's only when the row size exceeds the page size (and thus get remainder pages) that we start to observe the problem. If you're getting unexplained long checkpoints, this might be a place to start. This also brings up a side point: I've got the 2,020 number burned into my head as "what fits on a 2K page" from at least the 7.x days, maybe even the 5.x days. Is that still true today? Also, what's the formula for figuring out what fits on a page for larger pages? Do you deduct a flat 28 bytes, or is it some multiple of the page size? Thanks, - TJG

On Aug 28, 12:29 am, "Thomas J. Girsch" <tgir...@NOSPAM.gmail.com> wrote: > FYI, we've recently encountered a particularly nasty bug that seems to > first appear in IDS 10.00.UC6. It involves tables where the record size > is larger than the page size. When a session that's accessing a table > causes the table to need to add an extent, that session goes into a > critical section and stays there for quite some time. When the table is > small, it's just for a few seconds, but as the table grows (to around > 200 MB, for example) this time increases dramatically. And the problem > becomes more acute when a checkpoint hits. Since the checkpoint can't > clear until all sessions leave their critical sections, the checkpoint > hangs up waiting on the session. In my testing, when my test table got > to around 200 MB, we had an 87 second (!) checkpoint. > > To my knowledge, IBM has not yet assigned a bug # to this issue, but we > still have a PMR open. > > Downgrading to UC5 eliminated this problem. I also suspect you could > work around this by using configurable page size to put the table in > pages where a whole record fits on a page, but I have not yet tested > this approach. (It took several hours just to get to a point where we > realized adding extents was the problem). On systems with 2K pages, > tables whose row size is < 2,020 bytes seem to work just fine; it's only > when the row size exceeds the page size (and thus get remainder pages) > that we start to observe the problem. > > If you're getting unexplained long checkpoints, this might be a place to > start. > > This also brings up a side point: I've got the 2,020 number burned into > my head as "what fits on a 2K page" from at least the 7.x days, maybe > even the 5.x days. Is that still true today? Also, what's the formula > for figuring out what fits on a page for larger pages? Do you deduct a > flat 28 bytes, or is it some multiple of the page size? > > Thanks, > > - TJG I believe it is still 28 bytes for larger page sizes. Also, you can still only store 255 or 256 records on a page because the number of slot entries have not changed.

On Aug 28, 10:37 am, bozon <cur...@crowson1.com> wrote: > On Aug 28, 12:29 am, "Thomas J. Girsch" <tgir...@NOSPAM.gmail.com> > wrote: > > > > > FYI, we've recently encountered a particularly nasty bug that seems to > > first appear in IDS 10.00.UC6. It involves tables where the record size > > is larger than the page size. When a session that's accessing a table > > causes the table to need to add an extent, that session goes into a > > critical section and stays there for quite some time. When the table is > > small, it's just for a few seconds, but as the table grows (to around > > 200 MB, for example) this time increases dramatically. And the problem > > becomes more acute when a checkpoint hits. Since the checkpoint can't > > clear until all sessions leave their critical sections, the checkpoint > > hangs up waiting on the session. In my testing, when my test table got > > to around 200 MB, we had an 87 second (!) checkpoint. > > > To my knowledge, IBM has not yet assigned a bug # to this issue, but we > > still have a PMR open. > > > Downgrading to UC5 eliminated this problem. I also suspect you could > > work around this by using configurable page size to put the table in > > pages where a whole record fits on a page, but I have not yet tested > > this approach. (It took several hours just to get to a point where we > > realized adding extents was the problem). On systems with 2K pages, > > tables whose row size is < 2,020 bytes seem to work just fine; it's only > > when the row size exceeds the page size (and thus get remainder pages) > > that we start to observe the problem. > > > If you're getting unexplained long checkpoints, this might be a place to > > start. > > > This also brings up a side point: I've got the 2,020 number burned into > > my head as "what fits on a 2K page" from at least the 7.x days, maybe > > even the 5.x days. Is that still true today? Also, what's the formula > > for figuring out what fits on a page for larger pages? Do you deduct a > > flat 28 bytes, or is it some multiple of the page size? > > > Thanks, > > > - TJG > > I believe it is still 28 bytes for larger page sizes. Also, you can > still only store 255 or 256 records on a page because the number of > slot entries have not changed. have should be has in the above. Also, is this really widespread or is it only with a certain platform? I was starting to evaluate FC6 but now I am worried because we have several tables that have more than 2020 max page size. We found that going to 16k helped performance on larger tables because you don't have the remainder pages. On our star schema historical reporting database where every table has large max page sizes we saved almost 40% because every record wasn't really close to the maximum but Informix would put each record on its own page if the max size was greater than 2020.

On Aug 28, 12:29 am, "Thomas J. Girsch" <tgir...@NOSPAM.gmail.com> wrote: <SNIP> Doesn't sound like fun! > This also brings up a side point: I've got the 2,020 number burned into > my head as "what fits on a 2K page" from at least the 7.x days, maybe > even the 5.x days. Is that still true today? Also, what's the formula > for figuring out what fits on a page for larger pages? Do you deduct a > flat 28 bytes, or is it some multiple of the page size? Almost, you subtract the size of the page header - 24bytes - the page trailer - 4 bytes - and the size of the one row's slot entry - 4 bytes - for a total of 32byte making the largest record on a 2K page actually 2016 bytes. The 2020 you're remembering is the amount of data space on a 2K page which you use to determine how many rows of a particular size (< pagesize) will fit on a page, ie: rows_per_page = 2020 / (rowsize + 4) This formula shows that exactly 1 row of 2016 bytes fits on a page with no free space left wasted. For the general case with adjustable pagesize you'd use: rows_per_page = (pagesize - 28) / (rowsize + 4) Art S. Kagel

On Aug 28, 11:16 am, bozon <cur...@crowson1.com> wrote: > On Aug 28, 10:37 am, bozon <cur...@crowson1.com> wrote: <SNIP> > We found that going to 16k helped performance on larger tables because > you don't have the remainder pages. On our star schema historical > reporting database where every table has large max page sizes we saved > almost 40% because every record wasn't really close to the maximum but > Informix would put each record on its own page if the max size was > greater than 2020. Actually, for variable sized rows, IDS will not place a row on a page if the remaining free space is less than the maximum size of a row. So if you have a row with a minimum size and maximum size such that the min plus the max is more than (pagesize - 36) only one row will be placed on a page. Art S. Kagel

On Aug 28, 12:26 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Aug 28, 11:16 am, bozon <cur...@crowson1.com> wrote: > > > On Aug 28, 10:37 am, bozon <cur...@crowson1.com> wrote: > <SNIP> > > We found that going to 16k helped performance on larger tables because > > you don't have the remainder pages. On our star schema historical > > reporting database where every table has large max page sizes we saved > > almost 40% because every record wasn't really close to the maximum but > > Informix would put each record on its own page if the max size was > > greater than 2020. > > Actually, for variable sized rows, IDS will not place a row on a page > if the remaining free space is less than the maximum size of a row. > So if you have a row with a minimum size and maximum size such that > the min plus the max is more than (pagesize - 36) only one row will be > placed on a page. > > Art S. Kagel Yep, which is what I was trying to say but didn't say it very well.

Easy reproduction: CREATE RAW TABLE bugtest ( col1 CHAR(2100) ) IN dbsp2kpg EXTENT SIZE 100000 NEXT SIZE 25000; INSERT INTO bugtest SELECT [char-column] FROM [some-table-with-lots-of-rows]; Do this enough times in IDS10.00.xC6 to get the table up around 200MB, and you'll start seeing the long checkpoints. Get it up around 1 GB, and you'll REALLY see it get ugly. On IDS10.00.UC5, everything works fine. Thomas J. Girsch wrote: > FYI, we've recently encountered a particularly nasty bug that seems to > first appear in IDS 10.00.UC6. It involves tables where the record size > is larger than the page size. When a session that's accessing a table > causes the table to need to add an extent, that session goes into a > critical section and stays there for quite some time. When the table is > small, it's just for a few seconds, but as the table grows (to around > 200 MB, for example) this time increases dramatically. And the problem > becomes more acute when a checkpoint hits. Since the checkpoint can't > clear until all sessions leave their critical sections, the checkpoint > hangs up waiting on the session. In my testing, when my test table got > to around 200 MB, we had an 87 second (!) checkpoint. > > To my knowledge, IBM has not yet assigned a bug # to this issue, but we > still have a PMR open. > > Downgrading to UC5 eliminated this problem. I also suspect you could > work around this by using configurable page size to put the table in > pages where a whole record fits on a page, but I have not yet tested > this approach. (It took several hours just to get to a point where we > realized adding extents was the problem). On systems with 2K pages, > tables whose row size is < 2,020 bytes seem to work just fine; it's only > when the row size exceeds the page size (and thus get remainder pages) > that we start to observe the problem. > > If you're getting unexplained long checkpoints, this might be a place to > start. > > This also brings up a side point: I've got the 2,020 number burned into > my head as "what fits on a 2K page" from at least the 7.x days, maybe > even the 5.x days. Is that still true today? Also, what's the formula > for figuring out what fits on a page for larger pages? Do you deduct a > flat 28 bytes, or is it some multiple of the page size? > > Thanks, > > - TJG

Do you have a PMR and bug number yet, and do you know if it's fixed in xC7? Thx Neil "Thomas J. Girsch" <tgirsch@NOSPAM.gmail.com> wrote in message news:ds6dnd8DptSC30vbnZ2dnUVZ_o3inZ2d@comcast.com... > Easy reproduction: > > CREATE RAW TABLE bugtest ( > col1 CHAR(2100) > ) IN dbsp2kpg EXTENT SIZE 100000 NEXT SIZE 25000;> > INSERT INTO bugtest > SELECT [char-column] > FROM [some-table-with-lots-of-rows];> > Do this enough times in IDS10.00.xC6 to get the table up around 200MB, and > you'll start seeing the long checkpoints. Get it up around 1 GB, and > you'll REALLY see it get ugly. > > On IDS10.00.UC5, everything works fine. > > Thomas J. Girsch wrote: >> FYI, we've recently encountered a particularly nasty bug that seems to >> first appear in IDS 10.00.UC6. It involves tables where the record size >> is larger than the page size. When a session that's accessing a table >> causes the table to need to add an extent, that session goes into a >> critical section and stays there for quite some time. When the table is >> small, it's just for a few seconds, but as the table grows (to around 200 >> MB, for example) this time increases dramatically. And the problem >> becomes more acute when a checkpoint hits. Since the checkpoint can't >> clear until all sessions leave their critical sections, the checkpoint >> hangs up waiting on the session. In my testing, when my test table got >> to around 200 MB, we had an 87 second (!) checkpoint. >> >> To my knowledge, IBM has not yet assigned a bug # to this issue, but we >> still have a PMR open. >> >> Downgrading to UC5 eliminated this problem. I also suspect you could >> work around this by using configurable page size to put the table in >> pages where a whole record fits on a page, but I have not yet tested this >> approach. (It took several hours just to get to a point where we >> realized adding extents was the problem). On systems with 2K pages, >> tables whose row size is < 2,020 bytes seem to work just fine; it's only >> when the row size exceeds the page size (and thus get remainder pages) >> that we start to observe the problem. >> >> If you're getting unexplained long checkpoints, this might be a place to >> start. >> >> This also brings up a side point: I've got the 2,020 number burned into >> my head as "what fits on a 2K page" from at least the 7.x days, maybe >> even the 5.x days. Is that still true today? Also, what's the formula >> for figuring out what fits on a page for larger pages? Do you deduct a >> flat 28 bytes, or is it some multiple of the page size? >> >> Thanks, >> >> - TJG

On Aug 29, 9:54 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: > Do you have a PMR and bug number yet, and do you know if it's fixed in xC7? > > Thx > Neil > > "Thomas J. Girsch" <tgir...@NOSPAM.gmail.com> wrote in messagenews:ds6dnd8DptSC30vbnZ2dnUVZ_o3inZ2d@comcast.com... > > > Easy reproduction: > > > CREATE RAW TABLE bugtest ( > > col1 CHAR(2100) > > ) IN dbsp2kpg EXTENT SIZE 100000 NEXT SIZE 25000;> > > INSERT INTO bugtest > > SELECT [char-column] > > FROM [some-table-with-lots-of-rows];> > > Do this enough times in IDS10.00.xC6 to get the table up around 200MB, and > > you'll start seeing the long checkpoints. Get it up around 1 GB, and > > you'll REALLY see it get ugly. > > > On IDS10.00.UC5, everything works fine. > > > Thomas J. Girsch wrote: > >> FYI, we've recently encountered a particularly nasty bug that seems to > >> first appear in IDS 10.00.UC6. It involves tables where the record size > >> is larger than the page size. When a session that's accessing a table > >> causes the table to need to add an extent, that session goes into a > >> critical section and stays there for quite some time. When the table is > >> small, it's just for a few seconds, but as the table grows (to around 200 > >> MB, for example) this time increases dramatically. And the problem > >> becomes more acute when a checkpoint hits. Since the checkpoint can't > >> clear until all sessions leave their critical sections, the checkpoint > >> hangs up waiting on the session. In my testing, when my test table got > >> to around 200 MB, we had an 87 second (!) checkpoint. > > >> To my knowledge, IBM has not yet assigned a bug # to this issue, but we > >> still have a PMR open. > > >> Downgrading to UC5 eliminated this problem. I also suspect you could > >> work around this by using configurable page size to put the table in > >> pages where a whole record fits on a page, but I have not yet tested this > >> approach. (It took several hours just to get to a point where we > >> realized adding extents was the problem). On systems with 2K pages, > >> tables whose row size is < 2,020 bytes seem to work just fine; it's only > >> when the row size exceeds the page size (and thus get remainder pages) > >> that we start to observe the problem. > > >> If you're getting unexplained long checkpoints, this might be a place to > >> start. > > >> This also brings up a side point: I've got the 2,020 number burned into > >> my head as "what fits on a 2K page" from at least the 7.x days, maybe > >> even the 5.x days. Is that still true today? Also, what's the formula > >> for figuring out what fits on a page for larger pages? Do you deduct a > >> flat 28 bytes, or is it some multiple of the page size? > or the general case with adjustable pagesize you'd use: rows_per_page = min((pagesize - 28) / (rowsize + 4),255) Art S. Kagel > >> Thanks, > > >> - TJG Let me add a small bit to Mr. Kagel's comment...don't forget that the 255 rows limit on DATA pages is still in place, regardless of page size. Not true (has never been) for index pages, but data pages, yes. This is due to the ROWID format of 0xLLLLLLSS, where SS = slot/row number on the page. Range for 1 byte (SS) is 0-255. One would think that it's 256 then, but we take the first slot, slot 0, for the page trailing timestamp. Also - all of the above is premised on the row fitting onto the page. IF a row has to be split across pages, you need to add a 4-byte forward pointer to each row that is split, 1 per remainder page. HTH - Mark Scranton rows_per_page = min((pagesize - 28) / (rowsize + 4),255)

On Aug 30, 10:57 am, "mark.scran...@gmail.com" <mark.scran...@gmail.com> wrote: > On Aug 29, 9:54 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: > > > > > Do you have a PMR and bug number yet, and do you know if it's fixed in xC7? > > > Thx > > Neil > > > "Thomas J. Girsch" <tgir...@NOSPAM.gmail.com> wrote in messagenews:ds6dnd8DptSC30vbnZ2dnUVZ_o3inZ2d@comcast.com... > > > > Easy reproduction: > > > > CREATE RAW TABLE bugtest ( > > > col1 CHAR(2100) > > > ) IN dbsp2kpg EXTENT SIZE 100000 NEXT SIZE 25000;> > > > INSERT INTO bugtest > > > SELECT [char-column] > > > FROM [some-table-with-lots-of-rows];> > > > Do this enough times in IDS10.00.xC6 to get the table up around 200MB, and > > > you'll start seeing the long checkpoints. Get it up around 1 GB, and > > > you'll REALLY see it get ugly. > > > > On IDS10.00.UC5, everything works fine. > > > > Thomas J. Girsch wrote: > > >> FYI, we've recently encountered a particularly nasty bug that seems to > > >> first appear in IDS 10.00.UC6. It involves tables where the record size > > >> is larger than the page size. When a session that's accessing a table > > >> causes the table to need to add an extent, that session goes into a > > >> critical section and stays there for quite some time. When the table is > > >> small, it's just for a few seconds, but as the table grows (to around 200 > > >> MB, for example) this time increases dramatically. And the problem > > >> becomes more acute when a checkpoint hits. Since the checkpoint can't > > >> clear until all sessions leave their critical sections, the checkpoint > > >> hangs up waiting on the session. In my testing, when my test table got > > >> to around 200 MB, we had an 87 second (!) checkpoint. > > > >> To my knowledge, IBM has not yet assigned a bug # to this issue, but we > > >> still have a PMR open. > > > >> Downgrading to UC5 eliminated this problem. I also suspect you could > > >> work around this by using configurable page size to put the table in > > >> pages where a whole record fits on a page, but I have not yet tested this > > >> approach. (It took several hours just to get to a point where we > > >> realized adding extents was the problem). On systems with 2K pages, > > >> tables whose row size is < 2,020 bytes seem to work just fine; it's only > > >> when the row size exceeds the page size (and thus get remainder pages) > > >> that we start to observe the problem. > > > >> If you're getting unexplained long checkpoints, this might be a place to > > >> start. > > > >> This also brings up a side point: I've got the 2,020 number burned into > > >> my head as "what fits on a 2K page" from at least the 7.x days, maybe > > >> even the 5.x days. Is that still true today? Also, what's the formula > > >> for figuring out what fits on a page for larger pages? Do you deduct a > > >> flat 28 bytes, or is it some multiple of the page size? > > or the general case with adjustable pagesize you'd use: > > rows_per_page = min((pagesize - 28) / (rowsize + 4),255) > > Art S. Kagel > > > >> Thanks, > > > >> - TJG > > Let me add a small bit to Mr. Kagel's comment...don't forget that the > 255 rows limit on DATA pages is still in place, regardless of page > size. Not true (has never been) for index pages, but data pages, yes. > This is due to the ROWID format of 0xLLLLLLSS, where SS = slot/row > number on the page. Range for 1 byte (SS) is 0-255. One would think > that it's 256 then, but we take the first slot, slot 0, for the page > trailing timestamp. > > Also - all of the above is premised on the row fitting onto the page. > IF a row has to be split across pages, you need to add a 4-byte > forward pointer to each row that is split, 1 per remainder page. > > HTH - > Mark Scranton > > rows_per_page = min((pagesize - 28) / (rowsize + 4),255) Just an aside to put Mark's worthy and correct update in perspective before anyone starts to complain that IBM needs to fix this little bit of apparent sillyness: 255 rows on a 2K page means each row must be <= 3bytes (+4 for the slot entry - 7 * 255 = 1785 8*255 = 2040) 4K <= 11bytes 8K <= 28bytes 16K <= 60 bytes This shows that the 255 slot limit isn't an impractical one (how practical would the ability to have 404 one bytes rows be anyway - especially given that the one byte can only hold 256 unique values?). 8^} Art S. Kagel

On 30 Aug, 05:12, "Thomas J. Girsch" <tgir...@NOSPAM.gmail.com> wrote: > Easy reproduction: > > CREATE RAW TABLE bugtest ( > col1 CHAR(2100) > ) IN dbsp2kpg EXTENT SIZE 100000 NEXT SIZE 25000;> > INSERT INTO bugtest > SELECT [char-column] > FROM [some-table-with-lots-of-rows];> > Do this enough times in IDS10.00.xC6 to get the table up around 200MB, > and you'll start seeing the long checkpoints. Get it up around 1 GB, > and you'll REALLY see it get ugly. > > On IDS10.00.UC5, everything works fine. > > > > Thomas J. Girsch wrote: > > FYI, we've recently encountered a particularly nasty bug that seems to > > first appear in IDS 10.00.UC6. It involves tables where the record size > > is larger than the page size. When a session that's accessing a table > > causes the table to need to add an extent, that session goes into a > > critical section and stays there for quite some time. When the table is > > small, it's just for a few seconds, but as the table grows (to around > > 200 MB, for example) this time increases dramatically. And the problem > > becomes more acute when a checkpoint hits. Since the checkpoint can't > > clear until all sessions leave their critical sections, the checkpoint > > hangs up waiting on the session. In my testing, when my test table got > > to around 200 MB, we had an 87 second (!) checkpoint. > > > To my knowledge, IBM has not yet assigned a bug # to this issue, but we > > still have a PMR open. > > > Downgrading to UC5 eliminated this problem. I also suspect you could > > work around this by using configurable page size to put the table in > > pages where a whole record fits on a page, but I have not yet tested > > this approach. (It took several hours just to get to a point where we > > realized adding extents was the problem). On systems with 2K pages, > > tables whose row size is < 2,020 bytes seem to work just fine; it's only > > when the row size exceeds the page size (and thus get remainder pages) > > that we start to observe the problem. > > > If you're getting unexplained long checkpoints, this might be a place to > > start. > > > This also brings up a side point: I've got the 2,020 number burned into > > my head as "what fits on a 2K page" from at least the 7.x days, maybe > > even the 5.x days. Is that still true today? Also, what's the formula > > for figuring out what fits on a page for larger pages? Do you deduct a > > flat 28 bytes, or is it some multiple of the page size? > > > Thanks, > > > - TJG- Hide quoted text - > > - Show quoted text - As per http://www-1.ibm.com/support/docview.wss?uid=swg21250366 what does setting export TRACECKPT=1 before starting the engine give? What about onstat -g stk for the active threads at the time?

On Aug 30, 4:57 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Aug 30, 10:57 am, "mark.scran...@gmail.com" > > > > <mark.scran...@gmail.com> wrote: > > On Aug 29, 9:54 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: > > > > Do you have a PMR and bug number yet, and do you know if it's fixed in xC7? > > > > Thx > > > Neil > > > > "Thomas J. Girsch" <tgir...@NOSPAM.gmail.com> wrote in messagenews:ds6dnd8DptSC30vbnZ2dnUVZ_o3inZ2d@comcast.com... > > > > > Easy reproduction: > > > > > CREATE RAW TABLE bugtest ( > > > > col1 CHAR(2100) > > > > ) IN dbsp2kpg EXTENT SIZE 100000 NEXT SIZE 25000;> > > > > INSERT INTO bugtest > > > > SELECT [char-column] > > > > FROM [some-table-with-lots-of-rows];> > > > > Do this enough times in IDS10.00.xC6 to get the table up around 200MB, and > > > > you'll start seeing the long checkpoints. Get it up around 1 GB, and > > > > you'll REALLY see it get ugly. > > > > > On IDS10.00.UC5, everything works fine. > > > > > Thomas J. Girsch wrote: > > > >> FYI, we've recently encountered a particularly nasty bug that seems to > > > >> first appear in IDS 10.00.UC6. It involves tables where the record size > > > >> is larger than the page size. When a session that's accessing a table > > > >> causes the table to need to add an extent, that session goes into a > > > >> critical section and stays there for quite some time. When the table is > > > >> small, it's just for a few seconds, but as the table grows (to around 200 > > > >> MB, for example) this time increases dramatically. And the problem > > > >> becomes more acute when a checkpoint hits. Since the checkpoint can't > > > >> clear until all sessions leave their critical sections, the checkpoint > > > >> hangs up waiting on the session. In my testing, when my test table got > > > >> to around 200 MB, we had an 87 second (!) checkpoint. > > > > >> To my knowledge, IBM has not yet assigned a bug # to this issue, but we > > > >> still have a PMR open. > > > > >> Downgrading to UC5 eliminated this problem. I also suspect you could > > > >> work around this by using configurable page size to put the table in > > > >> pages where a whole record fits on a page, but I have not yet tested this > > > >> approach. (It took several hours just to get to a point where we > > > >> realized adding extents was the problem). On systems with 2K pages, > > > >> tables whose row size is < 2,020 bytes seem to work just fine; it's only > > > >> when the row size exceeds the page size (and thus get remainder pages) > > > >> that we start to observe the problem. > > > > >> If you're getting unexplained long checkpoints, this might be a place to > > > >> start. > > > > >> This also brings up a side point: I've got the 2,020 number burned into > > > >> my head as "what fits on a 2K page" from at least the 7.x days, maybe > > > >> even the 5.x days. Is that still true today? Also, what's the formula > > > >> for figuring out what fits on a page for larger pages? Do you deduct a > > > >> flat 28 bytes, or is it some multiple of the page size? > > > or the general case with adjustable pagesize you'd use: > > > rows_per_page = min((pagesize - 28) / (rowsize + 4),255) > > > Art S. Kagel > > > > >> Thanks, > > > > >> - TJG > > > Let me add a small bit to Mr. Kagel's comment...don't forget that the > > 255 rows limit on DATA pages is still in place, regardless of page > > size. Not true (has never been) for index pages, but data pages, yes. > > This is due to the ROWID format of 0xLLLLLLSS, where SS = slot/row > > number on the page. Range for 1 byte (SS) is 0-255. One would think > > that it's 256 then, but we take the first slot, slot 0, for the page > > trailing timestamp. > > > Also - all of the above is premised on the row fitting onto the page. > > IF a row has to be split across pages, you need to add a 4-byte > > forward pointer to each row that is split, 1 per remainder page. > > > HTH - > > Mark Scranton > > > rows_per_page = min((pagesize - 28) / (rowsize + 4),255) > > Just an aside to put Mark's worthy and correct update in perspective > before anyone starts to complain that IBM needs to fix this little bit > of apparent sillyness: > > 255 rows on a 2K page means each row must be <= 3bytes (+4 for the > slot entry - 7 * 255 = 1785 8*255 = 2040) > 4K > <= 11bytes > 8K > <= 28bytes > 16K > <= 60 bytes > > This shows that the 255 slot limit isn't an impractical one (how > practical would the ability to have 404 one bytes rows be anyway - > especially given that the one byte can only hold 256 unique > values?). 8^} > > Art S. Kagel Of course Art is correct here that it isn't really that much of a limitation. It only becomes a consideration for the 16K page data spaces and tables that are very narrow. You just know that you will be wasting space if you put a narrow table in a 16K page. I am thinking about multiway join tables that usually only contain 2 or 3 IDs. So you don't put them there if you don't want to waste the space.