Re: BUFFERED LOGGING: Is it safe??

In article <42q2ib$9ah@cssun.mathcs.emory.edu>, Jonathan Leffler <johnl@informix.com> wrote: >I want to make some minor amendments to Clem's answer, simply to clarify >the multi-user aspects of buffered logging... Most of your corections were correct, there was one exception however > >>> I'm trying to decide whether to use buffered or unbuffered logging >>> for a customer's database. The engine is Online 5.02.UC6. >>> >>> The Online Administrator's Guide says that "if you use buffered logging >>> and a failure occurs, you could lose more than just the current >>> transaction. In return for this risk, performance during alterations is >>> slightly improved." Does this mean that buffered logging carries with it >>> the risk that the fast-recovery mechanism might not be able to restore the >>> database to a consistent state in the event of a failure, or does it merely >>> mean that more transactions might be rolled back than otherwise would be? >>> > >With buffered logging, the COMMIT record is written into the in-memory >logical log page, and that's all. There is no guarantee that the data will >be written to disk, so if the machine crashes between the COMMIT point and >when the logical log page is flushed to disk, the COMMIT record for the >transaction is lost, and when the system is restored, the transaction will >be rolled back. The database won't be inconsistent; it will be consistent >with the state prior to that transaction. Right, but by "logical log page" you mean the logical log buffer whose size is specified in your config file and can be many pages. Actually there are two such buffers so that commits can be piled up in one while the other is being written (== piggyback commits). > >With unbuffered logging, by contrast, when the COMMIT record is written >into the in-memory logical log page, the logical log is also flushed to >disk, along with any other information that is necessary to be able to >recover the system (meaning, primarily, any stuff in the physical log >buffers). Thus, if the system crashes without trashing the disk containing >the logical log, the transaction will be recoverable. The downside of this >is that if UserA commits a transaction, the whole logical log is flushed to >disk, even if the page containing the COMMIT record is 1% full. And it >will be flushed for each user whenever a transaction is committed (give or >take piggy-backed commits, which don't really affect the argument). So, >there will be greater traffic on the logical logs because (a) partial pages >are written, and (b) each committed transaction causes writes. Correct, except for the part about the physical log. A commit does not cause the physical log buffer to be flushed. Its flushed whenever a page is about to be written and the modification time on the page is since the last physical log flush, thus the before image of this page is not yet on disk (and at some key points like checkpoints of course). Actaully there are some optimizations here as well concerning pages that we know had no sturctural changes so that its safe to not flush the before image but lets not get into that. > > >>I'd choose unbuffered logging (as the Admin Guide suggests), and then change >>it as a last-ditch tuning option if the performance was not acceptable. I generally agree with Clem here, I'd start with UNbuffered, if its not fast enough, play with log placement, play with bufer sizes, or more likely play with the application. I can't think of tooo many applications where loseing commited data would be ok. It may be very unlikley but when it happens its a pain in the rear. -jrg -- internet jrg@galloway.sj.ca.us John R. Galloway, Jr 795 Beaver Creek Way applelink D3413 CEO...receptionist San Jose, CA 95133 Galloway Research (408) 259-2490 v (408) 259-5058 f