Re: Experience with large tables using Online
Rob McAllister (rmcall@on-ramp.ior.com) wrote: : There are some annoying bug in 5.01. The worst I have found is with tbtape. : 1) there is no way to verify an archive, might be good might be trash, who : knows. : 2) There is a serious problem with performing level-0 archives while the engine : is on-line. It may not backup all pages to tape. This is because each page has : a time-stamp of the last update or last archive or both (I'm not sure which), : but this time-stamp can go negative at times which are skipped by a level-0 : archive. There is ONLY 2 work arounds. : A) Always perform 2 level-0 archives back to back (Informix told me this : but I don't know how to restore both) and keep both tapes. This method : is not absolutely secure since an active system could cause some pages : to be left off both tapes. Correct me if I'm wrong about this. : B) Go into quiescent mode and do 2 level-0 archives and only keep the : the second. The first archive could be to to /dev/null for speed sake, : but you still have to be down during the entire archive process, YUCK. : : There is also a problem with using temp tables. Apparently, when the engine : drops the temp table, especially within a transaction, sometime the engine will : attempt to drop the wrong table (YIKES). There is an internal trap to prevent : the worst, but the engine comes down as a result. I have had to use perminent : temp tables and delete contents each time they are needed (very slow). : : The last thorny bug only occurs with small tables with a lot of insert and : delete activity, after a while you get a -245 error (ISAM error 103). When this : occurs, the only fix is to unload, drop and re-load the table. I have looked : for help an this problem, but no one seems to know any better work arounds. : Using UPDATE STATISTICS more frequently helps this problem, but creates an even : uglier concurrency problem (locks on the table AND on some system catalogs). My : company is currently trying to decide whether to upgrade to 7.x or switch to a : different RDBMS vendor all together. These bugs, and others, have created a : serious image problem for my company and the complaints are primarily due to : Informix bugs that cause down time and data loss. What is one to do? : : rmcall@on-ramp.ior.com : : -------------------------------------------------------- : Rob McAllister rmcall@on-ramp.ior.com : Spokane, WA : "On all good programmers a little core must dump" : I had not heard about the temp table problem but we have had plenty of headaches over the archive bug problem. We just rolled out new external disk drives to 160 servers and found out that we had faulty hardware. We are now faced with bad disks and bad archives. Apparently the archive bug 18027 also affects the mirror. Since installing the buggy drives we have an average of 2 - 8 disk crashes per week, sometimes both primary and mirror at the same time. We found corruption on restoring from archive and we also had corruption restoring from mirror. After many frustrating calls to Informix I think I finally found someone who knew something about this bug and was able to halfway explain it so that it made some sense. The first workaround is no longer recommended according the the guy I talked to. They had one customer who had to go back 4 generations and restore each successive archive before getting a system without corruption. They spent four days doing it too. There is another bug 9643 that affects the primary chunk going down. The 5.01 engine marks the chunk as down but continues to try to access it. This brings the box to 100% busy and has the potential of corrupting the mirror. I have also recently found out about additional bugs in the 5.01 version that I have yet to verify. Informix has lost all credibility in our shop as a result of all the serious bugs. Programmers now come to me first when their code doesn't work to see if it may be a bug in the engine instead of their code. We would have to upgrade the OS on 160 servers to get to the 5.05 where these bugs are fixed. Informix finally agreed to produce a patch for all the serious bugs that are killing us though and have been very helpful throughout the process. In the meantime however we still have no good archive, several corrupt servers, and a bunch of time bombs waiting to go off. I would be very interested to know if anyone else is using 5.01 and having problems like this. These are the bugs that I am aware of in the 5.01 NCR 3450 OS2.01.01 release. 18027 Archive bug 9643 Primary chunk bug 22101 Informix is sending me info. 14829 Informix is sending me info. Several others but I don't rememeber the numbers. Anyone have any more bugs for my collection? Thanks for any input. If I get a more complete list I will repost or mail it to any interested. brford@xmission.com