Well, there is the ideal setup, which requires intimate knowledge of the database, lots of disks and extra administration. And then there is the easy setup. Ideally you don't want to have any "hot" disks which will cause contention. This requires you to place your busy tables (read or write) on separate disks so that one drive doesn't get most of the activity. You really only need to do this for your large tables. But as your system grows, it can be difficult to do this manual tuning. I don't recommend this on "smaller" systems and would really only recommend it on systems where the OS allows you to expand file systems or migrate them while they are in use. I know AIX can do this and I'm sure Solaris (with Veritas) and a few others can.
The easy option, and the one I would recommend, is setting up a RAID using something like RAID 0 (striping) which will give you good read and write performance but no safety. Or RAID 1 (mirrored), which will give you good read performance, but poor write performance. RAID 0 and 1 are the least expensive to implement and can be done in software. RAID 5 will give you good read and write as well as safety, but takes a minimum of three disks. If you are using a hardware based RAID, then there typically is no write performance hit for mirrored drives. When striping is used (RAID 0 and 5), your data is split up into small chunks and spread across disks, so it's unlikely that you would get a hot disk. More disks will give you better performance. For optimal RAID setup you want to set the optimal stripe size. If you are dealing with large files, like graphics, you want to setup a large stripe size so that you can take advantage of read ahead settings on the drive/os. For databases, you probably want to have a small stripe size, but not smaller than the size of your largest record size. The optimal setup would be to have a stripe size that is the same size as your database record. In real life this isn't really feasible though. Striping is the easiest way to go and will give you very good performance. The other thing to watch out for is the performance of the card you have the drives hooked up to. Just like you wouldn't want to have a hot drive, you don't want to have a hot card. If you are using SCSI, you really wouldn't want to have any more that 6 drives hooked up to one card. Even if the card could theoretically handled the max output of the combined drives. There is addressing overhead, and protocol overhead, in SCSI that becomes more significant with the more drives you add. If you really want to get technical, the SCSI ID of a drive also has an affect on performance. But this is pretty minimal. If you are doing strictly mirroring, you want to have at least two cards and separate your drives between your cards so that your mirrored drives are on separate cards. That also gives you safety if a card fails. This used to be called duplexing, but I haven't heard that term used for storage in a while. Some SCSI cards do have more than one independent bus, this would also work for mirroring. On Thursday, October 3, 2002, at 04:26 PM, Gary Traffanstedt wrote: > sql, query > > I have a dilemma and maybe you can help. I'm wanting to improve my > disk > performance and I'm wondering if I should go with Raid 10 or if I should > simply mirror the drives so that I have redundancy and then put some of > my > tables on one drive and some on the other. Or the third option is to > put the > tables on one drive and the logs on another drive. Ultimately, what is > going > to give me the best performance? Should I use 3 drives (mirrored so 6 > actual > drives) and put half of the tables on drive 1, half on drive 2, and > logs on > drive 3? > > TIA, > Gary > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <mysql-unsubscribe- > [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
