Portable Tablespace in InnoDB I test it PART2
Overview
Question 1: how much it will cost?
- First 10MB data,
- Second 100 MB data,
- Third 1GB data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SHOW CREATE TABLE tbtest_XX\G (root@localhost) [test_tablespace1]>SHOW CREATE TABLE tbtest_10MB\G *************************** 1. row *************************** TABLE: tbtest_10MB CREATE TABLE: CREATE TABLE `tbtest_10MB` ( `a` int(11) NOT NULL DEFAULT '0', `b` char(3) DEFAULT 'AAA', `dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row IN SET (0.00 sec) (root@localhost) [test_tablespace1]>SHOW CREATE TABLE tbtest_100MB\G *************************** 1. row *************************** TABLE: tbtest_100MB CREATE TABLE: CREATE TABLE `tbtest_100MB` ( `a` int(11) NOT NULL DEFAULT '0', `b` char(3) DEFAULT 'AAA', `dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row IN SET (0.00 sec) (root@localhost) [test_tablespace1]>SHOW CREATE TABLE tbtest_1GB\G *************************** 1. row *************************** TABLE: tbtest_1GB CREATE TABLE: CREATE TABLE `tbtest_1GB` ( `a` int(11) NOT NULL DEFAULT '0', `b` char(3) DEFAULT 'AAA', `dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row IN SET (0.00 sec) |
Now let us take the tablespace snapshot
1 2 3 4 |
(root@localhost) [test_tablespace1]>FLUSH TABLE tbtest_10MB, tbtest_100MB,tbtest_1GB WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) (root@localhost) [test_tablespace1]> |
drwxr-xr-x 2 root root 4096 2012-04-27 15:31 ./ drwxrwxr-- 30 mysql mysql 4096 2012-04-27 14:59 ../ -rw-r----- 1 root root 442 2012-04-27 15:26 tbtest3.cfg -rw-r----- 1 root root 360448 2012-04-27 15:26 tbtest3.ibd -rw-r----- 1 root root 98304 2012-04-27 15:31 tbtest3.ibt -rw-r----- 1 root root 440 2012-04-27 14:59 tbtest.cfg -rw-r----- 1 root root 8606 2012-04-27 14:59 tbtest.frm -rw-r----- 1 root root 360448 2012-04-27 14:59 tbtest.ibd -rw-r----- 1 root root 98304 2012-04-27 15:17 tbtest.ibt root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp tbtest_1*.* /home/mysql/backup root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll /home/mysql/backup total 1276880 drwxr-xr-x 2 root root 4096 2012-05-05 18:59 ./ drwxrwxr-- 30 mysql mysql 4096 2012-04-27 14:59 ../ -rw-r----- 1 root root 476 2012-05-05 18:59 tbtest_100MB.cfg -rw-r----- 1 root root 8606 2012-05-05 18:59 tbtest_100MB.frm -rw-r----- 1 root root 92274688 2012-05-05 18:59 tbtest_100MB.ibd -rw-r----- 1 root root 475 2012-05-05 18:59 tbtest_10MB.cfg -rw-r----- 1 root root 8606 2012-05-05 18:59 tbtest_10MB.frm -rw-r----- 1 root root 10485760 2012-05-05 18:59 tbtest_10MB.ibd -rw-r----- 1 root root 474 2012-05-05 18:59 tbtest_1GB.cfg -rw-r----- 1 root root 8606 2012-05-05 18:59 tbtest_1GB.frm -rw-r----- 1 root root 1203765248 2012-05-05 18:59 tbtest_1GB.ibd -rw-r----- 1 root root 442 2012-04-27 15:26 tbtest3.cfg -rw-r----- 1 root root 360448 2012-04-27 15:26 tbtest3.ibd -rw-r----- 1 root root 98304 2012-04-27 15:31 tbtest3.ibt -rw-r----- 1 root root 440 2012-04-27 14:59 tbtest.cfg -rw-r----- 1 root root 8606 2012-04-27 14:59 tbtest.frm -rw-r----- 1 root root 360448 2012-04-27 14:59 tbtest.ibd -rw-r----- 1 root root 98304 2012-04-27 15:17 tbtest.ibt root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#
Do the drop
1 2 3 4 5 |
(root@localhost) [test_tablespace1]>drop TABLE tbtest_10MB; DROP TABLE tbtest_100MB;drop TABLE tbtest_1GB; Query OK, 0 rows affected (0.22 sec) Query OK, 0 rows affected (0.14 sec) Query OK, 0 rows affected (0.26 sec) |
Will create the fake tables
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
(root@localhost) [test_tablespace1]> CREATE TABLE `tbtest_10MB` ( `a` int(11) NOT NULL DEFAULT '0', `b` char(3) DEFAULT 'AAA', `dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `tbtest_100MB` ( `a` int(11) NOT NULL DEFAULT '0', `b` char(3) DEFAULT 'AAA', `dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `tbtest_1GB` ( `a` int(11) NOT NULL DEFAULT '0', `b` char(3) DEFAULT 'AAA', `dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
1 2 3 4 5 6 7 8 |
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_10MB DISCARD TABLESPACE; Query OK, 0 rows affected (0.07 sec) (root@localhost) [test_tablespace1]>ALTER TABLE tbtest_100MB DISCARD TABLESPACE; Query OK, 0 rows affected (0.03 sec) (root@localhost) [test_tablespace1]>ALTER TABLE tbtest_1GB DISCARD TABLESPACE; Query OK, 0 rows affected (0.07 sec) |
Move back all the good tablespaces from backup
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp /home/mysql/backup/tbtest_1*.* . root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll total 1329248 ddrwx------ 2 mysql mysql 4096 2012-05-05 19:06 ./ drwxr-xr-x 4 mysql mysql 4096 2012-05-03 15:40 ../ -rw-rw---- 1 mysql mysql 65 2012-04-27 14:28 db.opt -rw-r----- 1 mysql mysql 476 2012-05-05 19:06 tbtest_100MB.cfg -rw-r----- 1 mysql mysql 8606 2012-05-05 19:06 tbtest_100MB.frm -rw-r----- 1 mysql mysql 92274688 2012-05-05 19:06 tbtest_100MB.ibd -rw-r----- 1 mysql mysql 475 2012-05-05 19:06 tbtest_10MB.cfg -rw-r----- 1 mysql mysql 8606 2012-05-05 19:06 tbtest_10MB.frm -rw-r----- 1 mysql mysql 10485760 2012-05-05 19:06 tbtest_10MB.ibd -rw-r----- 1 mysql mysql 474 2012-05-05 19:06 tbtest_1GB.cfg -rw-r----- 1 mysql mysql 8606 2012-05-05 19:06 tbtest_1GB.frm -rw-r----- 1 mysql mysql 1203765248 2012-05-05 19:06 tbtest_1GB.ibd -rw-rw---- 1 mysql mysql 8636 2012-05-04 12:11 tbtest_part.frm -rw-rw---- 1 mysql mysql 44 2012-05-04 12:11 tbtest_part.par -rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partA.ibd -rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partB.ibd -rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partC.ibd -rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partD.ibd root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#
1 2 3 4 5 6 7 |
root@localhost) [test_tablespace1]>ALTER TABLE tbtest_10MB IMPORT TABLESPACE; Query OK, 0 rows affected (3.11 sec) (root@localhost) [test_tablespace1]>ALTER TABLE tbtest_100MB IMPORT TABLESPACE; Query OK, 0 rows affected (10.17 sec) (root@localhost) [test_tablespace1]>ALTER TABLE tbtest_1GB IMPORT TABLESPACE; Query OK, 0 rows affected (8 min 53.98 sec) |
Question 2: What about partitions?
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# mkdir /home/mysql/backup1 root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp tbtest_*.* /home/mysql/backup1/ root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# rm -f tbtest_1*.* root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll total 53292 drwx------ 2 mysql mysql 4096 2012-05-05 19:05 ./ drwxr-xr-x 4 mysql mysql 4096 2012-05-03 15:40 ../ -rw-rw---- 1 mysql mysql 65 2012-04-27 14:28 db.opt -rw-rw---- 1 mysql mysql 8636 2012-05-04 12:11 tbtest_part.frm -rw-rw---- 1 mysql mysql 44 2012-05-04 12:11 tbtest_part.par -rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partA.ibd -rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partB.ibd -rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partC.ibd -rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partD.ibd root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#
Preparing the table with partitions
1 2 3 |
(root@localhost) [test_tablespace1]>FLUSH TABLE tbtest_part WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) |
Copy files
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp tbtest_part*.* /home/mysql/backup1/
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE `tbtest_part` ( `a` int(11) NOT NULL DEFAULT '0', `b` char(3) DEFAULT 'AAA', `dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `part` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (part) (PARTITION partA VALUES IN (1) ENGINE = InnoDB, PARTITION partB VALUES IN (2) ENGINE = InnoDB, PARTITION partC VALUES IN (3) ENGINE = InnoDB, PARTITION partD VALUES IN (4) ENGINE = InnoDB) */ |
1 2 3 4 5 6 7 |
-rw-rw---- 1 mysql mysql 8636 2012-05-04 12:11 tbtest_part.frm -rw-rw---- 1 mysql mysql 44 2012-05-04 12:11 tbtest_part.par -rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partA.ibd -rw-rw---- 1 mysql mysql 98304 2012-05-05 20:36 tbtest_part#P#partB.ibd <---------------- -rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partC.ibd -rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partD.ibd |
1 2 3 |
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_part DISCARD TABLESPACE; ERROR 1031 (HY000): TABLE storage engine FOR 'tbtest_part' doesn't have this option <------------ oops is not possible |
Question 3: Finally can I import a table space on a different table (with code modification)?
1 2 3 4 5 |
(root@localhost) [test_tablespace1]>CREATE TABLE `tbtest_IAMNEW` ( `a` int(11) NOT NULL DEFAULT '0', `b` char(3)
DEFAULT 'AAA', `dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.24 sec) (root@localhost) [test_tablespace1]>ALTER TABLE tbtest_IAMNEW DISCARD TABLESPACE; Query OK, 0 rows affected (0.08 sec) |
-rw-rw---- 1 mysql mysql 8606 2012-05-06 18:28 tbtest_IAMNEW.frm -rw-rw---- 1 mysql mysql 98304 2012-05-06 18:28 tbtest_IAMNEW.ibt
root@000033:/home/mysql/instances/my56testm/DATA/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.frm ./tbtest_IAMNEW.frm root@000033:/home/mysql/instances/my56testm/DATA/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.ibd ./tbtest_IAMNEW.ibd root@000033:/home/mysql/instances/my56testm/DATA/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.cfg ./tbtest_IAMNEW.cfg root@000033:/home/mysql/instances/my56testm/DATA/test_tablespace1# chown mysql:mysql * root@000033:/home/mysql/instances/my56testm/DATA/test_tablespace1# ll total 1326476 drwx------ 2 mysql mysql 4096 2012-05-06 18:30 ./ drwxr-xr-x 4 mysql mysql 4096 2012-05-06 18:25 ../ -rw-rw---- 1 mysql mysql 65 2012-04-27 14:28 db.opt -rw-r----- 1 mysql mysql 476 2012-05-05 19:06 tbtest_100MB.cfg -rw-r----- 1 mysql mysql 8606 2012-05-05 19:06 tbtest_100MB.frm -rw-r----- 1 mysql mysql 92274688 2012-05-05 19:17 tbtest_100MB.ibd -rw-r----- 1 mysql mysql 475 2012-05-06 18:09 tbtest_10MB.cfg -rw-rw---- 1 mysql mysql 8606 2012-05-06 18:09 tbtest_10MB.frm -rw-r----- 1 mysql mysql 10485760 2012-05-06 18:10 tbtest_10MB.ibd -rw-rw---- 1 mysql mysql 98304 2012-05-06 18:09 tbtest_10MB.ibt -rw-r----- 1 mysql mysql 474 2012-05-05 19:06 tbtest_1GB.cfg -rw-r----- 1 mysql mysql 8606 2012-05-05 19:06 tbtest_1GB.frm -rw-r----- 1 mysql mysql 1203765248 2012-05-05 19:26 tbtest_1GB.ibd -rw-r----- 1 mysql mysql 475 2012-05-06 18:30 tbtest_IAMNEW.cfg -rw-rw---- 1 mysql mysql 8606 2012-05-06 18:30 tbtest_IAMNEW.frm -rw-r----- 1 mysql mysql 10485760 2012-05-06 18:30 tbtest_IAMNEW.ibd
1 2 3 4 5 6 7 8 9 10 11 12 13 |
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_IAMNEW IMPORT TABLESPACE; +--------------------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+-------+----------+----------+ | test_tablespace1.tbtest_IAMNEW | check | status | OK | +--------------------------------+-------+----------+----------+
|
Final test do it in a different Schema
1 2 3 4 5 6 7 8 9 10 11 |
(root@localhost) [test_tablespace1]>create schema test_tablespace2; Query OK, 1 row affected (0.05 sec) (root@localhost) [test_tablespace1]>use test_tablespace2 DATABASE changed CREATE new TABLE (root@localhost) [test_tablespace2]>CREATE TABLE `tbtest_IAMNEW` ( `a` int(11) NOT NULL DEFAULT '0', `b` char(3) DEFAULT 'AAA', `dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.22 sec) Do the process, we know it quite well now. (root@localhost) [test_tablespace2]>ALTER TABLE tbtest_IAMNEW DISCARD TABLESPACE; Query OK, 0 rows affected (0.03 sec) |
Let test the real situation on FS
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.frm ../test_tablespace2/tbtest_IAMNEW.frm root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.cfg ../test_tablespace2/tbtest_IAMNEW.cfg root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.ibd ../test_tablespace2/tbtest_IAMNEW.ibd root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cd ../test_tablespace2 root@000033:/home/mysql/instances/my56testm/data/test_tablespace2# ll total 10364 drwx------ 2 mysql mysql 4096 2012-05-06 18:47 ./ drwxr-xr-x 5 mysql mysql 4096 2012-05-06 18:46 ../ -rw-rw---- 1 mysql mysql 65 2012-05-06 18:46 db.opt -rw-r----- 1 mysql mysql 475 2012-05-06 18:47 tbtest_IAMNEW.cfg -rw-rw---- 1 mysql mysql 8606 2012-05-06 18:47 tbtest_IAMNEW.frm -rw-r----- 1 mysql mysql 10485760 2012-05-06 18:47 tbtest_IAMNEW.ibd
1 2 |
root@000033:/home/mysql/instances/my56testm/data/test_tablespace2# vi tbtest_IAMNEW.cfg |
1 2 3 4 5 6 7 8 9 10 11 |
(root@localhost) [test_tablespace2]>ALTER TABLE tbtest_IAMNEW IMPORT TABLESPACE; Query OK, 0 rows affected (3.03 sec) (root@localhost) [test_tablespace2]>show TABLE STATUS; (root@localhost) [test_tablespace2]>check TABLE tbtest_IAMNEW; +--------------------------------+-------+----------+----------+ | TABLE | Op | Msg_type | Msg_text | +--------------------------------+-------+----------+----------+ | test_tablespace2.tbtest_IAMNEW | CHECK | STATUS | OK | +--------------------------------+-------+----------+----------+ 1 row IN SET (0.06 sec) |
Conclusion
- move tablespace from one object (table/schema) to another
- having the chance to DISCAR/IMPORT by partition
- make the process on IMPORTing the table space faster
{joscommentenable}