Exchange MyISAM partitions in MySQL 5.1
Posted by:
Danny Wang
Date: March 21, 2011 12:43PM
We need to insert several million rows to partitioned MyIsam table, but the inserting will make the server pretty slow. so I am thinking if I can do to build those partitions somewhere else then just overwrite the mysql partition file to make data available.
Here is my test,
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (a)
(PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (10) ENGINE = MyISAM) */
CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL,
`b` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (a)
(PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (10) ENGINE = MyISAM) */
mysql> insert into t1 values(6,'a');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t1 values(7,'b');
Query OK, 1 row affected (0.00 sec)
So now here is what I have,
mysql> select * from t1;
+------+------+
| a | b |
+------+------+
| 6 | a |
| 7 | b |
+------+------+
mysql> select * from t2;
Empty set (0.00 sec)
Then I will swap files for partition p2,
root@ubuntu:/var/lib/mysql/test2# pwd
/var/lib/mysql/test2
root@ubuntu:/var/lib/mysql/test2# mv t1#P#p2.MYD t1#P#p2.MYD.old
root@ubuntu:/var/lib/mysql/test2# mv t1#P#p2.MYI t1#P#p2.MYI.old
root@ubuntu:/var/lib/mysql/test2# mv t2#P#p2.MYD t1#P#p2.MYD
root@ubuntu:/var/lib/mysql/test2# mv t2#P#p2.MYI t1#P#p2.MYI
root@ubuntu:/var/lib/mysql/test2# mv t1#P#p2.MYI.old t2#P#p2.MYI
root@ubuntu:/var/lib/mysql/test2# mv t1#P#p2.MYD.old t2#P#p2.MYD
On mysql,
mysql> flush tables t1,t2;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
Empty set (0.00 sec)
mysql> select * from t2;
+------+------+
| a | b |
+------+------+
| 6 | a |
| 7 | b |
+------+------+
2 rows in set (0.00 sec)
1 row in set (0.00 sec)
mysql> alter table t1 check partition p2;
+----------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+-------+----------+----------+
| test2.t1 | check | status | OK |
+----------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> alter table t2 check partition p2;
+----------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+-------+----------+----------+
| test2.t2 | check | status | OK |
+----------+-------+----------+----------+
1 row in set (0.00 sec)
Looks it works, and there is no performance impact on server and minimum downtime.If this way is working, we also can achieve the "truncate partition" feature is not available in 5.1. of course, it only works on myISAM table.
but I want to check with you if there is potential issue in this approach, I don't want to later find out that table is corrupted.
Thanks,