0

With nearly a billion rows, we have to split up individual partitions across SSDs with about 4 partitions of them in each SSD.

The table's current schema:

CREATE TABLE IF NOT EXISTS `a` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `special_key` varchar(20) NOT NULL DEFAULT '0',
  `data1` varchar(20) NOT NULL,
  `data2` varchar(20) NOT NULL,
  `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`special_key`),
  UNIQUE KEY `special_key` (`special_key`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
PARTITION BY KEY (special_key)
PARTITIONS 12;

If we'd done this while partitioning initially, the command that works looks like this:

ALTER TABLE `a`
partition by key(special_key) PARTITIONS 12  
(
PARTITION p0 ,
PARTITION p1 ,
PARTITION p2 ,
PARTITION p3 DATA DIRECTORY='/media/p1/mysql_table_a',
PARTITION p4 DATA DIRECTORY='/media/p1/mysql_table_a',
PARTITION p5 DATA DIRECTORY='/media/p1/mysql_table_a',
PARTITION p6 DATA DIRECTORY='/media/p1/mysql_table_a',
PARTITION p7 DATA DIRECTORY='/media/p2/mysql_table_a',
PARTITION p8 DATA DIRECTORY='/media/p2/mysql_table_a',
PARTITION p9 DATA DIRECTORY='/media/p2/mysql_table_a',
PARTITION p10 DATA DIRECTORY='/media/p3/mysql_table_a',
PARTITION p11 DATA DIRECTORY='/media/p3/mysql_table_a',
);

But now that we've already partitioned this table, is there a way to get MySQL to 'move' specific partitions without re-creating the whole table?

Copying & Symlinking these tables manually works but it's not the 'right' way and this is reset on any further alters.

2

Is

ALTER TABLE `a` REORGANIZE PARTITION
     p0 INTO (PARTITION p0 DATA DIRECTORY='/media/p666/mysql_table_a');

what you're looking for?

Docs suggest it should be safe to run on existing partitions, meaning it won't repartition the whole table.

ALTER TABLE with ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REBUILD PARTITION, or REORGANIZE PARTITION does not create any temporary tables (except when used with NDB tables); however, these operations can and do create temporary partition files.

...

REORGANIZE operations copy only changed partitions and do not touch unchanged ones.

| improve this answer | |
  • Rephrasing: Does the command simply copy the '.ibd' file or recreate it row-by-row? – kouton Aug 15 '15 at 12:40
  • 1
    I kind of don't understand what difference between the two you expect. (Besides some performance) – Fox Aug 15 '15 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.