Re: BUFFERED LOGGING: Is it safe??

I want to make some minor amendments to Clem's answer, simply to clarify the multi-user aspects of buffered logging... >Date: Fri, 8 Sep 1995 08:58:26 -0500 >From: Clem Akins <cwakins@leia.alloys.rmc.com> >X-Informix-List-Id: <list.7408> > >> 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? >> >> In other words, could buffered logging compromise the integrity of the >> database in the event of a failure? >> >> Thanks in advance ... >> -- >> ______________________________________________________________________________ >> Steve Chell steve@nezsdc.fujitsu.co.nz >> Fujitsu NZ Ltd, Auckland, New Zealand FAX: +64.9.3564851 > >There is always the possibility of a compromise of integrity in the event >of a failure. With buffered logging you may lose more logical log records >(those associated with committed transactions) because the engine stores >more of them in memory. This is done to allow the user to get on with his >work, and to keep him from having to wait on the disk. Correct so far. It also means that there is less disk traffic, which benefits all users by releasing the disk for other activity. Of course, if your logical logs are on their own private disk not used for anything else, this isn't any saving, but many (most) installations aren't big enough to warrant that treatment. >With unbuffered logging the engine only keeps one transaction at a time in >memory, and each user must wait for that one to be written to the logical >logs (on disk) before he can do anything more. Here's where I have problems with Clem's explanation. It seems to me to imply minimal (rather than maximal) concurrency. I'm sure Clem knows about this but... The data on the logical log pages contains whatever information is required for any and all concurrent transactions, not just one at a time. There is only one transaction per user, but there are typically many transactions occurring at the same time and they all use the logical concurrently. Strictly, each transaction takes it in turns to access the log, and only one process accesses it any given time, but the information from multiple transactions is interleaved in the log. Thus far, it is the same for both unbuffered and buffered logging. The big difference is what happens when a user executes COMMIT WORK. 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. 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. Note that if any database in the OnLine system has unbuffered logging (or is MODE ANSI), then any COMMIT operations on that database will flush the single logical log, even if the other databases are all buffered logging. >You still stand to lose that one transaction in the event of a failure >even using unbuffered logging, which could leave your data in an >inconsistant state. With unbuffered logging, once the COMMIT returns successfully, the data necessary to recover that transaction is securely out on disk and you will not lose the data for that transaction unless the logical log disk is trashed before the logical logs are backed up. This is a good reason for having mirroring on the dbspace containing the logs (logical and physical). >Buffered logging does carry the risk of the recovery mechanism not being >able to restore. It does not mean that the engine would roll back more >transactions, the engine would not know about them at all. Buffered logging will recover all the transactions known to be committed, and will rollback all transactions not known to be committed, and will hold fire on any distributed transactions which are in the critical indeterminate state. What may be true is that there were some transactions for which the COMMIT records were in shared memory but not on disk, and these transactions may be rolled back because the disk does not record that they were committed. >No matter how far back you push the risk, by archives, logical logs, buffered >logs, and backup data files, it's always there. You must judge whether the >risks justify the costs, and where your break-even point lies. Agreed. >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 tend toward the other view: use unbuffered logging unless there is a compelling reason (eg legal) for having every transaction committed. It depends on the application(s) and on the consequences of a dropped transaction. And also on your assessment of the reliability of the hardware, the o/s, and the database software. It really depends on what level of risk is acceptable -- Clem's view is safer than mine! >Have fun, I am:-) Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>