MySQL Blogs

 

Overview

After testing ... in my previous article (Portable Tablespace in InnoDB I test it!)
I was thinking...
But how much that will really cost?
How long it will take porting the table space?
What about partitions?
Finally (after Sunny told me it is possible) can I import a table space on a different table (with code modification)?
Here is the work I have done today while having some spare time, after a loooong ride on bicycle with the family.

Question 1: how much it will cost?

I have created 3 tables
  • First 10MB data,
  • Second 100 MB data,
  • Third 1GB data.
Not too much but should be enought to identify a possible delta between tables.
Let's go:
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;

 

Discard table space to prepare the tables for the IMPORT
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#
 

 

Re-attach the tablespaces to the tables
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)
 

 

All done and time is increasing a lot with the size of the tablespace.

Question 2: What about partitions?

Create a different directory to store my backup
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/

 

Recreate the fake tale with partitions
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) */
 

 

I would like to perform discard on only ONE partition, but htere is no OPTION to do it,
I have to TRY to discard all of them the partition B.
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

 

But it is not working.
Too bad, I would love to have it!!!

Question 3:  Finally can I import a table space on a different table (with code modification)?

Fist of all I have change the code.
Create a new table, but I will use the old tablespace coming from tbtest_10MB
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)
 

 

Here the files as usual
-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
 

 

And now copy the old tablespace files in the new place
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
 

 

Finally do the real test
1
2
3
4
5
6
7
8
9
10
11
12
13

(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_IAMNEW IMPORT TABLESPACE;

(root@localhost) [test_tablespace1]>check table tbtest_IAMNEW;

+--------------------------------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+--------------------------------+-------+----------+----------+

| test_tablespace1.tbtest_IAMNEW | check | status | OK |

+--------------------------------+-------+----------+----------+

1 row in set (0.05 sec)

 

Done.

Final test do it in a different Schema

Create 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
 

 

I also Patch the schema name
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)
 

 

Done !

Conclusion

The way we can play with tablespace without having to face the previous issue is definitely good.
The time IMPORT the partition is long and it could be really too long when use on huge tablespace.
Almost 9 minutes, for a 1GB tablespace, on a simple machine 4 CPU 4GB RAM, doing nothing else, is really too much.
But I am not complain, never the less for DBAs one of the most common use is related to PARTITIONS management, Backup/recovery and possible data move.
Given that having the PARTITIONs cut out is really nonsense for me.
Finally, also IF I understand that some user can do dangerous things moving table from one ORIGINAL TABLE to another, I see this feature only use from advance DBA.
Given that limiting it, and limiting the flexibility is again none sense for me.
I know we can hack the code in a very easy way, but this is not nice anyhow.
My Shopping list for this feature is the following:
  1. move tablespace from one object (table/schema) to another
  2. having the chance to DISCAR/IMPORT by partition
  3. make the process on IMPORTing the table space faster
Not really too much, not really complicate, but really helpful for DBAs.

{joscommentenable}

Latest conferences

We have 4764 guests and no members online

login

Remember Me