Extent size and data forecast
AMIT PATEL asked for a formula to size INITIAL and NEXT extents and forecast dbspace fill for a 758-byte-row table growing 100000 rows a month. David Williams and Paul Watson advised loading sample rows and checking with oncheck. Art Kagel gave page-count formulas for fixed-length rows and indexes. Jon Ritson warned that growth is rarely linear.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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 ------------------------------ #Informix
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 ------------------------------
Why not just create the table, load it with 100K rows of real data and run oncheck ?
Cheers
Paul
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 ------------------------------
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 ------------------------------