Tuesday Apr 10, 2012

InnoDB transportable tablespaces

Note: this article was originally published on http://blogs.innodb.com on April 10, 2012 by Sunny Bains.

The Problem

In the past, users were unable to take full advantage of the FLUSH TABLES WITH READ LOCK statement. InnoDB simply ignored the flush to disk part. If the table did not have any dirty pages in the InnoDB buffer that weren’t synced to disk (due to sheer luck) then it was safe to copy the .ibd file to another location. Also, the restore was not without its limitations and complications. The .ibd file could not be copied over to another server because InnoDB during import did not fix up metadata required for a trouble-free import. The main problems during import were:

  • If the tablespace ID of the IMPORTing instance had changed, for example if the table had been dropped and re-created, then the import would fail.
  • If the table and index IDs in the tablespace were different than on the importing server.
  • InnoDB uses a global row ID for tables without an explicit primary key. Any such row IDs in an imported tablespace had to be lower than the current maximum on the new server.
  • The maximum LSN of the tablespace had to be lower than the server’s current max LSN.
  • The maximum transaction (trx) ID of the tablespace had to be lower than the server current max trx id.
  • Purge and change buffer issues.

In short, if it worked you were lucky that the above constraints were satisfied, or your tables were probably read-only.

The Solution

Do the flush (export) properly and also the import. During flushing we disable purge, merge all pending change buffer entries to the tablespace and then flush all the dirty pages to disk. Note: Purge will remain disabled until UNLOCK TABLES; is issued. Write out a metadata file that contains the table schema, hostname of the exporting server, page size of the exporting instance, the highest autoinc value in memory etc. So that when we do an import, we can check if the table schema matches (currently, it ignores foreign key relationships) and also restore the autoinc value. This metadata file is created in the same directory as the tablespace, with a suffix of .cfg. If the table is named customer then the meta-data file will be named customer.cfg and the tablespace will be named customer.ibd. Note: FLUSH TABLES and IMPORT only work for tables that are stored outside the system tablespace, in their own tablespace. The InnoDB config parameter innodb_file_per_table must be set when creating the table.

Because InnoDB stores data in big-endian format, this allows copying data between different architectures. Copying between different operating systems will also work.

Example

If you want to backup a single table:

CREATE DATABASE ftwrl;

SET GLOBAL innodb_file_per_table=1;

CREATE TABLE ftwrl.t (C INT) ENGINE=InnoDB;

INSERT INTO ftwrl.t VALUES(1),(2),(3);

SELECT * FROM ftwrl.t;

FLUSH TABLES ftwrl.t WITH READ LOCK;

From the command line, copy the t.ibd and t.cfg to your backup directory. The files should be in the ftwrl/ sub-directory under datadir.

UNLOCK TABLES; — Note: This will remove the t.cfg file.

DROP TABLE ftwrl.t;

CREATE TABLE ftwrl.t (C INT) ENGINE=InnoDB;

ALTER TABLE ftwrl.t DISCARD TABLESPACE;

The DISCARD tablespace will rename the t.ibd file to t.ibt in the same directory; you have to remove this file manually for now. Once you’ve removed this file, copy the backed up tablespace t.ibd and the metadata file t.cfg to the ftwrl/ sub-directory. Then issue the folowing:

ALTER TABLE ftwrl.t IMPORT TABLESPACE;
CHECK TABLE ftwrl.t;
SELECT * FROM t;

You should be good to go. If you want to backup multiple tables together, the syntax for that is:

FLUSH TABLES t1, t2, …, tn WITH READ LOCK;

Copy as before but this time the multiple tablespaces and metadata files.

UNLOCK TABLES;

Then first discard the tablespace as above followed by an import as above.

Other changes
Errors and warnings will be pushed to the client so that the user doesn’t need to poke around in the server error log files to check why an operation failed. The intention is to send enough information to the user so that the problem can be clearly identified and understood.

Conclusion

The syntax for “export” is not finalised yet and may change; for now the above examples using FLUSH TABLES WITH READ LOCK should work. To export tables that are in a foreign key relationship, you must export and import all the tables together, otherwise you may end up with inconsistencies. The export/import doesn’t work on partitioned tables, this limitation will be addressed later. A matching table definition must exist in the importing server instance, currently we don’t automagically create the table using the saved metadata during import. The .cfg format is not written in stone yet, we may add more data to this file, for example for partition support.

Your feedback is important to us, so that we can improve this feature further.

Choose the Location of your InnoDB File-per-Table Tablespace

Note: this article was originally published on http://blogs.innodb.com on April 10, 2012 by Kevin Lewis.

The April 2012 InnoDB labs release introduces a new feature in InnoDB that allows you to choose the location of specific tables.  For example, you can place critical tables onto an SSD drive while leaving the system tablespace on a hard drive.  Conversely, you can store you primary database files on an SSD and put a seldom used but very large archive or reference table on a larger cheaper hard drive.

Innodb now makes use of the following existing syntax in MySQL ;

CREATE TABLE  . . .  DATA DIRECTORY = ‘absolute path of data directory’;

CREATE TABLE  . . .  PARTITION . . . DATA DIRECTORY = ‘absolute path of data directory’;

