Search code examples
mysqldatabase-partitioning

MySQL partitioned table creation with DATA DIRECTORY not moving tables

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

sudo ls -l /data/mysql/cdr3/
total 13530388
-rw-rw---- 1 mysql mysql      13654 Aug 27 10:24 allreads.frm
-rw-rw---- 1 mysql mysql         60 Aug 27 10:24 allreads.par
-rw-rw---- 1 mysql mysql     147456 Aug 27 10:24 allreads#P#p0#SP#p0a.ibd
-rw-rw---- 1 mysql mysql     147456 Aug 27 10:24 allreads#P#p0#SP#pob.ibd
-rw-rw---- 1 mysql mysql  503316480 Aug 27 10:41 allreads#P#P1#SP#p1a.ibd
-rw-rw---- 1 mysql mysql  503316480 Aug 27 10:41 allreads#P#P1#SP#p1b.ibd
-rw-rw---- 1 mysql mysql         65 Aug 14 13:05 db.opt

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)

Solution

  • Version 5.5.38 doesn't support DATA DIRECTORY statements for InnoDB. Reformatted the new disks to RAID so the question is moot.