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...id=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@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

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.

>>> 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

I've been told that this is indeed fixed in xC7; the fix made it just
ahead of the code freeze. I'll try to get a bug number.