List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:August 2 2006 4:14pm
Subject:Fwd: Spreading Database across multiple disks
View as plain text  
Tripp, YMMV, but I'm a firm believer that keeping things as simple as
possible pays big dividends.

I believe the advice to spread out IO tasks among different disks is
good advice, when dealing with direct-attached disks you deal with
directly (i.e. not part of a RAID).  The setup and maintenance is not
inconsiderable, however, and performance will lag behind an array no
matter what you do.

If you're building a high-performance server, you may well want to
consider moving away from individual disks and using hardware RAID for
performance and redundancy.

Once you're on such a device, you need to spend less/no time worrying
about directing data to individual disks, because all your data is
spread among mutiple disk spindles and can also use RAM caching in the
device.  You also likely have the option of dedicating a group of
disks within the device for logging, while still utilizing all that
hardware RAID has to offer.

At my last job, we used an Apple XServe RAID to host our MySQL data,
and performance was astounding.  We also used Sun 3310 arrays to host
our Sybase data, and performance was likewise astounding, though setup
and administration was more complex than the Apple (you had more
options too though).  I say "astounding" considering what these
advanced little boxes cost, which is just several thousand dollars.

The Apple box has been certified on Mac OS X, Windows, Linux and
Netware, so it's pretty versatile.  There are also PCI card solutions
that let you create a hardware RAID within your server box of course -
ATA, SATA, and SCSI.  Lots of options to choose from so you can select
what fits your needs and budget.

If you're wanting to build a high-availability setup, two server boxes
with one Apple RAID, each using half the array, could be a very good
setup, insulating you from hardware and software disasters.  Expense
is relative - I'd estimate you'd be looking at $25K for such a setup,
easily more depending on your server boxes.  You can do the same thing
with many SCSI arrays (Sun 3310/3320s included) with some advanced
setup work.

Hope this helps,

On 8/1/06, Tripp Bishop <dyne_erg@stripped> wrote:
> Howdy all,
> We're looking at building a new database server and
> I'm looking into strategies for optimizing disk i/o.
> Bit of background. We will be running a single
> database on this box under MySQL 5.0.15. All of the
> tables are INNODB. We have about 130 tables in the db.
> I've read that it's a good idea to have the innodb log
> files written out to a seperate physical drive so that
> those operations don't bog down the rest of the
> database disk I/O operations. Configuring INNODB to do
> that looks straightforward.
> Then there's the data. I know that I can create
> multiple shared table spaces and locate them on
> seperate disks but that doesn't seem to give me
> control over where individual tables' data are written
> on the disk array. Is there a way using
> innodb_file_per_table to control where the individual
> .idb files are located in the disk array? Do I have to
> use symbolic links to trick innodb or is there a
> cleaner way?
> Also, I've been told that innodb table spaces never
> shrink. Is this true or is there a way to periodically
> cleanup the idb files to reclaim unused space?
> Cheers,
> Tripp
Fwd: Spreading Database across multiple disksDan Buettner2 Aug