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

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. If you find yourself in this situation again, just bounce the Engine, not the machine. klb