IDS Forum

Re: Indexes contain multiple extents after reorg

Posted By: Art Kagel
Date: Thursday, 8 November 2012, at 9:27 p.m.

In Response To: Re: Indexes contain multiple extents after reorg (MARK COLLINS)

Mark:

Actually, I have to apologize, when you calculate the ratio of rowsize to
keysize, you have to add a few piddling items onto the key (forgot that,
getting old you know). You have to add 4 bytes for the row pointer (8 if
the table is partitioned) plus another byte (or two I don't remember --
Fernando?) for the deleted key flag. That brings your key size up from 20
bytes to 25/26 or 33/34 depending on whether the table is partitioned or
not. Assuming 26 that's 11.87% (34 bytes is 15.53%) not far off of the
12.667% you calculated.

Anyway, the contiguous blocks added to the initial 28000+ page extent would
be accidents that happened when later extents (say the 20th?) had run out
of 16K free space blocks are started using bits of the larger block
(probably that 300000 page free block you mentioned) that was used to
allocate the first extent but prior to that all of the other smaller
extents were allocated from the dregs left over from allocating extents for
other tables and indexes from block just a wee bit bigger than needed.
That's why you got fragmented.

(As an aside for bystanders, this extent compression behavior causing new
pages to be inserted before other existing pages is one reason why you
cannot depend on the order of rows returned by the engine to match the
order of insertion unless you can use an ORDER BY clause on an insertion
timestamp or SERIAL/SERIAL8/BIGSERIAL/sequence column to force the
ordering.)

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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, Nov 8, 2012 at 9:02 PM, MARK COLLINS <markc@myfastmail.com> wrote:

> Art,
>
> Thanks for the information. I hadn't calculated the percent of keylength
> vs.
> row size. I follow the math that you provided, it makes sense. I just can't
> see why the index ends up with so many extents based on that math.
>
> It may very well be that the engine assigns 28,263 pages for the first
> extent,
> and then adds some additional extents and compresses them due to their
> contiguous pages, resulting in a first extent that ends up at 35,843. And
> then, assuming that there were no additional free pages adjacent when it
> needed to extend the index yet again, it would then base the index's
> secondary
> extents on the table's NEXT SIZE.
>
> The part that doesn't make sense to me yet is, why does the index end up
> requiring almost 38,000 pages when it is less than 10% of the row size? The
> table is less than 300,000 used pages (312,500 allocated through EXTENT
> SIZE
> 625000), so I would expect the index to be around 30,000 pages (I rounded
> up,
> I know you came up with 28,263).
>
> I have a spreadsheet where I attempt to calculate such things, and I end up
> with 1 root page, 8 level-1 non-leaf pages, 520 level-2 non-leaf pages, and
> 37,370 leaf pages, for a total of 37,899 pages. This compares very
> favorably
> to the oncheck -pT output, which shows 36 free pages, 10 bit-map pages (my
> spreadsheet doesn't attempt to calculate these, but it does include slot
> table
> entries and rowids when calculating 72 keys/2k page), and 37,877 index
> pages,
> for a total of 37,923. If my spreadsheet can get that close, why doesn't
> the
> engine? And why is the total number of pages so much larger than one would
> expect based on the 9.13% ratio of key length / rowsize? The index actually
> ended up consuming 12.667% as many pages as the table, and this is
> immediately
> after being dropped and rebuilt.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--14dae93406d7bc51f304ce06b1ad

Messages In This Thread

• Indexes contain multiple extents after reorg
  MARK COLLINS -- Thursday, 8 November 2012, at 7:08 p.m.
  • Re: Indexes contain multiple extents after reorg
    Fernando Nunes -- Thursday, 8 November 2012, at 7:31 p.m.
    • Re: Indexes contain multiple extents after reorg
      MARK COLLINS -- Thursday, 8 November 2012, at 8:40 p.m.
  • Re: Indexes contain multiple extents after reorg
    Art Kagel -- Thursday, 8 November 2012, at 8:38 p.m.
    • Re: Indexes contain multiple extents after reorg
      MARK COLLINS -- Thursday, 8 November 2012, at 9:02 p.m.
      • Re: Indexes contain multiple extents after reorg
        Art Kagel -- Thursday, 8 November 2012, at 9:27 p.m.
        • Re: Indexes contain multiple extents after reorg
          MARK COLLINS -- Thursday, 8 November 2012, at 11:46 p.m.
          • Re: Indexes contain multiple extents after reorg
            Fernando Nunes -- Friday, 9 November 2012, at 3:15 a.m.
            • Re: Indexes contain multiple extents after reorg
              MARK COLLINS -- Friday, 9 November 2012, at 10:02 a.m.
              • Re: Indexes contain multiple extents after reorg
                Fernando Nunes -- Friday, 9 November 2012, at 10:54 a.m.
                • Re: Indexes contain multiple extents after reorg
                  MARK COLLINS -- Friday, 9 November 2012, at 11:29 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.