Re: Re-create Index on a Large Table ( 7.1 )

In article <3v140a$m6v@txirvuhw302.gtetel.com>, kbrand@gtetel.com (Kevin Brand) wrote: >Diana Li (li@inetgw.fsc.ibm.com) wrote: >> Hi Everyone, > >> I have a table with 450,000 records inserted via an ESQL/C program. Since >> the transaction rate started to go down when the number of records approached >> 300,000 and running update statistics took a long time and did not seem to help >> much, I tried to drop and re-created the one and only primary index on the >> table and went into a worse situation. > >> After I invoked dbaccess to run the drop and create index SQL file, I saw >> "Killed" appeared on my console. After that the host was not accessible >> by any command, only returns "The fork function failed. There is not enough >> memory available". So my system administrator re-booted the box. > >> I am running AIX 3.2.5, Informix 7.10. The index takes about 22 MB disk >> space at that time and I have 20 MB virtual memory and 35 MB disk space for >> DBSPACETEMP in server configuration.> >> There is no error message reported in Informix server log. As far as I know >> drop and create an index uses DBSPACETEMP and virtual portion of shared memory. >> I guess something went beyond at O.S. level. > >> Please shed some lights. Any info. is greately appreciated ! >> > > >I have been running 5.02.UC9 on AIX 3.2.5, and have found that this is a VERY >solid Engine. I routinely re-cluster indexes, update statistics, etc ... on tables >that range in size from 0 to 8 million rows ( granted, the big ones are a bit >more complicated )! > >Recently, I had the opportunity to 'help out' on a similar system, >where the Engine had just been upgraded to 7.1. Among other bugs ( mostly bugs >relating to the embedded lang tools ), I found that there may be a problem with >the 7.1 shared memory dynamic allocation. > >Check the SHMTOTAL in the onconfig file, make sure it's NOT set to 0. During >a large index build on this system, the box died. The logs showed massive chunks of >memory being taken by the Engine as the index build progressed, until eventually the >OS returned an error. I believe this is another Informix >bug, but couldn't really look into it ( no permission ). Worst case, unload the rows, >re-build the table with indexe(s), then re-load the rows. It sure is an Informix Bug. I found the same bug when trying to rebuild a large index. Fortunately, I didn't have SHMTOTAL=0 (was already bitten on that one). It's fixed (supposedly) in 7.1UD1 (I'm going to install it next week). Gary Mitchell Mitch@lexmark.com