On Thu, Dec 17, 2009 at 5:51 PM, Michael Brian - IL <micha...@tusc.com> wrote: > I am pretty new to Dtrace but use the Dtrace Toolkit when trying to > troubleshoot I/O issues > On Oracle. > > I am looking for help on how to do the following: > I am trying to answer whether adding more HBA Cards/ports > would be effective. > > To do this, I need to know the i/o's per second > As well as total bandwidth per second. > > Has anyone done this before?
Sure - and dtrace isn't needed # iostat -xCn 1 | nawk '$0 ~ /device|c.$/' > Does anyone have any other ideas on how to attack this problem? Your DBA's should be able to tell give you more detailed data such as how individual tables and files are performing. Ask for an AWR report (e.g. http://users.telenet.be/oraguy.be/awr2.htm). It could be that there is just a hot LUN due to multiple hot tables being on the same file system. You can see the relative performance of the disks with iostat (get rid of the nawk at the end). Adding more paths to storage can sometimes confuse the storage array, causing it to be less efficient, thereby making your problem worse. Depending on I/O patterns, striping, array type, etc., you could end up making it so that the array no longer recognizes sequential reads (thereby missing out on readahead) or you could end up with more copies of the most active data in the array's cache while slightly less active data is evicted. I've generally found that if you are at the point of thinking you need more paths you are best off making any LUN available only to two HBAs. If you are using veritas file systems, you should be using odm. If you think you are using odm, verify that you really are by using odmstat on some of the database files to be sure you don't see just a bunch of zeros. If you are using UFS or NFS, be sure that you are using directio. If you are using ASM or raw disks you should be good on this front. Typically problems in this area will result in very high %sys (vmstat) while not a lot of real work is getting done. > I have been tuning Oracle for quite some time now, and I am continually > Asked to prove what I tend to know naturally, that the classic 1 HBA, 2 > port card > Isn't cutting it. > > I also have similar discussions on whether I am saturating the BUS on a > particular box. What kind of bus? What speed are the HBA's? If you are on a x8 PCIe connected to a dual port 2 Gb HBA, you are going to max out the HBA's while only at no more than 25% of the PCIe bandwidth. On the other hand, a dual 4 Gb HBA in a PCI or PCI-X slot could certainly be problematic. You may want to take a look at busstat if you feel like you are likely overwhelming a bus. Also, if prstat -mL is showing that you have queries where usr + sys add up to 100 for extended periods of time, you may have problems with queries not having fast enough CPU's. If you see the lat (time waiting to get on a cpu) more than a few percent, you could probably benefit from having more CPU or faster CPU's. Hmmm... I forgot to mention dtrace... One thing I have found with the dtrace toolkit scripts on multi terabyte OLTP databases is that the default values in dtrace and/or the scripts are not large enough to store the amount of data required. As such there are more drops than data points, severely limiting the usefulness of the scripts unless you tune them. I love dtrace, but to date I haven't found it to be more useful for database analysis than a lot of the tools that have existed in Solaris for a decade or so. -- Mike Gerdts http://mgerdts.blogspot.com/ _______________________________________________ dtrace-discuss mailing list dtrace-discuss@opensolaris.org
