Systems Engineering and RDBMS

Archive for March 1st, 2007

Custom Search Engine

Posted by decipherinfosys on March 1, 2007

One of the common issues that all of us consultants (and company employees) have faced time and again is that when we search for information on Google or Yahoo or other search engines, the plethora of information that one gets makes it very difficult to go through all those links. And finding the right URL that provides some meaningful help takes some time. With that in mind, we started doing some research and using Google’s Co-op, created our own customized search engine which gets it’s information from selected premier sites where the information provided is to the point. You can search for items in these categories:

  • Oracle
  • SQL Server
  • DB2 LUW
  • Windows
  • Linux
  • Unix
  • Performance Tuning
  • VMWare
  • Hardware related
  • .Net Development
  • Disaster Recovery
  • Exchange
  • Data Modeling
  • Networking

The search engine link is listed on the widget in the right hand bar as well can be accessed here.

Posted in Decipher News | Leave a Comment »

RAID Levels and IOs per disk

Posted by decipherinfosys on March 1, 2007

Here is the calculation of the I/O’s per disk for different RAID levels:

  • Raid 0 — I/Os per disk = (reads + writes) / number of disks
  • Raid 1 — I/Os per disk = [reads + (2 * writes)] / 2
  • Raid 5 — I/Os per disk = [reads + (4 * writes)] / number of disks
  • Raid 10 — I/Os per disk = [reads + (2 * writes)] / number of disks

So, as you can see from above RAID 5 incurs a higher overhead for writes as compared to RAID 10 which is why for requirements of a highly performant and transactional RDBMS, it is rarely recommended to have RAID 5 for the logs.

Also, how can you make use of this information to see whether you are running into a I/O bottleneck or not – because, what is presented above is just theory and to prove a well known point about RAID 5 vs RAID 10. Well, if we take the Windows OS for example and use perfmon (performance monitor) utility to measure these counters:

PhysicalDisk Object: Disk Reads/Sec., Disk Writes/Sec., Avg. Disk Queue Length (you can descriptions on these by hitting the explain button in perfmon when selecting the counters). Now, assume that you have put your log files on RAID 1 system which has 2 physical disks on it. Since logs are written to sequentially, RAID 1 is a good choice. Suppose, the counters measurements over a period of time yield you these values:

Disk Reads/sec = 90
Disk Writes/sec = 80
Avg. Disk Queue Length = 5

In that case, you are encountering (90 + (2 * 80))/2 = 125 I/Os per disk and your disk queue length = 5/2 = 2.5 which indicates a border line I/O bottleneck (any value over 2 is a cause of concern and should be evaluated along with the I/Os seen per disk).

From a RDBMS perspective, all three leading RDBMS: Oracle, SQL Server and DB2 LUW provide with system level information to look into the wait events for further diagnosis. You can look at the pending I/O requests, the latch waits etc. but do not (of course) provide any visibility into the physical disks experiencing the problem. In a future whitepaper on our site, we will cover in detail the different wait events in the three leading RDBMS and how they can help you to troubleshoot performance issues.

Posted in Hardware | Leave a Comment »

New “How-To” articles posted

Posted by decipherinfosys on March 1, 2007

We have posted three new “How-To” articles on our site:

  1. How can I view the historical changes made to a table using Flashback versions query?
  2. How can I recover a table which is dropped by mistake using the flashback table clause?
  3. How can I return a record set from a function in Oracle, MS SQL Server and DB2?

You can access them at this link.

Posted in Decipher News | Leave a Comment »