Informix

  • 1.  Extent size and data forecast

    Posted Thu August 12, 2021 06:59 AM
    Hello everyone,

    Please tell me the simple formula for calculating the INITIALI and NEXT extent size for below criteria.

    TABLE "informix".cs_cust row size = 758 number of columns = 50 index size = 331

    If customer says table growth rate 100000 rows per month.

    We have 2 GB chunk size and if dbspace has 4 chunks then How to forecsast for filling the dbspace?

    Like with 100000 rows per months in what time dbspace can be filled ?

    Thanks
    Amit 



    ------------------------------
    AMIT PATEL
    ------------------------------


  • 2.  RE: Extent size and data forecast

    Posted Thu August 12, 2021 08:02 AM



    Also depends on how many variable length columns you have and the size of each column for each row.

    Rather than trying to work out a complex formula just create a test table and insert 100,000 sample rows...

    Regards,

    David.



    ------------------------------
    David Williams
    ------------------------------



  • 3.  RE: Extent size and data forecast

    Posted Thu August 12, 2021 08:25 AM

    Why not just create the table, load it with 100K rows of real data and run oncheck ?

     

    Cheers

    Paul

     






  • 4.  RE: Extent size and data forecast

    Posted Thu August 12, 2021 09:37 AM
    Amit:

    I like David's and Paul's responses, but, on the off chance that the table is fixed length, here's how to determine how many pages a give number of rows will require (We'll deal with the indexes later):

    pagesz = <bytes of a page in the given dbspace>
    rowsz = 758 bytes

    rows_per_page = floor( (pagesz - 24) / (rowsz + 4) )
    num_pages = num_rows / rows_per_page
    num_pages = num_pages + (num_pages / (pagsz * 2))    # Add in bitmap pages

    ​For variable length rows, use the average row size of actual or expected data instead of the engine's maximum row  size or make two calculations, one using the max row size and one using the estimated minimum row size to guide expectations.

    Index sizing: This is tricky:

    pagesz = <# bytes in the dbspace to hold the index>
    keysz = 1.15 * (bytes per index key + 8) (add up the maximum lengths of all columns in the key)

    Use keysz in the num_pages formula above instead of rowsz. 

    num_pages = ((num_pages * 100) / FILLFACTOR)

    This mainly works for indexes built on a filled table. For an index that grows over time, instead of the configured FILLFACTOR use 50 if the keys are normally distributed and 95 if the key grows monotonically (like an index on a SERIAL column). You will have to do each index separately.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 5.  RE: Extent size and data forecast

    Posted Fri August 13, 2021 11:51 AM

    I like David's, Paul's AND Art's answers ... however, data growth is rarely "linear" ... be aware as sizing for linear growth as opposed to compound will catch you out fairly quickly.

    Linear starting at 1,000,000 then 12 months growth at 100,000 per month => 2,200,000

    Compound starting at 1,000,000 10% growth per month for 12 months =>  3,138,428

    "That Friday feeling"



    ------------------------------
    Jon Ritson
    ------------------------------