Re: fragmentation factor

malcolm weallans wrote: > And I haven't seen any definitive reasons why it doesn't matter. I am just > told by many people, including Informix Tech Support, that it isn't a good > idea to have large numbers of extents. Now if anybody who says we shouldn't > have too many extents can explain that reasoning I am sure we would all be > very grateful. Malcolm, here's my take. In modern disk subsystems and IDS releases of Informix fragmentation of a table has little effect on performance IN THE GENERAL CASE. It will have no effect at all IFF data locality is strong, ie most of the rows accessed by any single query are located in the same one or a few extents. Fragmentation WILL have an effect if data locality is very weak, ie if your system regularly queries for or filters out rows which are spread over many extents spashed across the disk landscape. How much of an effect? That depends on many factors: - How much cache on the drives, controllers, arrays and in the engine. - How actively other data may be thrashing one or more of these caches. - How much the engine will have to contend with other applications moving the drive heads during intensive random read activity. So, to sum it up. Fragmentation is far less of a problem than it was in OL[45].xx where only 8 extent locations per table were cached in memory (IDS [789].xx & IDS 10.xx cache the entire extent map as part of the partition page). Depending on your apps and system usage it can still be a concern. Personally if I don't know the locality of a particular table I want its extents to number in the double digits at worst. If I know the locality is poor, I want single digits. If I know the locality is very good I'll only start to worry when the count passes about 150 so I don't run out of extent slots on the partition page (assuming 2K pagesize). Art S. Kagel

Art's comments here are most enlightening. In fact a recent experiment on a single-user machine with ample shared memory and a reasonable amount of processor power for the job involved has shown that when doing a load operation to a system with a large number of high-extent tables (40-70) a particular run was taking about 25 seconds. Having exported and imported the database the tables all had less than 10 extents. Now the operations that took 25 seconds are taking about 17 seconds. The load operation is quite complex and involves a fair amount of processing, and some queries, so it is not just a straight onload. I appreciate it isn't a comprehensive test, but it indicates to me that it is still worth the effort to set extent sizes appropriately for the active tables. regards Malcolm Art S. Kagel wrote: > Malcolm, here's my take. In modern disk subsystems and IDS releases of > Informix fragmentation of a table has little effect on performance IN THE > GENERAL CASE. It will have no effect at all IFF data locality is strong, ie > most of the rows accessed by any single query are located in the same one or > a few extents. Fragmentation WILL have an effect if data locality is very > weak, ie if your system regularly queries for or filters out rows which are > spread over many extents spashed across the disk landscape. How much of an > effect? That depends on many factors: > > - How much cache on the drives, controllers, arrays and in the engine. > - How actively other data may be thrashing one or more of these caches. > - How much the engine will have to contend with other applications moving > the drive heads during intensive random read activity. > > So, to sum it up. Fragmentation is far less of a problem than it was in > OL[45].xx where only 8 extent locations per table were cached in memory (IDS > [789].xx & IDS 10.xx cache the entire extent map as part of the partition > page). Depending on your apps and system usage it can still be a concern. > > Personally if I don't know the locality of a particular table I want its > extents to number in the double digits at worst. If I know the locality is > poor, I want single digits. If I know the locality is very good I'll only > start to worry when the count passes about 150 so I don't run out of extent > slots on the partition page (assuming 2K pagesize). > > Art S. Kagel