Extent Size suggestion in Server Studio

Posted in 2011

sdhaya — Thu, 14 Apr 2011 07:27:44 -0700 (PDT) — source: Usenet: comp.databases.informix

Hi, Last week we had a issue with a table and new records where not getting inserted. I was verifying the schema and found the table contain around 5M rows with 5 index. ( Each index with 5 colums ). Currently the extent size, next extent size was 16K respectively. I need to increase the extent size/next size and unable to come up with a correct values. I used Server Studio to compute/suggest Extent/ Next Extent size for me. Below are the results. **************************************************** Server Studio Table Extent Size Report Apr 14, 2011 ***************************************************** Table: shclog Recommended first extent size: 7 GB - 7368960 KB (current value:16 KB) Recommended next extent size: 1 GB - 1263250 KB (current value:16 KB) Total estimated table size: 3684480 page(s) (7 GB ) Data pages: 3095764 page(s) (5 GB ) Index pages: 588260 page(s) (1 GB ) Index shclog_1: 135098 pages (263 MB ) Index shclog_2: 43481 pages (84 MB ) Index shclog_4: 143036 pages (279 MB ) Index shclog_5: 131547 pages (256 MB ) Index shclog_6: 135098 pages (263 MB ) Bitmap pages: 456 page(s) (912 KB ) Our database is serving an OLTP app ( ATM Switch ). My question is can I go with the recommended extent sizes. When allocating the above extent size, with there be impact on disk space, locks, buffers or any other parameters. Also the suggestion given via SS are for the current record count in table or does it buffer the values to suggest the sizes. Our current record count is 3M and we are planning to double the count by increase our retention period. Please provide your valuable inputs. I have more tables in DB with same extent sizes. Thanks Dhaya

↪ replying to sdhaya

Art Kagel — Thu, 14 Apr 2011 10:38:36 -0400 — source: Usenet: comp.databases.informix

Here are the rules of thumb for extent sizing: - Size your first and next extents so that at most two extents contain actively accessed rows. - Size your extents less than or equal to the chunks in the dbspace (the engine won't allocate any extents larger than a chunk anyway). - If you are not using fragmentation to segregate retention periods, size your extents so that performing historical data cleanup empties an entire extent for reuse by new data. So, if you retain 3 years of data and delete the 4th oldest year at the beginning of the year make an extent hold a full year of data. If you clean up month by month then an extent should hold a full month's data. Otherwise, without considering these rules, SS's recommendations are fine. 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, Apr 14, 2011 at 10:27 AM, sdhaya <dhaya.snidhi@gmail.com> wrote: > Hi, > > Last week we had a issue with a table and new records where not > getting inserted. I was verifying the schema and found the table > contain around 5M rows with 5 index. ( Each index with 5 colums ). > Currently the extent size, next extent size was 16K respectively. > > I need to increase the extent size/next size and unable to come up > with a correct values. I used Server Studio to compute/suggest Extent/ > Next Extent size for me. Below are the results. > > **************************************************** > Server Studio Table Extent Size Report Apr 14, 2011 > ***************************************************** > > Table: shclog > Recommended first extent size: 7 GB - 7368960 KB (current value:16 KB) > Recommended next extent size: 1 GB - 1263250 KB (current value:16 KB) > Total estimated table size: 3684480 page(s) (7 GB ) > Data pages: 3095764 page(s) (5 GB ) > Index pages: 588260 page(s) (1 GB ) > Index shclog_1: 135098 pages (263 MB ) > Index shclog_2: 43481 pages (84 MB ) > Index shclog_4: 143036 pages (279 MB ) > Index shclog_5: 131547 pages (256 MB ) > Index shclog_6: 135098 pages (263 MB ) > Bitmap pages: 456 page(s) (912 KB ) > > Our database is serving an OLTP app ( ATM Switch ). My question is can > I go with the recommended extent sizes. When allocating the above > extent size, with there be impact on disk space, locks, buffers or > any other parameters. Also the suggestion given via SS are for the > current record count in table or does it buffer the values to suggest > the sizes. > > Our current record count is 3M and we are planning to double the count > by increase our retention period. > > Please provide your valuable inputs. I have more tables in DB with > same extent sizes. > > Thanks > > Dhaya > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >