Buffer or Unbuffer
User asked about behavior of buffered vs unbuffered databases in Informix when programs fail. Experts clarified: rollback behavior is identical, but buffered mode has a vulnerability window where committed transactions may still be in memory buffers and could be lost if the instance crashes before flushing to disk.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi to ALL, Hi Tarn, The last month I have tried several thing to improve the performance between the application and the database server (PDQ, FET_BUF_SIZE BUFFER) . I came up to the conclusion that if I convert my database to BUFFER mode the speed is almost doubled. Now if I convert the database from Unbuffer to Buffer mode and a program fails , it will roll back the changes of the specific program ? What it will happened with the changes of the previous program that it might not flushed in the disk and are still in the memory (buffers ) ? Best Regards Description: Description: CoopLogo1Description: Description: CoopLogo1 Cooperative Computer Society (S.E.M) Ltd 1306 Nicosia P.O.B. 25037 CY Tel: +357 22 673 901 Fax: +357 22 672 774 Achilleas Achilleos Official A OS and Databases Management <mailto:AchilleasAchilleos@semltd.com.cy> AchilleasAchilleos@semltd.com.cy --Boundary_(ID_b8WbREH6A+/bjNgPOZl87g)
On Sun, Aug 5, 2012 at 10:29 PM, Achilleas Achilleos < AchilleasAchilleos@semltd.com.cy> wrote: > The last month I have tried several thing to improve the performance > between > the application and the database server (PDQ, FET_BUF_SIZE BUFFER) . > > I came up to the conclusion that if I convert my database to BUFFER mode > the > speed is almost doubled. > > Now if I convert the database from Unbuffer to Buffer mode and a program > fails, it will roll back the changes of the specific program? > Rollback is the same in buffered and unbuffered databases. There might occasionally be a slight difference in COMMIT, though, because with an unbuffered database, the logical log is automatically flushed so that your transaction is safe on disk before the COMMIT completes, but with a buffered database, the logical log is not flushed until its buffer is full, which might be some time (typically, at most a few seconds) later. The delay does depend on the activity level of the instance; it is shorter on more active instances. If the instance crashes while the logical log is not flushed, the recovery will treat the committed transaction as incomplete and roll it back (because the information that the transaction was complete was in the buffers, and not in the log on disk). With unbuffered logging, that (small) window of vulnerability is avoided. > What it will happened with the changes of the previous program that it > might > not flushed in the disk and are still in the memory (buffers)? > In a buffered database, if another program has committed and its changes are not yet flushed, and your commit is not flushed, then both sets of changes are not yet finally safe on disk until the logical log buffer is flushed to disk. Note that any commit in any unbuffered database in the system flushes the logical log buffer for all databases in the instance. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d04088c75de59c204c69300b6
Jonathan already explained the details. I'll just add another point of view that I believe to be important. In a bufferred database your application can issue a COMMIT and receive the confirmation but nevertheless all this can be in the memory buffer. So there is the possibility that a "confirmed" COMMIT can be rolled back if there is an unexpected stop (crash, power or disk failure etc.). So, in other words the status perceived by the application may not match the database status. This can have serious impacts specially if your application interacts with other systems. On the other hand, unexpected stops should be rare... In any case it's a business decision. Can you handle an occasional "inconsistency" between the database and the application? Note that from the database point of view the crash recovery assures that it will get to a consistency point... But not necessarily the one that the application "thinks" it reached. Regards. On Mon, Aug 6, 2012 at 6:29 AM, Achilleas Achilleos < AchilleasAchilleos@semltd.com.cy> wrote: > Hi to ALL, > > Hi Tarn, > > The last month I have tried several thing to improve the performance > between > the application and the database server (PDQ, FET_BUF_SIZE BUFFER) . > > I came up to the conclusion that if I convert my database to BUFFER mode > the > speed is almost doubled. > > Now if I convert the database from Unbuffer to Buffer mode and a program > fails , it will roll back the changes of the specific program ? > > What it will happened with the changes of the previous program that it > might > not flushed in the disk and are still in the memory (buffers ) ? > > Best Regards > > Description: Description: CoopLogo1Description: Description: CoopLogo1 > > Cooperative Computer Society (S.E.M) Ltd > > 1306 Nicosia > > P.O.B. 25037 CY > > Tel: +357 22 673 901 > > Fax: +357 22 672 774 > > Achilleas Achilleos > > Official A > > OS and Databases Management > > <mailto:AchilleasAchilleos@semltd.com.cy> AchilleasAchilleos@semltd.com.cy > > --Boundary_(ID_b8WbREH6A+/bjNgPOZl87g) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00248c70fddd6a50ff04c694f661
Tarn: One other fact that often gets over looked is that the buffered mode only defaults to the database buffering mode, it is NOT mandated. What I would suggest is that you set the buffered mode to non-logged, then application by application you examine if it is safe to move to a buffered database logging mode. You can do this with the "SET LOG BUFFERED" statement. A batch job which does allot of updating or loading which can be re-started would be an ideal candidate for Buffered logging. The logging mode is a property of the user, not the database. The user derives their default from the database property. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 08/06/2012 01:50:04 AM: > From: "Fernando Nunes" <domusonline@gmail.com> > To: ids@iiug.org > Date: 08/06/2012 01:50 AM > Subject: Re: Buffer or Unbuffer [27941] > Sent by: ids-bounces@iiug.org > > Jonathan already explained the details. I'll just add another point of view > that I believe to be important. > In a bufferred database your application can issue a COMMIT and receive the > confirmation but nevertheless all this can be in the memory buffer. > So there is the possibility that a "confirmed" COMMIT can be rolled back if > there is an unexpected stop (crash, power or disk failure etc.). > > So, in other words the status perceived by the application may not match > the database status. This can have serious impacts specially if your > application interacts with other systems. > On the other hand, unexpected stops should be rare... In any case it's a > business decision. Can you handle an occasional "inconsistency" between the > database and the application? Note that from the database point of view the > crash recovery assures that it will get to a consistency point... But not > necessarily the one that the application "thinks" it reached. > > Regards. > > On Mon, Aug 6, 2012 at 6:29 AM, Achilleas Achilleos < > AchilleasAchilleos@semltd.com.cy> wrote: > > > Hi to ALL, > > > > Hi Tarn, > > > > The last month I have tried several thing to improve the performance > > between > > the application and the database server (PDQ, FET_BUF_SIZE BUFFER) . > > > > I came up to the conclusion that if I convert my database to BUFFER mode > > the > > speed is almost doubled. > > > > Now if I convert the database from Unbuffer to Buffer mode and a program > > fails , it will roll back the changes of the specific program ? > > > > What it will happened with the changes of the previous program that it > > might > > not flushed in the disk and are still in the memory (buffers ) ? > > > > Best Regards > > > > Description: Description: CoopLogo1Description: Description: CoopLogo1 > > > > Cooperative Computer Society (S.E.M) Ltd > > > > 1306 Nicosia > > > > P.O.B. 25037 CY > > > > Tel: +357 22 673 901 > > > > Fax: +357 22 672 774 > > > > Achilleas Achilleos > > > > Official A > > > > OS and Databases Management > > > > <mailto:AchilleasAchilleos@semltd.com.cy> AchilleasAchilleos@semltd.com.cy > > > > --Boundary_(ID_b8WbREH6A+/bjNgPOZl87g) > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --00248c70fddd6a50ff04c694f661 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
So, for some transaction critical business, like Bank or Credit Card business..., Buffered database is normally not acceptable, Unless you do not care lose money( when power is off ....) Frank On Mon, Aug 6, 2012 at 4:50 AM, Fernando Nunes <domusonline@gmail.com>wrote: > Jonathan already explained the details. I'll just add another point of view > that I believe to be important. > In a bufferred database your application can issue a COMMIT and receive the > confirmation but nevertheless all this can be in the memory buffer. > So there is the possibility that a "confirmed" COMMIT can be rolled back if > there is an unexpected stop (crash, power or disk failure etc.). > > So, in other words the status perceived by the application may not match > the database status. This can have serious impacts specially if your > application interacts with other systems. > On the other hand, unexpected stops should be rare... In any case it's a > business decision. Can you handle an occasional "inconsistency" between the > database and the application? Note that from the database point of view the > crash recovery assures that it will get to a consistency point... But not > necessarily the one that the application "thinks" it reached. > > Regards. > > On Mon, Aug 6, 2012 at 6:29 AM, Achilleas Achilleos < > AchilleasAchilleos@semltd.com.cy> wrote: > > > Hi to ALL, > > > > Hi Tarn, > > > > The last month I have tried several thing to improve the performance > > between > > the application and the database server (PDQ, FET_BUF_SIZE BUFFER) . > > > > I came up to the conclusion that if I convert my database to BUFFER mode > > the > > speed is almost doubled. > > > > Now if I convert the database from Unbuffer to Buffer mode and a program > > fails , it will roll back the changes of the specific program ? > > > > What it will happened with the changes of the previous program that it > > might > > not flushed in the disk and are still in the memory (buffers ) ? > > > > Best Regards > > > > Description: Description: CoopLogo1Description: Description: CoopLogo1 > > > > Cooperative Computer Society (S.E.M) Ltd > > > > 1306 Nicosia > > > > P.O.B. 25037 CY > > > > Tel: +357 22 673 901 > > > > Fax: +357 22 672 774 > > > > Achilleas Achilleos > > > > Official A > > > > OS and Databases Management > > > > <mailto:AchilleasAchilleos@semltd.com.cy> > AchilleasAchilleos@semltd.com.cy > > > > --Boundary_(ID_b8WbREH6A+/bjNgPOZl87g) > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --00248c70fddd6a50ff04c694f661 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340e1bd6a47604c6b320f4
Correct. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 7, 2012 at 4:49 PM, FRANK <yunyaoqu@gmail.com> wrote: > So, for some transaction critical business, like Bank or Credit Card > business..., Buffered database is normally not acceptable, Unless you > do not care lose money( when power is off ....) > > Frank > > On Mon, Aug 6, 2012 at 4:50 AM, Fernando Nunes <domusonline@gmail.com > >wrote: > > > Jonathan already explained the details. I'll just add another point of > view > > that I believe to be important. > > In a bufferred database your application can issue a COMMIT and receive > the > > confirmation but nevertheless all this can be in the memory buffer. > > So there is the possibility that a "confirmed" COMMIT can be rolled back > if > > there is an unexpected stop (crash, power or disk failure etc.). > > > > So, in other words the status perceived by the application may not match > > the database status. This can have serious impacts specially if your > > application interacts with other systems. > > On the other hand, unexpected stops should be rare... In any case it's a > > business decision. Can you handle an occasional "inconsistency" between > the > > database and the application? Note that from the database point of view > the > > crash recovery assures that it will get to a consistency point... But not > > necessarily the one that the application "thinks" it reached. > > > > Regards. > > > > On Mon, Aug 6, 2012 at 6:29 AM, Achilleas Achilleos < > > AchilleasAchilleos@semltd.com.cy> wrote: > > > > > Hi to ALL, > > > > > > Hi Tarn, > > > > > > The last month I have tried several thing to improve the performance > > > between > > > the application and the database server (PDQ, FET_BUF_SIZE BUFFER) . > > > > > > I came up to the conclusion that if I convert my database to BUFFER > mode > > > the > > > speed is almost doubled. > > > > > > Now if I convert the database from Unbuffer to Buffer mode and a > program > > > fails , it will roll back the changes of the specific program ? > > > > > > What it will happened with the changes of the previous program that it > > > might > > > not flushed in the disk and are still in the memory (buffers ) ? > > > > > > Best Regards > > > > > > Description: Description: CoopLogo1Description: Description: CoopLogo1 > > > > > > Cooperative Computer Society (S.E.M) Ltd > > > > > > 1306 Nicosia > > > > > > P.O.B. 25037 CY > > > > > > Tel: +357 22 673 901 > > > > > > Fax: +357 22 672 774 > > > > > > Achilleas Achilleos > > > > > > Official A > > > > > > OS and Databases Management > > > > > > <mailto:AchilleasAchilleos@semltd.com.cy> > > AchilleasAchilleos@semltd.com.cy > > > > > > --Boundary_(ID_b8WbREH6A+/bjNgPOZl87g) > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --00248c70fddd6a50ff04c694f661 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae9340e1bd6a47604c6b320f4 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae934052581246804c6b36608