Friday, 4 November 2011

SSD (EFD) devices for Oracle Redo

I performed one more testing of this subject and found that results are quite interesting. RAID5 7+1 on High-End disk system was used as dedicated device for redo log files (RH Linux 5.5, redo filesystem formatted to ext2, Oracle 11.2.0.3)

Load generator script is simple and does commit after each (!) insert.

3 comments:

Kudos for testing your redo device.While you reached the right conclusion (no need for SSD), the data you show can't be used to reach this conclusion...

First of all, you ran inserts in a tight PL/SQL loop, once with 2 concurrent processes and once with 7 concurrent processes. Are you sure this represents your production workload?

You didn't post traces, but I suspect that most of the CPU time was used for PL/SQL looping. Since the system was CPU starved the entire time, you did not test the disks at all. Note that there is no "log file sync" wait event in either of your tests, so the writing of the redo log was never an issue.

If this is really your workload, then even your high end array is an overkill for the redo logs, but in reality, you just never stressed the disk system, so its hard to tell.

Second thing that looks suspicious is that in the second test, 25% of the wait time is spent waiting for redo space. I'd allocate a larger redo buffer, to get rid of this event and maybe finally see "redo log sync" as a wait event.

Third thing that jumps to mind is the "local write wait". This event is extremely rare to see in production, and shows that significant amount of the waiting time in test2 can be attributed to flushing blocks to disk after truncate. You truncated a large table, with most of the blocks in memory, from several session concurrently. This is definitely not real-world load.

From your iostat results, it looks like the data and redo are on the same disks? In this case, it means that its difficult to tell what you are really seeing in iostat - is it the writes of lgwr or dbwr?

Thanks again for posting detailed results. You definitely got some interesting waits there and is worth more investigation.

Yes, in real production the workload would differ because of reads for ARCH and another application logic. Yes, it is possible to optimize the second test. But the main point is to show, that it is possible to achieve high redo bandwidth and excellent service time for redo even on RAID5…

> Since the system was CPU starved the entire timeCPU usage was ~30..40% for the second test. 16 physical cores in total.

> it looks like the data and redo are on the same disks?No. Redos are on separate RAID5 LUN.

Oracle batches multiple transactions into larger writes. http://www.solarisinternals.com/si/reading/oracle_fsperf.pdf describes in detail how solaris deals with it (or did 10 years ago, anyways). So all those parallel writes are bundled together.