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.
Best Answer
Is
what you're looking for?
Docs suggest it should be safe to run on existing partitions, meaning it won't repartition the whole table.
...