myschema - extent size looks very large

Newbie alert!! I ran myschema -d <db> -a and found the extent sizes to look very large. I also used 'extentreport' from Matthew Devlin and the report for this table looks like this: ----------------------------------------------------------------------------- First Next Total Used Num Num Table Extent Extent Size Kb Extents Rows ----------------------------------------------------------------------------- inv_shp_l 16 8192 223008 217874 155 563222 Thanks Gary Quiring Output from myschema: REVOKE ALL ON "informix".quot_l FROM public;CREATE TABLE "informix".inv_shp_l ( inv_shp_l_id SERIAL(-2147110047) {Curr: 1} NOT NULL, <snip> item_w_m_id INTEGER ) IN data2dbs EXTENT SIZE 217874 NEXT SIZE 16 LOCK MODE ROW; { Current first extend (16) too small! Actual pages used by table has been used. Tablespace header reports - Total: pgsused=108937, pgsdata=93871. Please review extent sizing and adjust to allow for growth. }

Gary, Looking at this output, you should probably unload this table to a file and then drop the table and recreate it with a proper extent size. Then reload the table and of course recreate all indexes, constraints, etc. You can do a dbschema -d <dbname> -t <tabname> to get the SQL to recreate the table along with any indexes and constraints. Also do a oncheck -pT <database>:<table> to get the number of pages allocated for this particular table. Multiply that number by two (or four depending on your OS) to get KB and this should be at least your first Extent size, with Next Extent being around 10% of that number. So when you reload your table, all the information will be in one contiguous extent rather than dispersed over 155 extents which is way way too many for optimal conditions. This is assuming that this table isn't fragmented over multiple dbspaces and/or over multiple drives. A couple of things to remember to do if you're going to follow this procedure: 1. do backup first 2. don't recreate indexes or constraints until after you've reloaded the data. It'll make the reload process go by much faster. 3. turn off logging to maximize load performance. You could also modify some ONCONFIG parameters as well to maximize load performance (bump up LRU_MAX/LRU_MIN, CKPTINTVL). You'll have to bounce the instance in order to make these changes effective. 4. When you recreate the table, be sure to specify which dbspace to create it in since the output of dbschema doesn't necessarily reflect the current dbspace where the table resides. Otherwise the table might be recreated in rootdbs! 5. update statistics after you're done reloading the data I recommend drafting a game plan first and creating scripts to do each step. For example, redirect the dbschema command to a file and then modify the file to edit the indexes and to specify which dbspace to recreate the table in. Also you can specify the extent sizes in this file as well using the output of the oncheck command to compute your extents. Then create another file with the index recreation lines in it for that step, and so forth. Good luck. Gary Quiring wrote: > Newbie alert!! I ran myschema -d <db> -a and found the extent sizes to > look very large. I also used 'extentreport' from Matthew Devlin and the > report for this table looks like this: > > ----------------------------------------------------------------------------- > First Next Total Used Num Num > Table Extent Extent Size Kb Extents Rows > ----------------------------------------------------------------------------- > inv_shp_l 16 8192 223008 217874 155 563222 > > Thanks > Gary Quiring > > Output from myschema: > REVOKE ALL ON "informix".quot_l FROM public;> CREATE TABLE "informix".inv_shp_l ( > inv_shp_l_id SERIAL(-2147110047) {Curr: 1} NOT NULL, > <snip> > item_w_m_id INTEGER > ) IN data2dbs EXTENT SIZE 217874 NEXT SIZE 16 LOCK MODE ROW; > > { > Current first extend (16) too small! > Actual pages used by table has been used. > Tablespace header reports - > Total: pgsused=108937, pgsdata=93871. > Please review extent sizing and adjust to allow for growth. > } -- Phillip Tien Database Administrator Whole Foods Market, Inc. "I always wanted to be the last guy on Earth just to see if all those women were lying to me."

