SEO

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@comca st.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@comca st.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