Re.systables.
Question: for a ~90 GB database, should the extent size of system catalog tables like systables be altered, and when? Answers: one poster routinely bumps the next-extent size of systables/syscolumns from the default 16 to 1024 right after CREATE DATABASE to limit extent counts and fragmentation. An IBM developer countered that this only matters if the schema is very dynamic (many new tables/columns); with 90 GB of data it's far more important to size extents on the big user tables at creation time, noting consecutive extents merge, IDS auto-doubles next extent size, and concurrent loads fragment chunks. Another suggested the dictionary cache matters more. The poster accepted the advice; his schema is fairly static and user-table extents were already addressed.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi Everybody, Do we have to alter the extent size for systables, database with probably 90 GB size. If yes when is the right time, when we create database or when the size of the database increases. Thanks in advance. Sushil... _________________________________________________________________ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail
There are quite a few tables whose next extent size you should alter after the "create database" statement; amongst them are systables and syscolumns (others depend upon your environment). I normally change the next extent size from its default of 16 to 1024 give them plenty of room to grow into. Since each table can only have a certain number of extents, the larger the next extent the better. Having the larger size will also keep the fragmentation of these tables down. Take care. Clifton ----- Original Message ----- From: "Sushil Shir...." <sushilps@hotmail.com> To: <ids@iiug.org> Sent: Sunday, February 23, 2003 5:42 PM Subject: Re.systables. [472] > Hi Everybody, > > Do we have to alter the extent size for systables, database with > probably 90 GB size. > If yes when is the right time, when we create database or when the > size of the database increases. > > Thanks in advance. > Sushil... > > > _________________________________________________________________ > The new MSN 8: smart spam protection and 2 months FREE* > http://join.msn.com/?page=features/junkmail >
Hi, hmm. It depends on what you are doing with your database. systables contains one row for each table in your database (+ some static overhead for the system catalog itself), and syscolumns holds, you guessed it, a row for each column (of each table) in your database. If your database schema is going to be rather static, i.e. you have a relatively fixed number of tables and you are not adding lots of columns to these existing tables, then there's no real need to change extent sizes for systables and/or syscolumns. If your database schema turns out to be rather dynamic, especially if you (plan to) add lots of tables and columns over time, then changing the extent sizes for those two tables might help. But even then, with some calculations you can make an educated guess at what would be sensible ... If you have 90 GB of data (which I assume is concentrated in rather few tables), then it is much more important to increase the extent size for those tables (rather than for systables). Since you know already, how big the database is going to be, then I would change the extent sizes upon creation of the database. [ BTW: a) If extents for a single table are allocated sequentially (happens especially during data loading), then these consecutive extents will be treated as one big extent (not many small ones). b) There is a feature "automatic increase of extent size". IDS detects that for a table there've been that many extent allocations (don't remember the absolut number) and then doubles the next extent size automatically for future allocations. This happens repeatedly. c) If you do concurrent data load into different tables, then you are most likely to get chunks fragmented by small extents. This is the scenario to avoid by defining a big enough first extent size - in fact at best so big that all the existing data to be loaded fits into it. ] Regards, Martin -- Martin Fuerderer IBM Informix Development Munich Data Management Solutions "Clifton M. Bean" <cmbean@sbcglobal.net> Sent by: forum.subscriber@iiug.org 24.02.2003 04:32 To: ids@iiug.org cc: Subject: Re.systables. [473] There are quite a few tables whose next extent size you should alter after the "create database" statement; amongst them are systables and syscolumns (others depend upon your environment). I normally change the next extent size from its default of 16 to 1024 give them plenty of room to grow into. Since each table can only have a certain number of extents, the larger the next extent the better. Having the larger size will also keep the fragmentation of these tables down. Take care. Clifton ----- Original Message ----- From: "Sushil Shir...." <sushilps@hotmail.com> To: <ids@iiug.org> Sent: Sunday, February 23, 2003 5:42 PM Subject: Re.systables. [472] > Hi Everybody, > > Do we have to alter the extent size for systables, database with > probably 90 GB size. > If yes when is the right time, when we create database or when the > size of the database increases. > > Thanks in advance. > Sushil...
Hi, Thanks guys for all your valuable suggestions, I will keep in mind the guidance/instruction related to extents of systables its very rare were we change the structure of the database/tables. Right now we don't have any issues with our normal/apps tables because all of our tables are below 8 extents, in fact we recently re-arranged all the big tables to extent 1 (I think the extents double on every 17th extent automatically). Thanks again. Sushil... >From: Martin Fuerderer <MARTINFU@de.ibm.com> >To: "Clifton M. Bean" <cmbean@sbcglobal.net>, sushilps@hotmail.com >CC: forum.subscriber@iiug.org, ids@iiug.org >Subject: Re: Re.systables. [473] >Date: Mon, 24 Feb 2003 02:20:59 -0700 >MIME-Version: 1.0 >Received: from e31.co.us.ibm.com ([32.97.110.129]) by >mc8-f39.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Mon, 24 Feb >2003 01:21:08 -0800 >Received: from westrelay02.boulder.ibm.com (westrelay02.boulder.ibm.com >[9.17.195.11])by e31.co.us.ibm.com (8.12.7/8.12.2) with ESMTP id >h1O9L67b058984;Mon, 24 Feb 2003 04:21:06 -0500 >Received: from d03nm028.boulder.ibm.com (d03av02.boulder.ibm.com >[9.17.193.82])by westrelay02.boulder.ibm.com (8.12.3/NCO/VER6.5) with ESMTP >id h1O9L57f085716;Mon, 24 Feb 2003 02:21:06 -0700 >X-Message-Info: dHZMQeBBv44lPE7o4B5bAg== >X-Mailer: Lotus Notes Release 5.0.7 March 21, 2001 >Message-ID: <OFF3EA342F.51DDBF3E-ONC1256CD7.002D4C28@us.ibm.com> >X-MIMETrack: Serialize by Router on D03NM028/03/M/IBM(Release 6.0 >[IBM]|December 16, 2002) at 02/24/2003 02:21:05,Serialize complete at >02/24/2003 02:21:05 >Return-Path: MARTINFU@de.ibm.com >X-OriginalArrivalTime: 24 Feb 2003 09:21:08.0771 (UTC) >FILETIME=[10A07F30:01C2DBE6] > >Hi, > >hmm. It depends on what you are doing with your database. > >systables contains one row for each table in your database (+ some static >overhead for the system catalog itself), and syscolumns holds, you guessed >it, >a row for each column (of each table) in your database. > >If your database schema is going to be rather static, i.e. you have a >relatively >fixed number of tables and you are not adding lots of columns to these >existing tables, then there's no real need to change extent sizes for >systables and/or syscolumns. > >If your database schema turns out to be rather dynamic, especially if you >(plan to) add lots of tables and columns over time, then changing the >extent >sizes for those two tables might help. But even then, with some >calculations >you can make an educated guess at what would be sensible ... > >If you have 90 GB of data (which I assume is concentrated in rather few >tables), >then it is much more important to increase the extent size for those >tables >(rather than for systables). Since you know already, how big the database >is >going to be, then I would change the extent sizes upon creation of the >database. > >[ BTW: > a) If extents for a single table are allocated sequentially (happens >especially > during data loading), then these consecutive extents will be treated >as one big > extent (not many small ones). > b) There is a feature "automatic increase of extent size". IDS detects >that for > a table there've been that many extent allocations (don't remember the >absolut > number) and then doubles the next extent size automatically for future >allocations. > This happens repeatedly. > c) If you do concurrent data load into different tables, then you are >most likely > to get chunks fragmented by small extents. This is the scenario to >avoid by > defining a big enough first extent size - in fact at best so big that >all the existing > data to be loaded fits into it. ] > >Regards, >Martin >-- >Martin Fuerderer >IBM Informix Development Munich >Data Management Solutions > > > > > >"Clifton M. Bean" <cmbean@sbcglobal.net> >Sent by: forum.subscriber@iiug.org >24.02.2003 04:32 > > > To: ids@iiug.org > cc: > Subject: Re.systables. [473] > > > >There are quite a few tables whose next extent size you should alter after >the "create database" statement; amongst them are systables and syscolumns >(others depend upon your environment). I normally change the next extent >size from its default of 16 to 1024 give them plenty of room to grow into. >Since each table can only have a certain number of extents, the larger the >next extent the better. Having the larger size will also keep the >fragmentation of these tables down. > >Take care. >Clifton > >----- Original Message ----- >From: "Sushil Shir...." <sushilps@hotmail.com> >To: <ids@iiug.org> >Sent: Sunday, February 23, 2003 5:42 PM >Subject: Re.systables. [472] > > > > Hi Everybody, > > > > Do we have to alter the extent size for systables, database with > > probably 90 GB size. > > If yes when is the right time, when we create database or when the > > size of the database increases. > > > > Thanks in advance. > > Sushil... > _________________________________________________________________ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail
Isn't it better to make sure that all the tables you routinely refer to remain in the dictionary cache, than to get too worried about extent sizes for systables & syscolumns. Or do I misunderstand what the dictionary cache is there for? Best regards, Andy. <.. snip ..> -- Andrew Lennard andy@kontron.demon.co.uk
Andy, You are right, but at this stage we already have database in production and making lot of changes to the schema, we have addressed all the data table extents issue so I was curious whether we should consider systables. And I think these tables already exist in the cache. Thanks Sushil... >From: "Andy Lennard " <andy@kontron.demon.co.uk> >To: ids@iiug.org >Subject: Re: Re.systables. [481] Date: Mon, 24 Feb 2003 07:43:50 -0500 >(EST) >Received: from mc8-f10.law1.hotmail.com ([65.54.253.146]) by >mc8-s20.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Mon, 24 Feb >2003 04:47:12 -0800 >Received: from ace.iiug.org ([216.177.38.212]) by mc8-f10.law1.hotmail.com >with Microsoft SMTPSVC(5.0.2195.5600); Mon, 24 Feb 2003 04:47:12 -0800 >Received: (from nobody@localhost)by ace.iiug.org (8.9.3/8.9.3) id >HAA21537;Mon, 24 Feb 2003 07:43:50 -0500 (EST) >X-Message-Info: dHZMQeBBv44lPE7o4B5bAg== >Message-Id: <200302241243.HAA21537@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Sender: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 24 Feb 2003 12:47:12.0494 (UTC) >FILETIME=[D9FAE8E0:01C2DC02] > >Isn't it better to make sure that all the tables you routinely refer to >remain in the dictionary cache, than to get too worried about extent >sizes for systables & syscolumns. > >Or do I misunderstand what the dictionary cache is there for? > >Best regards, >Andy. > ><.. snip ..> > >-- >Andrew Lennard andy@kontron.demon.co.uk > _________________________________________________________________ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail