The Boston MySQL Meetup Group Message Board › InnoDB help needed

InnoDB help needed

Richard
user 7589000
Boston, MA
Post #: 2
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
A former member
Post #: 1
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
Jacob N.
user 2421302
Needham Heights, MA
Post #: 39
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
A former member
Post #: 2
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

Powered by mvnForum

Our Sponsors

  • Technocation, Inc.

    Video camera, host video files, meetup fees. They are non-profit.

  • Oracle, Inc.

    Oracle develops the MySQL database core and offers support and training.

  • Akiban Technologies

    Has a new table grouping technology as a denormalization alternative.

  • Tokutek

    TokuDB is a drop-in replacement for InnoDB that scales MySQL®.

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy