Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: MDC calculations

    One of the most complicated tasks for me in db2/luw is the calculation of the "RIGHT" pagesize/extentsize combination for a MDC table.
    In this case/casus:

    a table with an avg row size of 782 and 429,480 rows.
    I want to organize on 3 columns and when I predict the population of the cells I get:
    Code:
    CNT         OCCURS
    ----------- -----------
              1         278
              2          58
              3          31
              4          18
              5          64
              6          17
              7           8
              8          11
              9           6
             10           9
             11           1
             12           3
             13           2
             14           3
             15           5
             16           1
             17           1
             18           2
             19           2
             20           2
             22           1
             23           2
             24           1
             26           1
             28           4
             29           1
             30           3
             31           1
             34           4
             35           1
             36           1
             37           1
             39           3
             41           1
             43           1
             44           1
             45           2
             47           1
             48           1
             50           2
             56           1
             58           1
             59           3
             60           1
             62           2
             63           1
             65           1
             66           1
             68           1
             69           1
             70           1
             72           1
             75           1
             76           1
             78           1
             79           2
             84           1
             85           1
             90           3
             92           1
             93           1
             98           1
            106           1
            108           1
            109           1
            123           1
            125           1
            128           1
            138           1
            144           2
            153           1
            158           1
            162           1
            166           2
            177           1
            182           1
            184           1
            187           1
            188           1
            189           1
            197           1
            203           1
            206           1
            222           1
            225           1
            231           1
            233           1
            242           1
            247           1
            253           1
            267           1
            285           1
            286           1
            288           1
            293           1
            294           1
            295           1
            316           1
            338           1
            348           1
            357           1
            376           1
            399           1
            434           1
            471           1
            474           1
            508           1
            552           1
            560           1
            564           1
            595           1
            610           1
            636           1
            656           1
            706           1
            772           1
            781           1
            803           1
            820           2
            825           2
            834           1
            887           1
            958           1
            964           1
           1004           1
           1039           1
           1069           1
           1134           1
           1146           1
           1172           1
           1238           1
           1345           1
           1365           1
           1456           1
           1474           1
           1550           1
           1551           1
           1608           1
           1609           1
           1672           1
           1746           1
           1824           1
           1845           1
           1850           1
           2026           1
           2184           1
           2296           1
           2302           1
           2852           1
           2900           1
           3073           1
           3275           1
           3451           1
           4123           1
           4457           1
           4669           1
           5465           1
           5578           1
           7529           1
           8283           1
           8480           1
          10210           1
          10235           1
          11197           1
          13050           1
          15661           1
          17953           1
          19778           1
          20027           1
          20719           1
          20822           1
          67067           1
          70811           1
    
      173 record(s) selected.
    (I will get 278 cells with only 1 row, 58 cells with only 2 rows ............... 1 cell with 67,067 rows, 1 cell with 70,811 rows)

    based upon a pagesize of 8k and a extentsize of 16 I calculate:
    - blocksize 131072
    - rows per page 10
    - rows per block 160

    That gives my material to calculate the space-overhead when using MDC. Just reduce the page- and extentsize and the overhead will reduce as well right?
    Fine, but that is only 1 side of the equation. On the other hand I want the optimal I/O performance. How do I calculate that?

    Anyone tips/trics/smart spreadsheets?

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Nobody? Back to good old trail&error. So I populated my tables a few times with different tablespace parameters and after each time I consulted db2mon to look at the "Used(MB)" column of the tablespace. Here are the results:
    Code:
    page extent     size Mb
    4	2	376,242
    4	4	377,844
    4	8	385,656
    8	2	364,406
    8	4	372,813
    8	8	391,750
    16	2	363,875
    16	4	382,625
    16	8	421,875
    32	2	377,813
    32	4	417,125
    32	8	497,500
    and now sorted by size
    Code:
    page extent     size Mb
    16	2	363,875
    8	2	364,406
    8	4	372,813
    4	2	376,242
    32	2	377,813
    4	4	377,844
    16	4	382,625
    4	8	385,656
    8	8	391,750
    32	4	417,125
    16	8	421,875
    32	8	497,500
    A pagesize of 16 is the cheapest! Who would have thought.
    The obvious conslusion is that in this case the influence of the few sparcely populated cells can be neglected.

    Please not that I use the dutch notation for the decimal point (being comma ) So the sizes vary betweeen 363 and 498 Mb
    Last edited by dr_te_z; 02-23-12 at 05:20. Reason: decimal point remark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •