MySQL Forums
Forum List  »  InnoDB

Data Directory is not moving subpartition
Posted by: Michael Muratet
Date: August 28, 2014 02:26PM

I am experimenting with partitioning a very large (100M rows) table across multiple disks to get the space and improve throughput. I installed, formatted and mounted two 1TB SAS disks as /db1 and /db2 into my Dell Poweredge server to hold the tables. My create statement is (omitting some rows for brevity):
DROP TABLE IF EXISTS allreads;
CREATE TABLE allreads (
uuid int(11) NOT NULL AUTO_INCREMENT,
sample_id int(11) NOT NULL,
uid int(11) NOT NULL,
rid char(100) NOT NULL,
seq text(1000) NOT NULL DEFAULT '',
v char(30) NOT NULL DEFAULT '',
d char(30) NOT NULL DEFAULT '',
j char(30) NOT NULL DEFAULT '',
c char(30) NOT NULL DEFAULT '',
pep char(100) NOT NULL DEFAULT '',
PRIMARY KEY (uuid,sample_id),
KEY index_v (v),
KEY index_j (j),
KEY index_cdr3 (pep)
) ENGINE=InnoDB
AUTO_INCREMENT=1
DEFAULT CHARSET=utf8
PARTITION BY RANGE(sample_id)
SUBPARTITION BY HASH(uuid) (
PARTITION p0 VALUES LESS THAN (4500) (
SUBPARTITION p0a
DATA DIRECTORY='/db1/allreads'
INDEX DIRECTORY='/db1/allreads',
SUBPARTITION pob
DATA DIRECTORY='/db1/allreads'
INDEX DIRECTORY='/db1/allreads'
),
PARTITION P1 VALUES LESS THAN (9000) (
SUBPARTITION p1a
DATA DIRECTORY='/db2/allreads'
INDEX DIRECTORY='/db2/allreads',
SUBPARTITION p1b
DATA DIRECTORY='/db2/allreads'
INDEX DIRECTORY='/db2/allreads'
)
)
;

I took the partition bits almost verbatim from the MySQL 5.5 manual.

Nothing gets created in /db1 or /db2, the partitions wind up in the default table space

I see the typos in the partition names, P1 and pob, surely that can't be it. I can provide my.cnf lines if needed.

I can't see anything wrong with the statement relative to the manual. Can anyone tell me what I am doing wrong? Has anyone gotten this feature to work?

I am using 5.5.38-0ubuntu0.14.04.1-log (Ubuntu)

Options: ReplyQuote


Subject
Views
Written By
Posted
Data Directory is not moving subpartition
1441
August 28, 2014 02:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.