Mysql – partitions defined in thesql

MySQLpartitioning

I have a large table that I want to spread across multiple disks/LUNs. It will contain blobs and I want to have it separated out from the other data.

How do I go from new disks (lets call them sdb1 and sdc1, the OS, etc. is on sdba1) attached to a (virtual) Linux database server to putting the parts of the database, really one table spread across a couple dedicated devices.

Have looked at the Partition information but its unclear how I change the location of the partitions so everything just doesn't end up in the same data directory.

Best Answer

There is a CREATE TABLE syntax where you can declare the data folder and index folder under the partition_definition of a partition_option:

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES 
            {LESS THAN {(expr | value_list) | MAXVALUE} 
            | 
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir'] <<<<<---------
        [INDEX DIRECTORY [=] 'index_dir'] <<<<<---------
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

CAVEATS

  • I know it works for MyISAM because I wrote about it in September 2011 : Dynamically changeable database. For an already existing MyISAM table, it can done from the OS by moving the .MYD and .MYI to another disk and setting up two symlinks. I wrote about that in May 2012 : Can I have one table be an "alias", or "symlink", to another?. When done correctly, SHOW CREATE TABLE will display DATA DIRECTORY and INDEX DIRECTORY and their respective folders.

  • I personally implemented this in production for a client when I did the following

    • Converted mysql.general_log from CSV to MyISAM
    • Moved /var/lib/mysql/mysql/general_log.MYD to a large 3TB disk
    • Moved /var/lib/mysql/mysql/general_log.MYI to a large 3TB disk
    • Setup /var/lib/mysql/mysql/general_log.MYD as a symlink to the real .MYD
    • Setup /var/lib/mysql/mysql/general_log.MYI as a symlink to the real .MYI
    • enabled the general log
    • Watched the client's general log growth to 1.1TB
    • I wrote up how to set this up back in June 2011 : Mail alert from MySQL when databases are edited (under Option 3 : You Could Monitor the general log)

  • Using hard links, it can be done to MyISAM tables in Windows, although the effort to do so may not be worth it. I wrote about that back in January 2012 : Can I specify a different disk for a specific MySQL table?

  • I do not believe [DATA DIRECTORY [=] 'data_dir'] and [INDEX DIRECTORY [=] 'index_dir'] were implemented for InnoDB. Even if the it were implemented for InnoDB, I would adamently advised against it and I wrote about why you should not back in August 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?

Your Original Question

MySQL 5.5 Only : With regard to your question in the heading, Where are partitions defined in mysql, you can run this query to locate all tables that have partitions:

select count(1) partition_count,table_schema,table_name
from information_schema.partitions
group by table_schema,table_name
having count(1) > 1;

Here is a sample:

mysql> select count(1) partition_count,table_schema,table_name
    -> from information_schema.partitions
    -> group by table_schema,table_name
    -> having count(1) > 1;
+-----------------+--------------+-------------------------+
| partition_count | table_schema | table_name              |
+-----------------+--------------+-------------------------+
|              31 | clickit      | t_keyword_conversion_ga |
|               2 | part         | myoldpart               |
|               3 | test         | messages                |
|             256 | test         | ngram_key               |
|               2 | test         | rc1                     |
+-----------------+--------------+-------------------------+
5 rows in set (1.60 sec)

mysql>

You can get the complete definition by running SHOW CREATE TABLE db.tbl\G from any table from the result of that query.

Related Question