A Tale of 8T (Transportable Tablespaces Vs Mysqldump)

1 . A Tale of 8T Transportable Tablespaces Vs Mysqldump Kristofer Grahn Verisure Innovation 

2 .whoami? Kristofer Grahn (kristofer.grahn@verisure.com) • Verisure (Sweden) • Senior Systems Specialist - But mostly Dba :) - Mysql - Cassandra 2 

3 .

4 .Datawarehouse setup • Used for BI + Troubleshooting • 45 shards = Channels and counting (+10 next week) • Challenges • Physical backups can't be used to merge several instances • TB sized databases and `mysqldump`, not efficient • Load of data must be fast, or replication will never catch up. • Poor catchup when channels > No of Cores • For 5.6 • Partitioned tables not supported for `IMPORT TABLESPACE`. 4 

5 .Mysqldump • Export / Import to .sql files • Need to lock data on source server ! • --single-transaction • --master-data (for replication) • Both Source and Destination parse the Sql • Flexible • Will solve DDL AutoMagically • But Slooooow (And heavy on the instance) • Example • 186Gb • Dump : 80m • Prepare/Export : NA • Import : 802m • Import no replication : 652m 5 

6 .Dump and Load using mysqldump • Dump the schema using mysqldump • `--single-transaction –master-data=2 --triggers --routines` • Restore • `mysql < dump.sql` • Wait, Wait, Wait • Tip • pv, will give a nice processbar.. 

7 .Transportable tablespaces • Export / Import .ibd files between Instances • But xtrabackup ´–export´ is easier • Import takes time • But main limit is ´cp´ bandwith • Import only read’s .ibd metadata fast for big files • Example • 186Gb • Dump : 18m • Prepare/Export : 5m • UnStream : 34m • Import : 628m • Import No Replication : 162m 7 

8 .Dump and Load using TTS • Dump the data using xtrabackup • `--export --prepare` • Dump the schema and/or table using mysqldump • `--no-data --triggers --routines` • Restore the DDL • `mysql < ddl.sql` • Load the data • `discard tablespace` • - `cp` • - `import tablespace` 

9 .Tip’s and tricks for TTS • Import and replication is not friends • Congestion ? • Metadata lock ? • ´STOP SLAVE´ while running import helps • But keep track of Binlog retention • ´ REMOVE PARTITIONING´ is slow • Try running only once if many partitions 

10 .Dump and Load Partitions • 5.6 • Not supported, but • Import each partition as a separate table • Add to table using `EXCHANGE PARTITION` • 5.7 • Supported ! • Discard • Cp • Import 

11 .Dump and Load Partitions in 5.6 • Create table on destination instance • ´Show create table´ • Get list of partitions • ´SELECT partition_name FROM information_schema.PARTITIONS where table_schema=inTableSchema and table_name=inTableName;´ • For each partition in (part1 part2) • ´CREATE TABLE inTableSchema.inTableName_part1 LIKE inTableSchema.inTableName;´ • ´ALTER TABLE inTableSchema.inTableName_part1 REMOVE PARTITIONING;´ • ´ALTER TABLE inTableSchema.inTableName_part1 DISCARD TABLESPACE;´ • ´cp part1.ibd -> inTableName_part1.ibd´ • ´ALTER TABLE inTableSchema.inTableName_part IMPORT TABLESPACE;´ • ´ALTER TABLE inTableSchema.inTableName EXCHANGE PARTITION part1 WITH TABLE inTableSchema.inTableName_part1;´ 

12 .Row format misery Diffrent default innodb row_format in Versions (Partitions created over time) • 5.5 : COMPACT • 5.6 : COMPACT • 5.7 : DYNAMIC • 8.0 : DYNAMIC Howto Check : ´select @@innodb_default_row_format;´ +-----------------------------+ | @@innodb_default_row_format | +-----------------------------+ | dynamic | +-----------------------------+ Error on import for missmatch : Starting load of tablespace : ERROR 1808 (HY000) at line 1: Schema mismatch (Table flags don't match, server table has 0x29 and the meta-data file has 0x1) 12 

13 .Checking for rowformat Misery For table log in logdb USE information_schema; SELECT substring_index(name,'\#',1) AS Tbl ,file_format,row_format,count(1) Partitions FROM INNODB_SYS_TABLES WHERE name like ’logdb/logtable%’ GROUP BY 1,file_format,row_format order by 1; +------------------------+-------------+------------+------------+ | Tbl | file_format | row_format | Partitions | +------------------------+-------------+------------+------------+ | logdb/logtable| Antelope | Compact | 48 | | logdb/logtable| Barracuda | Dynamic | 25 | +------------------------+-------------+------------+------------+ 13 

14 .Solving the rowformat misery Long way... • ALTER TABLE inTableSchema.inTableName_part FORCE; • Create the destination table one partition at a time • ´set innodb_default_rowformat=’dynamic’;´ • ´create partition´ • ´set innodb_default_rowformat=’compact’; • ´create partition´ • Then run load as usual.. 14 

15 .Some related bugs/Feature requests Regarding create table like and row_format • https://bugs.mysql.com/bug.php?id=95478 • https://bugs.mysql.com/bug.php?id=95484 • https://bugs.mysql.com/bug.php?id=95486 • Big Thank’s to Jean-François Gagné for taking the time to write them :) • Links • https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition- operations.html 15 

16 .Rate My Session 16