Re: EXTENTS discussion proposal

> Proposal for Group discussion > Some One-Liners about EXTENTS... RTFM. Extents are thoroughly discussed in the "Informix Guide to SQL Tutorial, v4.1, December 1991 Part No. 000-7117". They are not discussed AT ALL in the v7.1 Tutorial, however, and it is a tragic omission. I guess you have to get the old copy, it is *well worth* the shelf space. Extents are discussed in the new "Informix-OnLine Dynamic Server Administrator's Guide, Volume 2 version 7.1, December 1994, Part No. 000-7777", and pretty much the same information is in the v5.0 Guide, too. > 1. An EXTENT is a physical unit of storage. An EXTENT is some amount of contiguous disk pages that the database engine has allocated for the use of some specific table. Disk space is converted from unused disk to tablespace (used disk dedicated to one table) as the tablespace is increased by some EXTENT. A PAGE is a physical unit of storage, as is a BLOCK. The size of a PAGE or BLOCK may vary according to your operating system, but is specified in the onconfig file under BUFFSIZE. > 2. EXTENTS are defined in Kilobytes. This is true. If you are a DBA you should know also that the mechanisms for providing raw disk space work in PAGES, and in BYTES. Be very careful with your units and have someone provide a sanity check on your work, as confusion over the units of measure has erased more than one database. > 3. A TABLE has a FIRST EXTENT and a NEXT EXTENT size. At the IWUC '95 one expert claimed that the FIRST size was never used by the internal code, but only the NEXT size. > 4. EXTENTS are contiguous PAGES contained in a CHUNK. > 5. Define the FIRST SIZE and NEXT SIZE of EXTENTS when creating the TABLE. You do not have to specify these parameters if the table will be created in contiguous space. The extents will be treated as a single extent. (This from the ODS Admin Guide, v7.1 pg 43-31) > 6. You can ALTER the NEXT SIZE of a TABLEs NEXT EXTENT. > 7. EXTENT interleaving or FRAGMENTATION degrades performance. > 8. tbcheck -ce reports when this degradation starts to hurt. > 9. Minumum EXTENT size is 4 PAGES. > 10. Maximum EXTENT size big (16,777,217). This is in PAGES, so the maximum size of a single extent is: 16,777,217 * BUFFSIZE. The maximum tablespace is that number multiplied by the maximum number of extents (see below.) (This from the ODS Admin Guide, v7.1 pg 43-26) > 11. Default EXTENT size is 8 PAGES. > 12. The NEXT EXTENT is allocated when the current EXTENT is full. > 13. EXTENT SIZES kinda double (nearest 20K) every 16 EXTENTS. > 14. You can get rid of EXTENT interleaving (Fragmentation) in many of ways. > 15. tbunload/tbload the db to compress EXTENTS. > 16. ALTER <index> to cluster to physically re-organise the TABLE(EXTENTS). Of course this requires an amount of free disk space equal to the size of the table and its indexes. The process creates a COPY of the existing table. Also, there should be enough contiguous free space left for the creation of the table; that is, it should not be re-created in a fragmented form. Informix does not provide a good tool for examination of table fragmentation. oncheck yields a numeric output but is difficult to read. > 17. unload/save schema/drop table/create table/load table works too. > 18. There is a limit to the number of EXTENTS a TABLE can safely have. This is a quickie script written for the Digital DECsystem (RISC) under Ultrix. It is a standard Bourne shell, but you may need to change something to use it. It shows: the size informix uses to store each row (sum(each col + 4)) the total number of pages used by the table (this is the size of the extent needed to hold that table) the maximum number of extents allowed for the table (for those DBA's who inherited a system with the extent/next size for each *large* table at the default size of 8) ------------------------------ cut here ------------------------------------- #!/bin/sh #set -x # calculate extent sizing for a table # and upper limit on extents for a table # these formulas taken from # Informix Guide to SQL Tutorial v4.10 July 1991 pg 10-12 # # cwa 5-18-93 pgsize=2048 #in tbconfig file pageuse=`expr $pgsize - 28` rowsize=0 echo "Enter the estimated number of rows: " read estrows echo "How many columns in the table? " read numcols colspace=`expr $numcols \\* 4` echo "Enter the maximum row size (from tbcheck)" echo "or zero to describe each column one-by-one: " read rowsize if [ $rowsize = 0 ] then i=0 while [ $i -lt $numcols ] do i=`expr $i + 1` clear echo "" echo "" echo " TEXT and BYTE: ................. 56 bytes" echo " SMALLINT: ....................... 2 bytes" echo " INTEGER: ........................ 4 bytes" echo " SMALLFLOAT: ..................... 4 bytes" echo " FLOAT: .......................... 8 bytes long" echo " SERIAL: ......................... 4 bytes long" echo " DATE: ........................... 4 bytes long" echo "" echo " MONEY & DECIMAL: 1/2 total digits + 1 (rounded up)" echo "" echo " DATETIME & INTERVAL:" echo " length is (((sum of digits)/2) +1) (rounded up)" echo " digits are:" echo " YEAR: .......4 digits (unless you specified more)" echo " FRACTION: ...3 digits (unless you specified more)" echo " all others: .2 digits (unless you specified more)" echo "" echo " CHAR columns as defined" echo "" echo "Enter the size in bytes of column $i: " read sizetemp rowsize=`expr $rowsize + $sizetemp` done rowsize=`expr $rowsize + 4` fi echo "How many indexes in the table? " read numindexes ixspace=`expr $numindexes \\* 12` ixparts=0 ixtemp=0 i=0 while [ $i -lt $numindexes ] do i=`expr $i + 1` echo "Enter the number of columns in index $i: " read numixcols ixtemp=`expr $numixcols \\* 4` ixparts=`expr $ixparts + $ixtemp` done echo "The size of the row is: $rowsize" if [ $rowsize -le $pageuse ] then homerow=$rowsize overpage=0 else homerow=`expr 4 + rowsize % pageuse` overpage=`expr $rowsize / $pageuse` fi datrows=`expr $pageuse / $homerow` if [ $datrows -gt 255 ] then datrows=255 fi dattemp=`expr $estrows % $datrows` #modulo (remainder) if [ $dattemp != 0 ] then datpages=`expr $estrows / $datrows` datpages=`expr 1 + $datpages` else datpages=`expr $estrows / $datrows` fi expages=`expr $overpage \\* $estrows` totpages=`expr $datpages + $expages` echo "Total pages required for this table: $totpages" exttemp=`expr $colspace + $ixspace + $ixparts + 84` extspace=`expr $pgsize - $exttemp` limit=`expr $