Re: What happens when a table has over 200 extents?
In article <44u8jt$o1o@access1.digex.net>, lester@access1.digex.net (Lester Knutsen) writes: |> Hello |> |> I have a site with a very large and fragmented database. One table |> has over 170 extents, and many have over 8 extents. The database |> is in use 6 days a week, 24 hours. I need to justify taking the |> database down for a few days to fix things. A couple of questions. |> |> Does anyone know what happens when you go over the max number of |> extents for a table? Does everything stop working or just |> adding rows to one table? I would really like hear from anyone |> who has gone over the max number of extents. Lester HURRY and FIX THIS!!! The limit is "platform dependent" and is 213 extents on a Sun running 4.1.3 and 5.01 Online (I know from experience). When the limit is exceeded (at least in my case) the engine crashed and was unrecoverable without lots of help from the SWAT team from Informix (they only come with a hefty price tag as well). Not only was the raw disk structures corrupted but we could not recover from log tapes (continuous logging). The Informix guys helped and recovered what was "recoverable" from the tape (after a restore). We then did some reorgs of the data and went on from there. Total recovery time: 1.5 DAYS (this of course was in a shop that was 7x24). |> |> What kind of performance gain should we expect from fixing |> the extents? 10%, 20%, etc ...?? Actually, the performance increase was pretty good. This is my understanding (please correct me if some knows differently): If the number of extents is <8 then extent/page information can be found with a single read operation. Extent information for extents 8-63 can be had for the cost of two reads. >63 extends cost three reads. I guess the levels of indirection act much like the old i-node structures in the UNIX file system. What this means is that keeping tables under 8 extents has a HUGE benefit (halving the number of reads at least to get information as to where pages are on disk). This is not always practical (and easy). My rule of thumb is to keep high volume tables to under 8 if possible and under 64 for sure. |> |> Thanks for sharing your experience. |> |> Regards - Lester |> |> |> ############################################################################# |> # Lester Knutsen lester@access.digex.net # |> # Advanced DataTools Corporation Voice: 703-256-0267 # |> # Grant group privileges for Informix databases with DB Privileges # |> # Visit our Web page: http://www.access.digex.net/~lester # |> ############################################################################# |> -- ---------------------------------------------------------------------------- Mike Reetz reetz@ncar.ucar.edu 3450 Mitchell Ln UNIX(tm) Operations Manager (303)497-8881(V) 497-8501(F) Building 1 UCAR - University Corporation For Atmospheric Research Boulder, CO 80301