Re: spreading the disk load
Symlinking works fine but keep in mind an important gotcha: if you ever do a table rebuild, mysql completely ignores your symlink and overwrites it with the file (which may even fill up your disk). Have you noticed these table options (from http://dev.mysql.com/doc/mysql/en/create-table.html)? | DATA DIRECTORY = 'absolute path to directory' | INDEX DIRECTORY = 'absolute path to directory' Having the data and index files on seperate drives can certainly speed up IO. I know these options work for MyISAM, but am not sure about InnoDB. Atle - Flying Crocodile Inc, Unix Systems Administrator On Wed, 14 Sep 2005, Chris Kantarjiev wrote: > I'd like to spread the disk arm load across multiple drives. At > the moment, we mostly use MyISAM tables, but we are also > experimenting with InnoDB. > > What's the 'best practice' for doing this? There's no obvious > configuration that lets me designate one directory for index > and another for data - am I meant to do this with symlinks? > How can I do anything like that with InnoDB, which appears > to put everything in one massive file? > > Thanks. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spreading the disk load
On Sep 14, 2005, at 6:16 PM, Chris Kantarjiev wrote: I'd like to spread the disk arm load across multiple drives. At the moment, we mostly use MyISAM tables, but we are also experimenting with InnoDB. What's the 'best practice' for doing this? There's no obvious configuration that lets me designate one directory for index and another for data - am I meant to do this with symlinks? How can I do anything like that with InnoDB, which appears to put everything in one massive file? I think broadly you have these three options: Move and symlink directories. This works with InnoDB if you use file_per_table so it doesn't just use one big file. However, for this to work well you need to know the load across tables. Use a RAID setup. We moved from single SATA drives to 6 spindle FC- ATA RAID 5 and saw a large improvement. This is useful in that it distributes the load across the spindles. Separate logs and tables. Particularly with InnoDB, you have a lot of logging going on. We use only InnoDB, so we have the transaction logs plus binary logs which is a decent amount of I/O. These are also easy to move (and you know they are pretty constant load, unlike some tables which might only see sporadic load), so we put them on a separate RAID 5 array using a separate fibre channel port. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spreading the disk load
Chris Kantarjiev wrote: I'd like to spread the disk arm load across multiple drives. At the moment, we mostly use MyISAM tables, but we are also experimenting with InnoDB. What's the 'best practice' for doing this? There's no obvious configuration that lets me designate one directory for index and another for data - am I meant to do this with symlinks? How can I do anything like that with InnoDB, which appears to put everything in one massive file? Thanks. There's no point putting things in separate files or directories on the same disk. If you want to balance your load, you have to do it across multiple disks. You can do something like put tables you will be often reading from on one disk, and tables you will be often writing to on another disk. Or you can split the data even across your disks by table size. It really depends on the characteristics of your database - you will know better than anyone else what sort of activity your tables have. As for how you actually do this, I'm not sure on the details. There are options in the my.cnf config file to specify paths to put InnoDB files. See the documentation on this - the online docs are quite good for this sort of thing. Sym links are also supported, but I'm not certain if this causes a performance hit or not. I know it's an easy way of doing it though - and maybe the only way with MyISAM tables. I found this: http://dev.mysql.com/doc/mysql/en/disk-issues.html after about 2 minutes of searching the online docs. It doesn't mention anything about performance hits, so maybe it is the way to go. Start at http://dev.mysql.com/doc/mysql/en/index.html to find more info. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
spreading the disk load
I'd like to spread the disk arm load across multiple drives. At the moment, we mostly use MyISAM tables, but we are also experimenting with InnoDB. What's the 'best practice' for doing this? There's no obvious configuration that lets me designate one directory for index and another for data - am I meant to do this with symlinks? How can I do anything like that with InnoDB, which appears to put everything in one massive file? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