This syntax is used in MyISAM and Archive engines to make use of symbolic links in those operating systems that support it.  But InnoDB can use this syntax on any OS since it stores the path in a new system table called SYS_DATAFILES.  There is also a new system table called SYS_TABLESPACES.  Both of these can be viewed in the April Labs release of 5.6 by using information schema. For example;

mysql> SET GLOBAL innodb_file_per_table=on;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='/ssd1/my_data';
 Query OK, 0 rows affected (0.12 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES;
 +-------+---------+------+-------------+----------------------+-----------+---------------+
 | SPACE | NAME    | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE |
 +-------+---------+------+-------------+----------------------+-----------+---------------+
 |     1 | test/t1 |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
 +-------+---------+------+-------------+----------------------+-----------+---------------+
 1 row in set (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES;
 +-------+---------------------------+
 | SPACE | PATH                      |
 +-------+---------------------------+
 |     1 | /ssd1/my_data/test/t1.ibd |
 +-------+---------------------------+
 1 row in set (0.00 sec)

Notice that a directory named for the database is added after the DATA DIRECTORY provided.  This is the same as what is done in the normal data directory.  It allows you to chose the same remote location for all your tablespaces, even if the tables in different databases have the same name.

The last 4 columns in innodb_sys_tablespaces are interpretations of the tablespace flags field. Tablespace flags do not distinguish between Compact and Redundant row format, so you see what is displayed above.  If you want to see the row format of the table, innodb_sys_tables now also interprets the table flags;

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name like 'test%';
 +----------+---------+------+--------+-------+-------------+------------+---------------+
 | TABLE_ID | NAME    | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
 +----------+---------+------+--------+-------+-------------+------------+---------------+
 |       24 | test/t1 |   65 |      5 |     1 | Antelope    | Compact    |             0 |
 +----------+---------+------+--------+-------+-------------+------------+---------------+
 1 row in set (0.00 sec)

In addition to SYS_DATAFILES, the absolute file path to the tablespace is also written to a text file in the normal datafiles location of the tablespace.  This is also the location of the FRM file written by the MYSQL server.  It has the same name as the FRM except it has the extension ‘.isl’ which stands for InnoDB Symbolic Link.  It is there to support recovery which happens before the system tables are opened.  For the example above, the sole contents of t1.isl are;

/ssd1/my_data/test/t1.ibd

The ISL file also allows a DBA to move one of these remote tablespaces since ALTER TABLE … DATA DIRECTORY =  ‘. . .’ is currently not supported.  In order to move a tablespace to another location, or to port an entire database along with its remote tablespaces, you must edit this ISL file after moving the OS files.  The ISL file is a simple text file that contains the absolute path and filename of the tablespace.  Just change that to the new location and save your changes.  Do not leave a copy of the tablespace in the old location.

If you have a large existing database that uses file-per-table tablespaces (files with ‘.ibd’ extensions), it is possible to move these from the default location next to the FRM file to a remote location such as a new SSD drive.  But you must shut down the MySQL server first. After it is shut down, move the IBD tablespace to the location that you would like, create a text file where it was with the same name except using a ‘isl’ extension.  Make the contents of the text file to be the full file path of the ibd file.  Then restart the server.  It will read the isl file, find the tablespace, and update the internal system tables to show where the file resides.

Conclusion

This new feature provides a valuable option to configure your database to make the best use of the hardware available.

InnoDB 2012 Spring Labs Release

Note: this article was originally published on http://blogs.innodb.com on April 10, 2012 by Calvin Sun.

InnoDB team is pleased to announce the 2012 Spring labs release, with several much anticipated new features and performance enhancements. Please download mysql-5.6-labs-april-2012 from MySQL Labs and give a try. Do not forget to provide your feedback.

The 2012 Spring labs release on MySQL Labs consists of the following InnoDB new features, which are not in the newly released MySQL 5.6.5 DMR yet:

  • Online DDL: some of the DDLs are now truly online, including ADD INDEX, SET DEFAULT, and DROP FOREIGN KEY.
  • Memcached plugin: with additional features, such as SASL support.
  • Transportable tablespace: allow user to export data files and import them into another MySQL instance.
  • Persistent statistics ON/OFF switch: the ability of controlling persistent statistics on table level.
  • Option for specifying locations of InnoDB tables: allows user to choose the location of specific tables.

This labs release also includes several performance and scalability improvements, specially on modern CPUs:

  • Reduced false sharing
  • Configurable fast mutexes
  • my_hash_sort_simple() loop unrolling
  • Improved adaptive flushing
  • Improved neighbor flushing

With those improvements, (InnoDB) read-only performance reaches a new high. Please see Mikael’s blog for some of the improvements. You will see the benchmark results on DimitriK’s blog. And the InnoDB team will also continue publishing technical details in the coming days on this site.

We intend to make those new features & improvements into future development milestone releases and GA releases. Thanks for being interested in InnoDB!

About

This is the InnoDB team blog.

Search

Archives
« April 2012 »
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
12
14
15
17
18
19
20
21
22
23
24
25
26
27
28
29
30
     
       
Today