March 4, 2011 Leave a comment
I’ve mentioned elsewhere that I’m in the early stages of an upgrade – 220.127.116.11 on 32 bit Solaris to 18.104.22.168 on 64 bit Linux.
(So, not exactly an illustration in implementing change incrementally. :) )
Initial run of application code was slow, very slow. Across the board. Some plans had changed. But average IO seems much slower than the benchmarks using for comparison.
Statspack shows that a single block read takes on average 4 ms in 22.214.171.124 production.
For 11gR2 on the new kit (which isn’t destined for production but which is at least meant to be used to provide a ballpark comparison), AWR shows average single block read times of more like 14ms.
But we’re not really comparing apples with apples. So many things are different, it’s more like apples and oranges – massive jump in Oracle version, new kit, different OS, different configurations, direct IO, etc, etc.
I have been advocating simplicity when approaching problems, especially with so many changes – divide & conquer, distill & simplify.
For example, let’s say that IO looks slow.
For now, let’s not use the application batch to test kernel settings, ODM, etc
(Don’t get me wrong – it will be important to use a workload absolutely representative of the the application, I just don’t think it might not be now).
Let’s benchmark using, for example, Oracle ORIONto see if we can achieve IO rates that someone, somewhere must know we need – they’ve purchased the kit after all – and then use that to test changes, use that to compare new environments against a benchmark, etc.
(Then, when we’re happy with simpler things, we can start adding back in and piecing things together again)
Anyway, today I came across an alternative to Orion that I can’t recall being previously aware of.
I like – it seems pretty neat to me.
Yet another feature which seems to have sneaked under my radar somehow (along with real time SQL monitoring – a quick blog post mention which I’ve half written but not published yet).
What does this CALIBRATE_IO do?
It’s like an easier way to do the ORION thing.
From the CALIBRATE_IO.PDF available via Metalink Doc Id 727062.1 “Configuring and using Calibrate I/O”:
When Calibrate I/O is invoked it will generate I/O intensive read-only random I/O (db_block_size)
and large-block (1MByte) sequential I/O workloads. Unlike various external I/O calibration tools, this
tool uses the Oracle code stack and runs in the database, issuing I/O against blocks stored in the
database. The results, therefore, much more closely match the actual database performance.
Once the workload execution is completed, a summary of the results is provided.
I ran it like this (DBA for env is away, SysAdmin sort of suggested 15 as NUM_DISKS but when I ran with 15 initially, it ran forever and it looked like the waits weren’t instrumented properly.)
SQL> declare 2 l_latency integer; 3 l_iops integer; 4 l_mbps integer; 5 begin 6 dbms_resource_manager.calibrate_io 7 (5,10,l_iops,l_mbps,l_latency); 8 dbms_output.put_line ('I/O Ops/sec = '||l_iops); 9 dbms_output.put_line ('Actual Latency = '||l_latency); 10 dbms_output.put_line ('MB/sec = '||l_mbps); 11 end; 12 /
And got the following output:
I/O Ops/sec = 52 Actual Latency = 18 MB/sec = 93
You can also get this information by querying DBA_RSRC_IO_CALIBRATE:
SQL> select * from dba_rsrc_io_calibrate; START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS --------------------------------- --------------------------------- ---------- ---------- ---------- 04-MAR-11 126.96.36.1990306 PM 04-MAR-11 01.08.30.898526 PM 52 93 7 18 5
To be honest, I have no feel for some of these numbers – no frame of reference.
But it’s not great, right?
Some other articles on CALIBRATE_IO:
Jose Valerio – DBMS_RESOURCE_MANAGER.CALIBRATE_IO 11gR2
Tim Hall – Measuring Storage Performance for Oracle Systems
Arup Nanda – Resource Manager IO Callibration in 11g