Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 128,862
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Evaluating Storage Performance!

Posted by FatDBA on March 10, 2015

Being a DBA means you need to posses outstanding knowledge of DBA Subjects as obvious, but nowadays knowledge on storage/network/OS is a big surplus. This helps a modern day DBA to troubleshoot/rectify and helps to avoid million of hours wasted to identify DB issues when in fact there isn’t any.

This time i tried to discuss steps which one can take when there are waits likes – log file sync, db file async io submit, log file parallel write, control file parallel write and many of the parallel and sequential write observed in system.

Quick Fast Disk Test Results (DD Command):
———————————————
[oracle@dixitdb111 datafiles]$ time sh -c “dd if=/dev/zero of=dd-test-file bs=20k count=1000000 && sync”
1000000+0 records in
1000000+0 records out
8192000000 bytes (8.2 GB) copied, 26.4959 seconds, 309 MB/s

real 0m44.318s
user 0m0.278s
sys 0m19.410s
You have new mail in /var/spool/mail/oracle

[oracle@dixitdb111 datafiles]$ ls -ltrh
-rw-r–r– 1 oracle oinstall 7.7G Jan 30 11:59 dd-test-file

ORION
————-

ORION (ORacle IO Numbers) imitates the type of I/O performed by Oracle databases, which makes possible for you to measure I/O performance for storage arrangements without actually installing Oracle. This is now included in the “$ORACLE_HOME/bin” directory of Database/Grid installations.

There are many of the options available for the orion to run.
e.g. oltp, olap and many more.
Below pasted is a beautiful explanation by Alex Gorbachev (ACED, IOUG, OAKTABLE Member & Renowned Blogger)
Link: http://www.uyoug.org.uy/eventos2013/OTNLAD2013-Benchmarking-Oracle-IO-Performance-with-ORION-by-Alex-Gorbachev.pdf

[oracle@dixitdb111 bin]$ ./orion -run oltp
ORION: ORacle IO Numbers — Version 11.2.0.3.0
orion_20150203_0800
Calibration will take approximately 2 minutes.
Using a large value for -cache_size may take longer.

This will result in few .csv and text files with IO results with some beautiful charts/graphs and tabular records for the runtime.

Reference:
————–
_hist.csv
Histogram of I/O latencies.
_iops.csv
Performance results of
small I/Os in IOPS.
_lat.csv
Latency of small I/Os in microseconds.
_mbps.csv
Performance results of
large I/Os in MBPS.
_summary.txt
Summary of the input parameters, along with the minimum small I/O latency (in secs),
the maximum MBPS, and the maximum IOPS observed.
_trace.txt
Extended, unprocessed output

I/O calibration is one of those magical option. This feature enables user to assess the performance of the storage subsystem, and determine whether I/O performance problems are caused by the database or the storage subsystem. Unlike other external I/O calibration tools that issue I/Os sequentially, the I/O calibration feature of Oracle Database issues I/Os randomly using Oracle datafiles to access the storage media, producing results that more closely match the actual performance of the database.

SELECT d.name,
i.asynch_io
FROM v$datafile d,
v$iostat_file i
WHERE d.file# = i.file_no
AND i.filetype_name = ‘Data File’;

NAME ASYNCH_IO
—————————————————————– ———
/dbmnt1/dixitdb/datafiles/system01.dbf ASYNC_ON
/dbmnt2/dixitdb/datafiles/undotbs1_01.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/sysaux01.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/users01.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem01.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem02.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem03.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem04.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem05.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem06.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem07.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem08.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem09.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem10.dbf ASYNC_ON
/dbmnt2/dixitdb/datafiles/unicode1tbs01.dbf ASYNC_ON
/dbmnt2/dixitdb/datafiles/unicode2atbs01.dbf ASYNC_ON
/dbmnt2/dixitdb/datafiles/r11testtbs.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem11.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem12.dbf ASYNC_ON
/dbmnt2/dixitdb/datafiles/artest.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem13.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem14.dbf ASYNC_ON
/dbmnt1/dixitdb/datafiles/dbsystem15.dbf ASYNC_ON
/dbmnt3/dixitdb/datafiles/dbsystem16.dbf ASYNC_ON

* below query is resource intensive. Load/CPU spikes are expected during the run.

SET SERVEROUTPUT ON
DECLARE
l_latency PLS_INTEGER;
l_iops PLS_INTEGER;
l_mbps PLS_INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 1,
max_latency => 20,
max_iops => l_iops,
max_mbps => l_mbps,
actual_latency => l_latency);

DBMS_OUTPUT.put_line(‘Max IOPS = ‘ || l_iops);
DBMS_OUTPUT.put_line(‘Max MBPS = ‘ || l_mbps);
DBMS_OUTPUT.put_line(‘Latency = ‘ || l_latency);
END;
/

Max IOPS = 610
Max MBPS = 67
Latency = 19

==================
Calibration runs can be monitored using the V$IO_CALIBRATION_STATUS view.

SET LINESIZE 100
COLUMN start_time FORMAT A20
COLUMN end_time FORMAT A20

SELECT TO_CHAR(start_time, ‘DD-MON-YYY HH24:MI:SS’) AS start_time,
TO_CHAR(end_time, ‘DD-MON-YYY HH24:MI:SS’) AS end_time,
max_iops,
max_mbps,
max_pmbps,
latency,
num_physical_disks AS disks
FROM dba_rsrc_io_calibrate;

START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY DISKS
——————– ——————– ———- ———- ———- ———- ———-
30-JAN-015 09:49:10 30-JAN-015 09:53:14 610 67 27 19 1

Thanks
Prashant Dixit

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

 
%d bloggers like this: