...
h2. Volume Layout considerations
Let's start with the obvious: Many Volume Managers provide a way to concentrate all reads towards one plex (mirror). Instead of reading in a round-robin fashion (which usually is set as default), most workloads are likely to benefit from satisfying reads from the local plex. Consider though that the corresponding disks will have to serve twice the number of read I/Os. If all systems are distributed equally to both locations, this should however have no performance impact in case of consolidated storage: the storage subsystems in both locations should have their share of the total I/O workload!
Regarding Veritas Volume Manager it is quite common to define so-called 'Dirty Region Logs' (DRLs) in order to prevent full mirror resynchs after a host crash. In a metrocluster setup, you should also carefully plan for the location of the DRL (keep it local for 'normal' operation). Another option specific to remote mirrors are so-called DCO-Logs (Data Change Object). These logs avoid full mirror resynchs not only after a host crash (in case DRLs are embedded to the DCO-Logs) but also after a temporary outage of the DWDM links (stale mirrors). Persistent DCO-Logs do track changes of data within a bitmap on disk. These logs have to exist in both locations. However, this additional security comes at it's price: During our tests we figured out, that each application write creates 6 I/O's to disk, 3 to each mirror. Although the DCO Log Area is pretty small, you still have to deal with lots of IOPS. I would recommend not to use DCO-Logs on small volumes with high write activity which is the case with Oracle's online redologs. A full mirror resynch of these small volumes won't hurt performance for a long time therefore omitting DCO-Logs here seems to be no problem at all.
h2. Some theory about Oracle
The Oracle Server uses different processes to perform different sorts of disk I/O. It is important to understand, that not every I/O has a direct impact on application response time. From an application point of view, a commit is a blocking event, the app may not continue to work before the commit has been acknowledged. On the other hand, a change to a data block (SQL Update) is a non blocking event. The change is being applied to the database block within the data buffer of the SGA and won't go to disk as long as no checkpoint is triggered. I'm trying to avoid the words 'asynchronous' and 'synchronous' because they tend to be a great source of confusion in the database space as described [here|http://wikis.sun.com/display/SAPonSun/Demystifying+Oracle+IO], so let's stick with 'blocking' and 'non-blocking'.
Oracle internal statistics reflect this fact by using so-called 'events' which may either be 'idle events' (non-blocking) or 'non-idle events' (blocking).
Now let's look at the most important types of I/O and see which ones are idle resp. non-idle events:
* Physical Reads (single or multiblock, data or index blocks):
It is pretty obvious, that reading a block is a blocking operation, since the app usually can't continue working without actually having the requested data. Since the volume manager's read policy should prefer the local disks, there should be no impact imposed by the long distance SAN (see description above). The corresponding Oracle wait event is called 'db file sequential read' for single block reads resp. 'db file scattered read' for multiblock reads (aka full table scans).
* Direct Path I/O:
Direct Path I/O is somewhat rare in SAP environments. It occurs when an Oracle
I've seen them in APO and BW/SEM environments when Oracle performs on-disk sorts.
Direct path reads don't need to be observed closer, as they benefit from an appropriate volume manager read policy. Direct path writes are non-blocking, as long as the session doesn't require the I/O Queue being completely processed. In addition, direct writes are multiblock I/O's and the latency impact for a fixed amount of data is reduced compared to lots of small I/O's (less roundtrips). Putting it all together, you probably don't have to consider Direct Path I/O when trying to guesstimate the performance impact on your app - at least not for SAP environments.
* Flushing of 'dirty blocks' by database writer process (DBWR):
As mentioned earlier, this is a non-blocking action from the app point of view. At least as long as you don't run into 'checkpoint not complete' situations. However, this usually only happens, if your storage subsystem doesn't provide enough IOPS. Increased latency should by no means become an issue here. Just be careful when using DCO-Logs, as this will increase the IOPS rate significantly.
* Writes to Oracle online redologs after commits:
The Oracle wait event you will see here is 'log file sync'. To be precise: this event also contains the time required for communication between the user session and the log writer process (LGWR), like waking up LGWR, retrieving the blocks to be flushed to the log from the log buffer, waking up the user session after successful flush etc. However, most of this event's wait time is spent to perform the I/O to the redolog. 'Log file sync' is a fully blocking event, as the app has to be sure that it's changes are persisted to the database before being able to continue processing. Therefore you certainly have to keep an eye on 'log file sync'.
h3. Systematic approach to estimate the impact on your application response time
The basic idea during my tests was to look at Oracle statistics
* at different times (e.g. during runtime critical processing, during batch, during online user load)
* for different systems (as not every system behaves the same)
After having spoken to application folks you know where and when to look. Your friend to analyze your existing systems is Oracle AWR or good old statspack reports. Check the Top 10 Wait events and watch out especially for 'log file sync'. If 'log file sync' is not a major contributor to your overall wait time, then - as a first guess - you are probably fine. But let's get back to the calculation:
# determine the number of 'log file sync' waits (find it in section 'Top 5 timed events' or 'events', usually the column next to 'event name')
*Example:*
{noformat}
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 18,767,245 0 141,638 8 25.5
buffer busy waits 1,596,964 22 18,337 11 2.2
db file parallel write 1,158,487 0 17,030 15 1.6
db file scattered read 933,386 0 15,495 17 1.3
log file sync 746,021 15 5,993 8 1.0
{noformat}
746021 is the number you are looking for
\\
# calculate the average commit size:
Look at section 'Instance Activity Stats' for 'redo size' and 'redo writes'. Now divide 'redo size' by 'redo writes' and you've got the average I/O size for LGWR in Bytes. You're curious why the I/O size is important? Well, stay tuned ...
*Example:*
{noformat}
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
redo size 39,353,890,204 5,465,059.1 53,368.7
redo writes 519,561 72.2 0.7
{noformat}
The average I/O size LGWR flushes to disk is 74 KB
\\
# Now you could add the additional latency by multiplying the theoretical latency of your DWDM network with the number of 'log file sync' waits. But there's one more thing to consider:
\\
!FC_64KB.png!
\\
As you can see, there's a roundtrip for every 32 KB of data. In case you have eliminated the two way handshake (e.g. by FC Write Acceleration), you'll have 3 roundtrips to perform a 74 KB I/O. Without FC-WA, you will end up with 4 roundtrips! That's the reason why we calculated the average LGWR I/O size. Makes sense?
*Example:*
Assuming we are talking about the 50 km distance, without having FC-WA, we can calculate 4 * 0,5ms = 2 ms.
Now multiply that with the number of 'log file sync' events, you get
746021 * 2 msec = 1492 sec (Christopher Columbus would have liked that result ;-) )
\\
# Now the real question: 'Do I have to care about the additional wait time?'
Oracle AWR reports provide a section 'Time Model statistics' which lists the total time being spent in database user calls. In case of statspack, the way I deal with it is to take the top event (from the 'Top 5 Timed Events'), and take it's wait time and it's 'total elapsed time' percentage to calculate the total wait time.
*Example:*
{noformat}
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 18,767,245 141,638 44.92
{noformat}
In this case just calculate:
141638 sec / 0,4492 = 315.311 sec
\\
Within the example above, we calculated an additional database wait time of 1492 sec compared to a total wait time of > 315000 secs. This means an increase of database response time of less than half a percent. That's probably acceptable for most customers. Especially considering the fact, that DB response time is just one part of SAP response time. You may check the proportions using transaction ST03(N). In our example above, you probaly will stop here and don't do the SAP math.
But be aware, that there are databases having 'log file sync' as the top wait event. Especially if this is due to a high number of commits with relatively low average wait time. If that's the case, the high latency may become a real problem for your system.
h3. Drawbacks
The method described above will only give you an idea with respect to average system behaviour. There might be transactions / jobs not fitting that average. Update intensive processes with frequent commits are certainly such candidates. If these processes are also runtime-sensitive, it might be a better idea to trace this transaction / job specifically in order to get an idea about it's commit-frequency.
|