After reading how inadequate Doug was feeling over his IO subsystem, I decided to see how quick mine was…not that we’re getting into a “mine is better than yours” game, but rather to see how mine stacks up against Doug’s, bearing in mind his is a 5 disk stripe natively attached to his machine (I’m assuming) and mine is a logical disk attached to a VMWare machine…although admittedly, the PC underneath this logical disk is running, motherboard based, RAID striping, over two physical SATA disks…I just figured it would be interesting to compare.
Obviously, any experiment that goes flawlessly according to a preconceived plan is:
1. Boring
2. Less educational
3. Not normally one I’ve done – mine always have problems it seems!
I ran the calibration on my VMWare based OpenSuse 10 linux with Oracle 11g and it immediately came up with a problem:
SQL> @io SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 lat INTEGER; 3 iops INTEGER; 4 mbps INTEGER; 5 BEGIN 6 -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat); 7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat); 8 9 DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops); 10 DBMS_OUTPUT.PUT_LINE ('latency = ' || lat); 11 dbms_output.put_line('max_mbps = ' || mbps); 12 end; 13 / DECLARE * ERROR at line 1: ORA-56708: Could not find any datafiles with asynchronous i/o capability ORA-06512: at "SYS.DBMS_RMIN", line 453 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1153 ORA-06512: at line 7
Of course, consulting the manual led me to run this query:
SELECT name, asynch_io FROM v$datafile f,v$iostat_file i WHERE f.file# = i.file_no AND filetype_name = 'Data File' /
which gave:
You can take Kamagra tablets either with meals or without meals anytime before the estimated sexual activity. downtownsault.org order viagra online No one wishes generic viagra online http://downtownsault.org/downtown/nightlife/ to get stuck in this situation. The second most prominent reason why men choose to suffer in silence because now you can buy sildenafil citrate you can visit our sildenafil from canada website genericpillshop.com. viagra online in canada This course we offer is one of the best courses for students.
SQL> / NAME ASYNCH_IO -------------------------------------------------- --------- /home/oracle/oradata/su11/system01.dbf ASYNC_OFF /home/oracle/oradata/su11/sysaux01.dbf ASYNC_OFF /home/oracle/oradata/su11/undotbs01.dbf ASYNC_OFF /home/oracle/oradata/su11/users01.dbf ASYNC_OFF /home/oracle/oradata/su11/example01.dbf ASYNC_OFF
…or in other words no asynchronous IO available – as the error message had said.
After altering the filesystemio_options parameter to “set_all” and bouncing the instance, a second run of the calibration process seemed to work fine…
SQL> @io SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 lat INTEGER; 3 iops INTEGER; 4 mbps INTEGER; 5 BEGIN 6 -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat); 7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat); 8 9 DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops); 10 DBMS_OUTPUT.PUT_LINE ('latency = ' || lat); 11 dbms_output.put_line('max_mbps = ' || mbps); 12 end; 13 / max_iops = 72 latency = 13 max_mbps = 26 PL/SQL procedure successfully completed.
So,my figures are considerably lower than those Doug achieved:
max_iops = 112 latency = 8 max_mbps = 32
…but not too bad I guess considering the fact that mine is a VM and the hardware I’m running is more humble…no seriously, size does not matter!
no seriously, size does not matter!
Oh, I think you know it does 😉
Nice to see some even poorer figures, though! (But not much poorer)
It did occur to me that it would be useful to see a survey of different results.
I’d run it at work but
a) It’s not available on AIX
b) I’m worried about what numbers might come out of the other side!
😉
I’d run it at work too but it’s not available on HP-UX…I’m worried about the numbers I’d see too…but that’s the point…I’d love to be able to run this to see if our systems are “reasonable” or not.
I’ve created a poll on the same place Kevin used the other day for his RAC poll:
Oracle 11g CALIBRATE_IO Results
Not sure if it takes a day or two to appear…once I know it’s there I’ll publicise it further.
😉
I finally got around to it
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Nov 23 10:14:49 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @calibrate_io
max_iops = 166
latency = 853
max_mbps = 345
PL/SQL procedure successfully completed.
SQL>
D’ya know…by the look of those figures, I’m guessing I can’t afford your system Kevin!
Cheers
Jeff