Re: Doubt on logical log - revisited

I just checked the docs for 5.20, unlike the 9.3 doc (which is not specific) it says that logical log buffer flushes for UNBUFFERED databases are full page flushes often wasting the balance of a page when the transaction is small. So there IS indeed waste for UNBUFFERED logging (and in later releases for SET LOG sessions as well) for smaller transactions especially on 4K machines like AIX & WinXX. And from the 7.30 Administrator's Guide: p16-9: "When the database server flushes the buffer, only the used pages are written to disk. Used pages include pages that are only partially full, however, so some space is wasted. For this reason, the logical-log files on disk fill up faster than if all the databases on the same database server used buffered logging." (Found almost identical language in the 9.3 manual p11-11 finally.) And: p16-11: "However, with unbuffered logging, the database server flushes the logical-log buffer to disk more frequently, and the buffer contains many more partially full pages, so it fills the logical log faster than buffered logging does." The unstated implication here is that 7.30 also flushed full pages when UNBUFFERED logging is used. Art S. Kagel

The important concept here is pages vs. buffers. We never write less than a page, so when we flush a logical log buffer, we don't write any unused pages in a logical log buffer, however we will write a partially full page, meaning there will possibly be _some_ white (empty) space written to the logical log. One other point to remember is that the logical logs are global to the instance, while the logging mode is specific to a database. Even if you set all your user databases to use BUFFERED logging, sysutils uses UNBUFFERED logging. If you're using OnBar for backups, the engine writes transactions for logical log backups (and of course for dbspace backups), and using the default behavior (backup the log as it completes), on a very busy OLTP system you're going to see some logical log buffers flushed before they fill (depends on how busy the system is/how fast the logical logs fill up). And, of course, checkpoints. Actually, there are a few other times, too - but we can save that for later. HTH... Mike =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Mike Lowe Certified Consulting Education Specialist Data Management Solutions IBM Software Group Tel: 303-773-5216 Tie Line: 565-5216 e-Fax: 413-674-2415 mlowe0@us.ibm.com WWW: ibm.com/software/data I just checked the docs for 5.20, unlike the 9.3 doc (which is not specific) it says that logical log buffer flushes for UNBUFFERED databases are full page flushes often wasting the balance of a page when the transaction is small. So there IS indeed waste for UNBUFFERED logging (and in later releases for SET LOG sessions as well) for smaller transactions especially on 4K machines like AIX & WinXX. And from the 7.30 Administrator's Guide: p16-9: "When the database server flushes the buffer, only the used pages are written to disk. Used pages include pages that are only partially full, however, so some space is wasted. For this reason, the logical-log files on disk fill up faster than if all the databases on the same database server used buffered logging." (Found almost identical language in the 9.3 manual p11-11 finally.) And: p16-11: "However, with unbuffered logging, the database server flushes the logical-log buffer to disk more frequently, and the buffer contains many more partially full pages, so it fills the logical log faster than buffered logging does." The unstated implication here is that 7.30 also flushed full pages when UNBUFFERED logging is used. Art S. Kagel

Just a note about the empty space in the logical logs. We will allow a logical log record to span multiple pages. Usually the last entry on a logical log page will be continued on the next page. However, if there is not enough space on the page to contain the log record's header, we will skip to the next log page to start the log record. We don't split the log record headers. Therefor, even with buffered logging, there may be a bit of empty space. Yes --- if unbuffered logging is used, then the remainder of the log page (i.e. the portion past the commit) will not be used. We don't write partial pages. So - yes - When using unbuffered logging, more logical log space may be required. Michael Lowe/Denver/IBM@I To: ids@iiug.org BMUS cc: Sent by: Subject: Re: Doubt on logical log - revisited [430] forum.subscriber@ iiug.org 02/19/2003 01:19 PM The important concept here is pages vs. buffers. We never write less than a page, so when we flush a logical log buffer, we don't write any unused pages in a logical log buffer, however we will write a partially full page, meaning there will possibly be _some_ white (empty) space written to the logical log. One other point to remember is that the logical logs are global to the instance, while the logging mode is specific to a database. Even if you set all your user databases to use BUFFERED logging, sysutils uses UNBUFFERED logging. If you're using OnBar for backups, the engine writes transactions for logical log backups (and of course for dbspace backups), and using the default behavior (backup the log as it completes), on a very busy OLTP system you're going to see some logical log buffers flushed before they fill (depends on how busy the system is/how fast the logical logs fill up). And, of course, checkpoints. Actually, there are a few other times, too - but we can save that for later. HTH... Mike =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Mike Lowe Certified Consulting Education Specialist Data Management Solutions IBM Software Group Tel: 303-773-5216 Tie Line: 565-5216 e-Fax: 413-674-2415 mlowe0@us.ibm.com WWW: ibm.com/software/data I just checked the docs for 5.20, unlike the 9.3 doc (which is not specific) it says that logical log buffer flushes for UNBUFFERED databases are full page flushes often wasting the balance of a page when the transaction is small. So there IS indeed waste for UNBUFFERED logging (and in later releases for SET LOG sessions as well) for smaller transactions especially on 4K machines like AIX & WinXX. And from the 7.30 Administrator's Guide: p16-9: "When the database server flushes the buffer, only the used pages are written to disk. Used pages include pages that are only partially full, however, so some space is wasted. For this reason, the logical-log files on disk fill up faster than if all the databases on the same database server used buffered logging." (Found almost identical language in the 9.3 manual p11-11 finally.) And: p16-11: "However, with unbuffered logging, the database server flushes the logical-log buffer to disk more frequently, and the buffer contains many more partially full pages, so it fills the logical log faster than buffered logging does." The unstated implication here is that 7.30 also flushed full pages when UNBUFFERED logging is used. Art S. Kagel