In article <3974A3D4.79D6C0D7@wholefoods.com>, tienp@wholefoods.com writes >Gary, > >Looking at this output, you should probably unload this table to a file and then >drop the table and recreate it with a proper extent size. Then reload the table >and of course recreate all indexes, constraints, etc. > Correct. > >A couple of things to remember to do if you're going to follow this procedure: > >1. do backup first True. >2. don't recreate indexes or constraints until after you've reloaded the data. >It'll make the reload process go by much faster. Automatically handled by alter fragment, see below. >3. turn off logging to maximize load performance. You could also modify some >ONCONFIG parameters as well to maximize load performance (bump up >LRU_MAX/LRU_MIN, CKPTINTVL). You'll have to bounce the instance in order to >make >these changes effective. Turning off logging makes the biggest improvement. >4. When you recreate the table, be sure to specify which dbspace to create it >in >since the output of dbschema doesn't necessarily reflect the current dbspace >where the table resides. Otherwise the table might be recreated in rootdbs! > Handled by alter fragment. >5. update statistics after you're done reloading the data > True. goto www.iiug.org look under the software section for util2_ak and get that. Extract and use the dostats program from this package. >I recommend drafting a game plan first and creating scripts to do each step. 1. Turn logging off. 2. Set the following environment variables. PDQPRIORITY=100 PSORT_NPROCS= number of physical CPUs in the machine (x2 if on Solaris) PSORT_DBTEMP = a list of directories. /<something>/1:/<somewhere>/2:/<somewhere>/3.. Putting these all on the same disk seems to work ok for me even with the contention on disk. (I am using a Sun E450 server) Create the directories first!! 2. ALTER TABLE inv_shp_l NEXT SIZE 220000 This takes only 1 second! 3. ALTER FRAGMENT ON TABLE inv_shp_l INIT IN data2dbs This move the table + rebuilding the index and keeps contraints, views on the tables etc. 4, ALTER TABLE inv_shp_1 NEXT SIZE 60000 60000 seems a reasonable size since you do not want the table to a) get many extents b) allocate too large a next extent and cause space problems. >For >example, redirect the dbschema command to a file and then modify the file to >edit >the indexes and to specify which dbspace to recreate the table in. Also you can >specify the extent sizes in this file as well using the output of the oncheck >command to compute your extents. Then create another file with the index >recreation lines in it for that step, and so forth. Good luck. > >Gary Quiring wrote: > >> Newbie alert!! I ran myschema -d <db> -a and found the extent sizes to >> look very large. I also used 'extentreport' from Matthew Devlin and the >> report for this table looks like this: >> >> ----------------------------------------------------------------------------- >> First Next Total Used Num Num >> Table Extent Extent Size Kb Extents Rows >> ----------------------------------------------------------------------------- >> inv_shp_l 16 8192 223008 217874 155 563222 >> >> Thanks >> Gary Quiring >> >> Output from myschema: >> REVOKE ALL ON "informix".quot_l FROM public;>> CREATE TABLE "informix".inv_shp_l ( >> inv_shp_l_id SERIAL(-2147110047) {Curr: 1} NOT NULL, >> <snip> >> item_w_m_id INTEGER >> ) IN data2dbs EXTENT SIZE 217874 NEXT SIZE 16 LOCK MODE ROW; >> >> { >> Current first extend (16) too small! >> Actual pages used by table has been used. >> Tablespace header reports - >> Total: pgsused=108937, pgsdata=93871. >> Please review extent sizing and adjust to allow for growth. >> } > >-- >Phillip Tien >Database Administrator >Whole Foods Market, Inc. > >"I always wanted to be the last guy on Earth just to see if all those women were >lying to me." > > -- David Williams

Gary Quiring wrote: > > Newbie alert!! I ran myschema -d <db> -a and found the extent sizes to > look very large. I also used 'extentreport' from Matthew Devlin and the > report for this table looks like this: Hi Gary, this looks like a difference in style. I like to have as much of the table as possible in a single extent with additional extents sized appropriately (you can add the -n option to have myschema calculate the NEXT SIZE based on EXTENT SIZE rather than just outputting the current NEXT SIZE which is obviously not good judging from the 155 current extents. Obviously Matthew likes a smaller initial extent with a single large second extent. I assume that the current size represents growth over time so that the NEXT SIZE should be smaller than EXTENT SIZE but the -m, -e, & -n options to myschema give you very complete control. At any rate how is an extent size of 217874 too large when the table currently has 217874 KB (108874 pages) used? The myschema output includes the actual pages used (108937 total, 93871 of data) in case you want to make manual adjustments. Note that this report agrees with the extentreport output. Also remember that the EXTENT SIZE and NEXT SIZE are in KB not pages. Art S. Kagel ----------------------------------------------------------------------------- > First Next Total Used Num Num > Table Extent Extent Size Kb Extents Rows > ----------------------------------------------------------------------------- > inv_shp_l 16 8192 223008 217874 155 563222 > > Thanks > Gary Quiring > > Output from myschema: > REVOKE ALL ON "informix".quot_l FROM public;> CREATE TABLE "informix".inv_shp_l ( > inv_shp_l_id SERIAL(-2147110047) {Curr: 1} NOT NULL, > <snip> > item_w_m_id INTEGER > ) IN data2dbs EXTENT SIZE 217874 NEXT SIZE 16 LOCK MODE ROW; > > { > Current first extend (16) too small! > Actual pages used by table has been used. > Tablespace header reports - > Total: pgsused=108937, pgsdata=93871. > Please review extent sizing and adjust to allow for growth. > }