|Is it possible to move a specific mysql table to specific hard drive?|
mysql table hard drive location
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.
I could also just setup multiple databases, but would like to use one mysql program.
database (thismasterdb): host table 1 [Drive F]
database (thisslavedb): host table 2 [Drive E]
Anyone know if this is possible?
Bump... Anyone? Is my only solution to have multiple machines with mysql on them?
It is possible to use a partitioned table that will split tables across harddrives/partitions.
I haven't used it operationally though.
|brotherhood of LAN|
You can also specify different directories for specific files, i.e. one for indexes, one for data files.
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.
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?
Back to reading (still trying to understand how to set a specific table to a certain drive)
|brotherhood of LAN|
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:
|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. |
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.
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
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).
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|
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)
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].