Welcome to WebmasterWorld Guest from 54.205.96.97

Forum Moderators: open

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

mysql table hard drive location

   
6:11 am on Oct 31, 2009 (gmt 0)

5+ Year Member



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

6:55 am on Oct 31, 2009 (gmt 0)

5+ Year Member



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

9:30 pm on Nov 1, 2009 (gmt 0)

5+ Year Member



Bump... Anyone? Is my only solution to have multiple machines with mysql on them?
11:29 pm on Nov 1, 2009 (gmt 0)

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



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

11:32 pm on Nov 1, 2009 (gmt 0)

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



2nd partitioning

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

6:04 am on Nov 2, 2009 (gmt 0)

5+ Year Member



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.
6:15 am on Nov 2, 2009 (gmt 0)

5+ Year Member



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)

6:30 am on Nov 2, 2009 (gmt 0)

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



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.

6:39 am on Nov 2, 2009 (gmt 0)

5+ Year Member



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).

6:40 am on Nov 2, 2009 (gmt 0)

5+ Year Member



oh and for those on *nix servers (which I miss), I think it's just as simple for you guys as well.
7:49 am on Nov 2, 2009 (gmt 0)

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



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].

 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month