Welcome to WebmasterWorld Guest from 184.108.40.206
Forum Moderators: open
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.
Back to reading (still trying to understand how to set a specific table to a certain drive)
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).
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].