Extents and Fragmentation
A new DBA on Informix 7.3/Solaris found a 3GB, 40-million-row table with 212 extents (plus other tables near 200), caused by a tiny 8-page first extent and an unadjusted NEXT SIZE. Replies agreed this is dangerously close to the extent limit and suggested either unload/drop/recreate with proper extent sizes and reload (HPL, then rebuild indexes and update stats), or ALTER TABLE MODIFY NEXT SIZE followed by ALTER FRAGMENT INIT IN. Art Kagel noted a 3GB table can't fit in one extent (one extent per chunk minimum) and recommended capping extent/next size around 500000 KB; others suggested utilities myschema/dostats from IIUG and spreading the table over multiple chunks or fragments.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi guys, I'm a pretty new DBA, so if I sound ignorant, forgive me. In one of my busiest databases, we have a 40 million row table with 212 extents (about 3 MB)and several 4-10 million row tables with close to 200 extents. From what I read read in the past two weeks, this is a very bad thing. I am under the impression that I will soon need to bring down the database, write out these tables, drop the tables, and import the tables back in. The problem I have now was caused by someone setting the first extent size to 8 pages; what is a logical setting? Should the entire table fit into one extent? Any suggestions would be greatly appreciated! btw we are running informix 7.3 on Sun boxes Duff Sent via Deja.com http://www.deja.com/ Before you buy.
duffybj1@my-deja.com writes: > Hi guys, > > I'm a pretty new DBA, so if I sound ignorant, forgive me. We're all ignorants:) > In one of my busiest databases, we have a 40 million row table with 212 > extents (about 3 MB)and several 4-10 million row tables with close to > 200 extents. From what I read read in the past two weeks, this is a > very bad thing. Somewhere between bad and _very_ bad yes (the older Informix-version the badder it is) > I am under the impression that I will soon need to bring down the > database, write out these tables, drop the tables, and import the > tables back in. Or do a "ALTER FRAGMENT INIT IN" >The problem I have now was caused by someone setting > the first extent size to 8 pages; what is a logical setting? Should the > entire table fit into one extent? What they really missed was NEXT SIZE > Any suggestions would be greatly appreciated! Install and compile Art's wonderfull myschema (utils_3 [?] in the software archive at www.iiug.org), and let it do the math for you. If you'd like to do an ALTER FRAGMENT instead of dropping the table you'd probably want to do a "ALTER TABLE ... MODIFY NEXT SIZE ..." first. It _could_ be faster to export the table, drop it, create it, import it, create indexes and run dostats (Art again...). > > btw we are running informix 7.3 on Sun boxes I've got 171 extens on a >10 million rows table (9.20.UC1 on Linux) with no apparant problems. Someone from Informix posted here this week about 7.x (?) with ~200 extents giving up so you should do something before it vomits on you. Thomas
Correction: size of the 40 million row table is 3GB, not MB In article <8iar7l$tuk$1@nnrp1.deja.com>, duffybj1@my-deja.com wrote: > Hi guys, > > I'm a pretty new DBA, so if I sound ignorant, forgive me. > > In one of my busiest databases, we have a 40 million row table with 212 > extents (about 3 MB)and several 4-10 million row tables with close to > 200 extents. From what I read read in the past two weeks, this is a > very bad thing. > > I am under the impression that I will soon need to bring down the > database, write out these tables, drop the tables, and import the > tables back in. The problem I have now was caused by someone setting > the first extent size to 8 pages; what is a logical setting? Should the > entire table fit into one extent? > > Any suggestions would be greatly appreciated! > > btw we are running informix 7.3 on Sun boxes > > Duff > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.
duffybj1@my-deja.com wrote: > > Hi guys, > Hi > I'm a pretty new DBA, so if I sound ignorant, forgive me. We were all ignorant at one time . . . . some of us still are . . .. 8-) > > In one of my busiest databases, we have a 40 million row table with 212 > extents (about 3 MB)and several 4-10 million row tables with close to > 200 extents. From what I read read in the past two weeks, this is a > very bad thing. Ouch . . . you _did_ say 212 extents? That's somewhere between "bad" and "I can't allocate any more extents" bad. > > I am under the impression that I will soon need to bring down the > database, write out these tables, drop the tables, and import the > tables back in. The problem I have now was caused by someone setting > the first extent size to 8 pages; what is a logical setting? Should the > entire table fit into one extent? > Sounds like someone just kept the default size and never updated any storage requirements. One thing I do is keep track of new tables and reset the first extent to at least hold the initial load; the next extent size I'll ballpark at 1/10. Then my table tracking program monitors the table and gives me good information about growth patterns and such. Yup, you might need to schedule some downtime. When you dump and reload the tables, I'd recommend the high-performance loader. It's great for large tables. Ideally, the first extent should be the table's current size. My guess is that there are a lot of other tables in this dbspace. In order to allow this table to occupy a contiguous space, you might need to unload and drop these other tables as well. When I reorg my dbspaces, my target is to get all tables to occupy one extent, updating my 'next extent' numbers to give me a bit of growth before I have to do it again. > Any suggestions would be greatly appreciated! > > btw we are running informix 7.3 on Sun boxes > > Duff > > Sent via Deja.com http://www.deja.com/ > Before you buy. -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */
duffybj1@my-deja.com wrote: > > Hi guys, > Hi > I'm a pretty new DBA, so if I sound ignorant, forgive me. We were all ignorant at one time . . . . some of us still are . . .. 8-) > > In one of my busiest databases, we have a 40 million row table with 212 > extents (about 3 MB)and several 4-10 million row tables with close to > 200 extents. From what I read read in the past two weeks, this is a > very bad thing. Ouch . . . you _did_ say 212 extents? That's somewhere between "bad" and "I can't allocate any more extents" bad. > > I am under the impression that I will soon need to bring down the > database, write out these tables, drop the tables, and import the > tables back in. The problem I have now was caused by someone setting > the first extent size to 8 pages; what is a logical setting? Should the > entire table fit into one extent? > Sounds like someone just kept the default size and never updated any storage requirements. One thing I do is keep track of new tables and reset the first extent to at least hold the initial load; the next extent size I'll ballpark at 1/10. Then my table tracking program monitors the table and gives me good information about growth patterns and such. Yup, you might need to schedule some downtime. When you dump and reload the tables, I'd recommend the high-performance loader. It's great for large tables. Ideally, the first extent should be the table's current size. My guess is that there are a lot of other tables in this dbspace. In order to allow this table to occupy a contiguous space, you might need to unload and drop these other tables as well. When I reorg my dbspaces, my target is to get all tables to occupy one extent, updating my 'next extent' numbers to give me a bit of growth before I have to do it again. > Any suggestions would be greatly appreciated! > > btw we are running informix 7.3 on Sun boxes > > Duff > > Sent via Deja.com http://www.deja.com/ > Before you buy. -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */
Both myschema and dostats are in the package utils2_ak. You will not get a 3GB table into one extent since there are at least one extent per chunk and 3GB is larger than the largest possible chunk so the very best you can do is 2 extents, one about 2GB and the remainder but do not make NEXT SIZE 2000000 because the table will suck up two ENTIRE chunks. I make 500000 an absolute maximum extent size or next size, if the engine can fill a chunk with four contiguous extents from the same table and compress them to one huge extent great otherwise... Art S. Kagel Thomas Parsli wrote: > > duffybj1@my-deja.com writes: > > > Hi guys, > > > > I'm a pretty new DBA, so if I sound ignorant, forgive me. > > We're all ignorants:) > > > In one of my busiest databases, we have a 40 million row table with 212 > > extents (about 3 MB)and several 4-10 million row tables with close to > > 200 extents. From what I read read in the past two weeks, this is a > > very bad thing. > > Somewhere between bad and _very_ bad yes > (the older Informix-version the badder it is) > > > I am under the impression that I will soon need to bring down the > > database, write out these tables, drop the tables, and import the > > tables back in. > > Or do a "ALTER FRAGMENT INIT IN" > > >The problem I have now was caused by someone setting > > the first extent size to 8 pages; what is a logical setting? Should the > > entire table fit into one extent? > > What they really missed was NEXT SIZE > > > Any suggestions would be greatly appreciated! > > Install and compile Art's wonderfull myschema (utils_3 [?] in the software archive > at www.iiug.org), and let it do the math for you. > > If you'd like to do an ALTER FRAGMENT instead of dropping the table you'd > probably want to do a "ALTER TABLE ... MODIFY NEXT SIZE ..." first. > > It _could_ be faster to export the table, drop it, create it, import it, create indexes > and run dostats (Art again...). > > > > btw we are running informix 7.3 on Sun boxes > > I've got 171 extens on a >10 million rows table (9.20.UC1 on Linux) with no apparant > problems. Someone from Informix posted here this week about 7.x (?) with ~200 extents > giving up so you should do something before it vomits on you. > > Thomas
When you load a table from scratch, the first extent size is basically ignored since the data will be loaded continguously. If you fill one chunk and hope to another, each will be considered one extent. With such a large table, you might want to either load it into one multiple drives (one dbspace, multiple chunks) (round robin) or fragment it by expression (multiple dbspaces). Take care. Clifton "Carlson@WHSmith" <carlson1@bellsouth.net> wrote in message news:642A954DD517D411B20C00508BCF23B00124AE6F@mail.sauder.com... > duffybj1@my-deja.com wrote: > > > > Hi guys, > > > > Hi > > > I'm a pretty new DBA, so if I sound ignorant, forgive me. > > We were all ignorant at one time . . . . some of us still are . . .. 8-) > > > > > In one of my busiest databases, we have a 40 million row table with 212 > > extents (about 3 MB)and several 4-10 million row tables with close to > > 200 extents. From what I read read in the past two weeks, this is a > > very bad thing. > > Ouch . . . you _did_ say 212 extents? That's somewhere between "bad" > and "I can't allocate any more extents" bad. > > > > I am under the impression that I will soon need to bring down the > > database, write out these tables, drop the tables, and import the > > tables back in. The problem I have now was caused by someone setting > > the first extent size to 8 pages; what is a logical setting? Should the > > entire table fit into one extent? > > > > Sounds like someone just kept the default size and never updated any > storage requirements. One thing I do is keep track of new tables and > reset the first extent to at least hold the initial load; the next > extent size I'll ballpark at 1/10. Then my table tracking program > monitors the table and gives me good information about growth patterns > and such. > > Yup, you might need to schedule some downtime. When you dump and reload > the tables, I'd recommend the high-performance loader. It's great for > large tables. > > Ideally, the first extent should be the table's current size. My guess > is that there are a lot of other tables in this dbspace. In order to > allow this table to occupy a contiguous space, you might need to unload > and drop these other tables as well. When I reorg my dbspaces, my > target is to get all tables to occupy one extent, updating my 'next > extent' numbers to give me a bit of growth before I have to do it again. > > > Any suggestions would be greatly appreciated! > > > > btw we are running informix 7.3 on Sun boxes > > > > Duff > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > > -- > John Carlson > Informix DBA > WHSmith USA > > #include std_disclaimer.h /* These are my opinions, not my company's > opinion */ >