homepage Welcome to WebmasterWorld Guest from 54.237.98.229
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Is it possible to move a specific mysql table to specific hard drive?
mysql table hard drive location
miketheman

5+ Year Member



 
Msg#: 4016666 posted 6:11 am on Oct 31, 2009 (gmt 0)

Hi everyone. Is it possible to move a specific table to a specific hard drive?

EXAMPLE / GOAL:
I have a database (thismasterdb).
There are 5 tables (1,2,3,4,5).
I'd like to move table "1" to Drive F; table "2" to Drive E; ect.

Is this possible, any input, suggestions, and comments welcome and greatly appreciated.

Thank you,
Mike

 

miketheman

5+ Year Member



 
Msg#: 4016666 posted 6:55 am on Oct 31, 2009 (gmt 0)

I could also just setup multiple databases, but would like to use one mysql program.

NEW EXAMPLE:
database (thismasterdb): host table 1 [Drive F]
database (thisslavedb): host table 2 [Drive E]

Anyone know if this is possible?

Thank u

miketheman

5+ Year Member



 
Msg#: 4016666 posted 9:30 pm on Nov 1, 2009 (gmt 0)

Bump... Anyone? Is my only solution to have multiple machines with mysql on them?

jmccormac

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



 
Msg#: 4016666 posted 11:29 pm on Nov 1, 2009 (gmt 0)

It is possible to use a partitioned table that will split tables across harddrives/partitions.
[dev.mysql.com...]

I haven't used it operationally though.

Regards...jmcc

brotherhood of LAN

WebmasterWorld Administrator brotherhood_of_lan us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 4016666 posted 11:32 pm on Nov 1, 2009 (gmt 0)

2nd partitioning

You can also specify different directories for specific files, i.e. one for indexes, one for data files.

miketheman

5+ Year Member



 
Msg#: 4016666 posted 6:04 am on Nov 2, 2009 (gmt 0)

THANK YOU GUYS! I think this is exactly what I was looking for...I'm going to study this part of the manual and post my results.

miketheman

5+ Year Member



 
Msg#: 4016666 posted 6:15 am on Nov 2, 2009 (gmt 0)

Just a note...while I'm studing this...anyone who stumbles on this thread. From what I understand so far, partitioning parts of mysql improves performance?

[dev.mysql.com...]

Back to reading (still trying to understand how to set a specific table to a certain drive)

brotherhood of LAN

WebmasterWorld Administrator brotherhood_of_lan us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 4016666 posted 6:30 am on Nov 2, 2009 (gmt 0)

Partitioning can indeed improve performance, namely, for queries that reference a particular field that the table is partitioned by. e.g. dates. If you stored log files in MySQL, you could partition by month and query for date ranges within a month would query only that partition rather than the whole table, great if the table is huge.

In regards to specifying where the MySQL data and index files go:

[dev.mysql.com...]

Data and indexes for each partition can be assigned to a specific directory using the DATA DIRECTORY and INDEX DIRECTORY options for the PARTITION clause of the CREATE TABLE statement used to create the partitioned table.
Note

Prior to MySQL 5.1.18, these options were permitted even when the NO_DIR_IN_CREATE server SQL mode was in effect. (Bug#24633)

The DATA DIRECTORY and INDEX DIRECTORY options have no effect when defining partitions for tables using the InnoDB storage engine.

DATA DIRECTORY and INDEX DIRECTORY are not supported for individual partitions or subpartitions on Windows. Beginning with MySQL 5.1.24, these options are ignored on Windows, except that a warning is generated. (Bug#30459)

Perhaps for your purpose, you would want to have just one partition, and use the DATA DIRECTORY and INDEX DIRECTORY options to specify where you want the data to be.

miketheman

5+ Year Member



 
Msg#: 4016666 posted 6:39 am on Nov 2, 2009 (gmt 0)

WHOOOOO!

You guys pointed me the right way....I can finally separate each database to a specific drive.

This article helped out: [dev.mysql.com...]

I'm not to sure if performance increases or not BUT for those out there like me.
My issue was I had a lot of records (the millions) I was able to simple cut and paste (yeah Cut And FREAKING paste people), my database to a different hard drive, then just create a text file with only the drive and folder and save it as .sym

SUPER SIMPLE

If anyone can let me (and most likely the users to stumble on this) how this effects performance I would greatly appreciate it. BUT MAN THIS IS AWESOME!

Next...off to Fry's to buy some 2TB hard drives (lol).

Thank you brotherhood of LAN and jmccormac for your support and pointing me in the right direction....you guys may have just saved me a humongous headache (and money).

miketheman

5+ Year Member



 
Msg#: 4016666 posted 6:40 am on Nov 2, 2009 (gmt 0)

oh and for those on *nix servers (which I miss), I think it's just as simple for you guys as well.

brotherhood of LAN

WebmasterWorld Administrator brotherhood_of_lan us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 4016666 posted 7:49 am on Nov 2, 2009 (gmt 0)

Mysql's implementation of partitioning is still in its infancy but does provide an number of ways of improving performance. I'm using it just now but struggling to get to grips with how it performs using JOIN's.

Googling/Binging 'mysql partitioning performance' brings up the MySQL forum and other well referenced popular forums.

Two immediately apparent performance gains

1) Partitioning is basically 'sub tables within tables'. Well tuned queries with partitioning only need to reference a fraction of the table, thus the queries are faster.

2) Spreading the database files across hard disks can speed up access (something to do with concurrency, I'm no guru)

http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitions.html

Example: Searching One Partition at a time

With MyISAM tables, each statement causes a lock. Dora The DBA discovers that some "maintenance update" statements harm concurrency with a huge table. So she arranges that the statements will only process 1,000,000 rows at a time, breaking up by partition. This takes advantage of the pruning feature.

It's well worth reading the partitioning section of the MySQL manual once or twice and thinking how it could be useful to your app in the future.

There are some performance penalties, particularly if using lots of partitions, be sure to read about its restrictions and limitations [dev.mysql.com].

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved