Re: Very slow unbuffered logging

>We were using buffered loggin, but I think that it's not secure because >that doesn't assure that the commited transactions are saved in case of >energy supply, etc. >So we changed to unbuffered logging and the performance of the machine >as a whole came down. Using tbstat -u we saw users waiting for logical >buffers. >The logical log buffer is configured for 16 pages. >We have Informix 5.10 UC1. >When we changed to buffered logging everything got ok again. >Is this the expected result? Very much so. >Does unbuffered logging make the machine very slow? Very much so. Relatively speaking. >Is buffered logging the best choice? Depends. From a performance point of view, yes. And using buffered logging only becomes "more dangerous" than unbuffered as your buffer gets bigger. What happens is that if you have a crash with buffered logging, you have a greater likelihood of a commit record being in the buffer and not on the disk. With unbuffered logging, this is minimised at the expense of performance. Every time a commit is written to the log buffer, the buffer gets flushed. (Although there is still a miniscule possibility of the server crashing between the request for flushing and the buffer gets flushed, even with unbuffered logging.) With a crash under buffered logging, "all" you will lose are those committed transactions in the buffer, which are rolled back. So your database will still be consistent. I mention this, because often sites will do some form of reconciliation to ensure their data is all there anyway if a server crashes. So it might be worth the risk of having a small data recapture exercise for the enhanced performance. Obviously this may not be possible in all cases, and unbuffered logging is definitely safer. >Another thing, I thought of lowering the logical log buffer to 1 page, >but the database says the minimun value is 16 pages. That's right. And what happens with unbuffered logging is that the whole buffer gets flushed to disk as soon as a commit is issued, no matter how full it is. With buffered logging, it only gets flushed when it's full. YAMMV. HTH. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com