MySQL Forums
Forum List  »  Partitioning

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,

Options: ReplyQuote


Subject
Views
Written By
Posted
Exchange MyISAM partitions in MySQL 5.1
3066
March 21, 2011 12:43PM


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.