Hi Folks,

I dont know if it is appropriate to pose this question here, but I figured someone here might know the answer :)

Is it possible with InnoDB to store the data portion of a database on one disk, but store the indexes of that data base on another disk?
The reason I am doing this is that I am adding something in the order of 10 - 20 million rows a day to a table (smallish row thankfully), but I need to sort them rather a lot, and hence need indexes. I figured it would be quicker to have the indexes sit on one disk and the data on the other sort sorting purposes. Current test case is at 70 MM rows, 15.48G data length, and 12.14 G index ... splitting the disk would give me a "twice as fast" write as it can write to each disk simultaneously ..

IS this possible, or am in dreaming in LaLa land ? I did look on the mysql reference site, and all it seemed I could do was set the data directory ...

thanks in advance for any help/pointers/answers

best regards,

Richard

Hi Richard,

If you need lots of indexes there is a simpler solution that doesn't require new hardware. High-speed indexed inserts is a major strength of the TokuDB storage engine, available as a free download from tokutek.com. (The storage engine is free for databases under 50G and free for development.)

I am prejudiced (I work at Tokutek), but I also believe honestly that TokuDB is the right solution for you. Anyway, it's free to try it, and I think you'll really like it. (At least our other customers do.)

If you have difficulty installing or using TokuDB or if you have any questions about it, I can help.

Barry Perlman
barry at tokutek.com

Hi Rich,

You don't have to store all your data on one disk, you can store your
InnoDB tables on multiple disks.
For example you can use the follow settings:
innodb_data_file_path =
/disk1/ibdata1:500M;/disk2/ibdata2:300MB;/disk3/ibdata3:100MB:autoextend

Regards,

Jacob Nikom

Hi Rich and Jacob,

InnoDB is limited by disk seek time, not by disk bandwidth. InnoDB needs to seek to the correct place in the index and then seek again to the correct place in the primary in order to insert an indexed row.

You can, however, improve your search time by using a covering or clustering index.
(See http://tokutek.com/20... .)

The TokuDB alternative is not a B-Tree and does not need to perform a separate disk seek for each index in an inserted row. It is limited by disk bandwidth, not by disk seek time. That's an essential difference that allows TokuDB to perform high speed indexed inserts, even in very large tables. This is demonstrated by the iiBench benchmark available free at tokudb.com. Try it, you'll see.

Note, TokuDB's high-speed indexed inserts allow support of many covering or clustering indexes in real time (as the rows are inserted).

Best of luck,
barry at tokutek dot com