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