in reply to OT: What Hardware is important for large I/O bound processes

The problem with SANs in many organisations is that you just get allocated space in the larger scheme of things. Depending on the architecture, it can be hard to specify exactly where your data goes, so you don't have the chance to optimise its layout. You want to spread your data across as many disks as possible in order to parallelise the I/O, so I'd go for local drives because you have comlete control over them

The sad thing is that disks are getting bigger, but it's better to have lots of smaller ones rather than a few (or even one) big one(s). You'd either stripe at the hardware level or lay your data out manually. Genterally you'll need to know your data hotspots - which you or your DBA can find out through the v$ tables, or via Enterprise Manager.

The other thing to consider is that Oracle has partitioned tables, so you can spread you can spread the hotspots around the disks, cos in most cases, there are a couple of tables that bear most of the I/O brunt

Finally, most I/O bottlenecks can be improved by closely examing your database schema, tuning your SQL and finally look at your hardware. Hardware considerations come last. Unlike coding, premature optimsation through db design is absolutely essential, as it's almost impossible to refactor the design once it's gone live. Also use the v$ tables to identify your worst performing SQL I/O-wise - they can often be tuned through judicious choice of indexes, optimizer hints etc.

Finally, you don't need a super-grunty server, just as many disks as you can afford!

  • Comment on Re: OT: What Hardware is important for large I/O bound processes

Replies are listed 'Best First'.
Re^2: OT: What Hardware is important for large I/O bound processes
by mugwumpjism (Hermit) on Jul 29, 2005 at 00:48 UTC
    Finally, you don't need a super-grunty server, just as many disks as you can afford!

    Amen to that. This would be why most big iron systems only ship with a very small number of surprisingly lowly clocked CPUs. I heard of one system managing a 0.5TB *active* database (ie, not just a warehouse) with only 3 processors clocked at 150Mhz.

    Of course, you would end up with a lot of system time without a decent IO controller. So, best to stick with the tried and tested solutions - which means high-end SCSI or FC-AL.

    And whatever you do, don't use any RAID level other than raid 1, or maybe 1+0. 

    $h=$ENV{HOME};my@q=split/\n\n/,`cat $h/.quotes`;$s="$h/."
."signature";$t=`cat $s`;print$t,"\n",$q[rand($#q)],"\n";

    [download]
[reply]
[d/l]
Re^2: OT: What Hardware is important for large I/O bound processes
by Anonymous Monk on Jul 28, 2005 at 19:53 UTC
    I agree completely with this; the hardware part is the final piecec to the puzzle.

    We've tunined the SQL and other processes as much as possible. The performance we're getting matches our collective experience. It still impresses me how changes to the SQL can cut processes from days to a few minutes.

    We're not finding more places to tune unless we partition the tables, which will be an interesting experiment. Your comment about the SANs in organizations struck a cord with me.

    The standard process at the company is to take a SAN and format it to work like a single disk or LUN. The IS group claims their's no benefit to keeping things in individual disks since the SAN's cache will handle it. I've long had doubts on this. Do the v$ tables show the hotspots even on a single SAN disk?

[reply]
      Do the v$ tables show the hotspots even on a single SAN disk?

      Not really. Oracle has no idea about the disk abstration behind the scenes, and that is the problem - how do you match an Oracle datafile to the disks in the SAN?

      One of my mates had a huge SAN performance issue at a large site in the UK. They had all the SAN vendor's leading experts working on the problem (even flew in the guru from the US), and everyone was scratching their heads. As a test they set the system up with a single Linux-based PC with dedicated disks. Under load tests it flew. Now I'm not saying that SANs are no good, but they add a huge level of complexity, and you need very sharp in how you architect your solution.

[reply]

In Section Seekers of Perl Wisdom