MySQL Bugs: #56317: More flexible InnoDB tablespace management (extension of bug #25491)

Bug #56317	More flexible InnoDB tablespace management (extension of bug #25491)
Submitted:	27 Aug 2010 0:12
Reporter:	Roel Van de Paar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Besides bug #25491 (the ability to move a tablespace with it's objects from one instance to another), a customer has requested for the following functionality to be added to InnoDB.

The items below, amongst other things, make InnoDB more flexible, allowing management of the database as blocks of (size-configurable) tablespaces, adding new ones as the db fills up and/or dropping tablespaces if they are unneeded.

* Ability to add a new tablespace (providing optional filename) of a defined size, and maybe configurable as "auto expand", and maybe configurable with a max size.
* Ability to remove an existing tablespace file.
* Ability to resize an existing tablespace, increasing its size, modifying the current tablespace settings, or reducing it's size. When reducing it's size if objects need to be moved out of the tablespace to another one this process would be done automatically. If the reduction in the space would not complete because the database is full the shrink request would fail.
* Ability to be able to see the tablespace configuration: tablespace names, filenames, sizes, options etc.
* Ability to move a database object (table, trigger, view, stored procedure, database) from one tablespace to another
* Ability to determine in which tablespace a database object exists
* Ability to determine how much free space is available in each of the tablespaces.
* Ability in CREATE XXXXX statements to determine the tablespace where the object will be located.
* Ability to configurable options for this dynamically from the MySQL command line.

Important features for this bug (and bug #25491) are in any case: import/move/export at disk transfer speed, avoiding index rebuild/data reparsing.

How to repeat:
N/A

Two workarounds for people who need to move large chunks of data, want to avoid mysqldump and/or want to obtain client side data in a reusable format:

1. To dump, use SELECT INTO OUTFILE to a network-mapped path (i.e. SELECT * FROM <table> INTO OUTFILE "\\\\somehost\\somepath\\somefile.txt";), then re-import using LOAD DATA INFILE. Note the need for double escaping.

2. mysqldump --tab, then import using LOAD DATA INFILE or mysqlimport.
http://dev.mysql.com/doc/refman/5.1/en/mysqldump-delimited-text.html
http://dev.mysql.com/doc/refman/5.1/en/reloading-delimited-text-dumps.html

In regards the workarounds:

1. For Linux, this could be done using NFS or some other technology like sshfs.

2. Using this workaround (--tab) is only possible if mysqldump is being run on the same host as where the database resides.

For importing or exporting between innodb instances often you don't want to keep the "files" you are writing out on the source machine (due to disk space issues) so perhaps commands like:

innodb_export --defaults-file=/path/to/defaults/file db_name table1 table2 table3 ... -
innodb_import --defaults-file=/path/to/defaults/file db_name -

would be useful (the last parameter should perhaps go at the front and would represent the filename to use for the import/export and using a - as in normal unix convention would mean stdin/stdout.

Authorisation to the db could also be done with parameters such as --user, --passs, --port, --host etc...

This would allow for the very convenient form:

ssh remote_server innodb_export --defaults-file=/path/to/defaults/file db_name table1 table2 table3 ... - | innodb_import --defaults-file=/path/to/defaults/file db_name -

to allow me to move some innodb tables quickly and efficiently between 2 instances over the network.

Note:
1. the data sent over would be as much as possible "as stored on disk in the .ibd file" (may require using innodb_file_per_table, though I hope not)
2. may be endian specific if the ondisk storage is endian specific so the import command would need to know if importing from the wrong type of box
3. would not be expected to write to binlogs when importing (may be optional?)

So using tools like this do have some caveats but if they are understood it would make moving data over between systems much much easier and quicker.

4. Final point which is perhaps worth stating.
(a) Ensure the export is consistent
(b) Output should of course includes any "dirty pages/data" which might actually not be on the tablespace disks, or require some sort of "flush operation" prior to doing that.
(c) if possible this operation (export) should be as non-intrusive as possible to active use of the server.