http://ooohi.ru
Payday Loans no credit check
 
 
Home MySQL Blogs Portable Tablespace in InnoDB I test it PART2
07
Mag
2012
Portable Tablespace in InnoDB I test it PART2 PDF Stampa E-mail
Scritto da Marco Tusa   

 

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}

Commenti (1)
  • Mattias Jonsson  - EXCHANGE PARTITIONS WITH TABLE
    You can swap a partition with a table with 'ALTER TABLE t_part EXCHANGE PARTITION partX WITH TABLE t'

    That way you have the chance to DISCARD/IMPORT by partition.

    see more at:
    http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html
  • Marco Tusa
    Mattias, thanks I know that, but for admin/recovery purpose that is not a valid solution, given you cannot push back a table into a partition right?
    So the method is one way only.
    What I was looking for is how to automate a possible disaster recovery script, base on the tablespace.

    Thanks for the comment, and let me know if I am wrong about the fact that I cannot push back a table to a partition. I mean right away not doing work around like, create truncate and fill it back.
  • Mattias Jonsson
    EXCHANGE PARTITION is two way :)

    So the data in partX will be the data in t, and the data in t will be the data in partX. As if they change places.

    That way you should be able to both export the data via a table, or import it into a partition via a table.
  • Marco Tusa
    Damn I miss it.. ;o) Will test asap.
    THANKS!
Solo gli utenti registrati possono inviare commenti!

!joomlacomment 4.0 Copyright (C) 2009 Compojoom.com . All rights reserved."

Ultimo aggiornamento Domenica 18 Agosto 2013 17:27
 
 

Who's Online

 35 visitatori online