今天再論壇看到一個問題關於 lost write 突然想到LGWR 在commit 時是如何保證寫道磁碟的在網上查到一篇文章很好的解釋了這個

Direct I/O and Redo writes.

Here is a subject that has bothered me for some time. When you make a database change and then issue a subsequent COMMIT, how can you guarantee that the change has been recorded and written to disk? I guess the real question is: Are you really sure your transactions are safe?

If we take a look at the Oracle database architecture, we are told that when we issue a commit, the change which has already been recorded in the Redo Log buffer is flushed to disk.

Linux likes to utilize all available memory, so for example if the O/S reports having free, then a good chunk of this will be used for O/S I/O caching. We can verify this with the free command:

free -m
total used free shared buffers cached
Mem: 6022 5983 38 0 26 3736

Notice here that my Linux machine is using over 3GB of the physical 6GB for I/O caching.

Caching at the O/S level is generally a good idea, however when we specifically look at Oracle, this mechanism has the affect of double caching our database data blocks and skewing our disk write time performance statistics. Database block writes (via DBWR) are no longer disk writes, they become cached disk writes.

The follow up question that springs into mind may also be: What about LGWR ? Does this also imply that when a user commits, the redo log entry is written to the Linux I/O cache before being written to disk? If so, surly I can then loose changes, even after they have been posted to disk..... This is something I needed to pursue with Oracle for peace of mind.

According to Oracle, the LGWR process uses an O/S option called "Direct IO". Direct IO instructs Linux to write directly to the disk I/O subsystem. Basically, bypassing the O/S I/O buffering mechanism. As a DBA, this makes me feel better. But we are not there yet.

Further investigations in this area (this time via Redhat) provides the final piece of the jigsaw. Although Direct IO does bypass the O/S cache, it cannot instruct your storage devices to bypass any physical caching that is taking place. In my case, I have direct attached storage with a controller which is caching reads and writes. By default, I also have physical disk read/write caching too! - All of these options are configurable. But, out of the box, in my environment, I have 2 levels of caching for LGWR, and 3 levels forDBWR ( DBWR does not implement DIRECT I/O by default*)

Ultimately, this investigation demonstrates that we DBA's do need to understand the O/S and Hardware that we are using. Secondly, we should not take everything we hear at face value.

I have heard several times (even on DBA training courses) that once a commit is issued, the change is guaranteed to be written to disk. However, if you have not thought about the extra possible levels of software and hardware caching there will be a split second when Oracle thinks it has written your valuable change to disk, but in fact it will be hanging around in a hardware cache - some place between your database server and the physical disk platter , just waiting for that inevitable power cut!

* Incidentally, DIRECT I/O can be enabled for DBWR by using thefilesystemio_options database parameter. This, I will save for another entry.

Direct I/O and Redo writes.

Direct I/O and Redo writes.

相關文章