Re: Flushing changes

Path: emory!wupost!usc!elroy.jpl.nasa.gov!ames!data.nas.nasa.gov!splatter.nas.nasa.gov!bross From: bross@splatter.nas.nasa.gov (Bill Ross) Newsgroups: comp.databases.informix Message-ID: <1992Feb7.201900.27901@nas.nasa.gov> Date: 7 Feb 92 20:19:00 GMT References: <EdYNfaS00hMgQ7TYBn@cs.cmu.edu> Sender: news@nas.nasa.gov Reply-To: bross@splatter.nas.nasa.gov (Bill Ross) Organization: Numerical Aerodynamic Simulation Facility NASA > From: Sean.Levy@cs.cmu.edu > > My isql command tells me it is version 2.10.00B. I use Informix at the > esql level mainly .. > > My problem: occasionaly, when my application crashes, some changes to > the database we made were lost. As I understand it, Informix caches > updates and flushes the cache when it feels like it (or whatever). Is > there a command that I can call from C to explicitly tell Informix to > flush its stuff? ... It may take an operating system modification to protect your data with the Standard Engine since Informix is not interested in opening the transaction log for synchronous writing (~"the transaction log is for transactions, not db integrity"!!). If you don't have a source license for your operating system, good luck. Our solution was to allow a special setting in the inode that forces all subsequent opens to be synchronous. Here is part of my log of the experience of finding out whether the log file is opened synchronously: I believe that every time I have called Informix support in the last year (except just now), I have spent 15 minutes or more on hold. The last time I asked a question, it was, "is the transaction log file written synchronously," which is of vital significance to us since if the answer is "no," we do not have dbase integrity in the event of a crash. I just talked to an engineer who read the case info to me: <case record> case # 126809 isql 2.10.03B opened: 4/10/91 closed: 9/18/91 by administrative default, i.e. it fell on the floor Wants to know if sync triggers code for transaction buffer write. Later: Wants specific proof of correctness of log writing. I was eventually referred to someone for authorization to receive the information on Informix internals. <end what I jotted of case record> Note that the 1st person I spoke to never understood the question, and the secrecy involved in disclosing whether their program is reliable (amounting to whether they open a file O_SYNC or not) is ludicrous. I am not sure that the person who read the case to me understood it either, although eventually he said he did and stopped repeating how a rollforward is done. I believe I tried a little further, but forget what happened exactly except that I never got an answer. Meanwhile, we found out from monitoring from the kernel side that the transaction log wasn't written synchronously, and I also heard indirectly that Informix's response to that was that the transaction log was for transactions and not for dbase integrity across a crash, i.e. if you wanted transactions, you sacrificed dbase integrity. Our solution was to modify the kernel so that synchronous writing was forced. Another tip: don't perform any long transactions, e.g. creation of one table by inserting all rows of another into it, since each suboperation takes longer than the previous one. We also had to modify our binary to give it extended addressing capability because it ran out of memory when rolling forward one such transaction, which eventually took about 12 hours on a mainframe. At least one problem has been reported with the TURBO engine, corrupted tables when running out of lock table space: > After running out of locks, it tried to rollback, but since rollback > needs locks, it barfed. And in turn, managed to destroy live production > data. That, along with the Informix responses to our concerns, redoubles my faith that something else must be better. We are looking at B-tree systems with accessible source code at the moment; e.g. Berkeley has an anonymously ftp-able package which uses a common interface to B-trees & hash tables, to which transactioning is being added. Bill Ross Computer Sciences Corporation NASA Ames Research Center