InnoDB tablespace tools

[Steve Ruby]

Is there a plan to have any tablespace management tools for innobase?

I personaly dont' care if these are via a separate program or through
the mysql SQL interface but the following would be very handy.

move tables between tablespaces
list tables in a tablespace
shrink tablespace

It is very handy with BDB and MyISAM tables to be able to back them
up directly or move them across different machines. If I have
a large database server with gigs of InnoDB tablespace and we need
to move a single database or table to another server it would
require mysqldump and reloading the tables, which can be very slow
for large tables. This seems to be the main disadvantage of the table
space method. It would be nice if one could move a binary table
with the following steps.

- shutdown the server
- copy a table into a new tablespace by itself
- move the "portable" table space to the new server
- expand the tablespace for growing room or copy the table to an
existing tablespace

I realize these are major things, I'm just curious if there are plans
for any of the above sort of tools.

---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <mysql-th...@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-mysql=freebsd.csie...@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

[Heikki Tuuri]

Steve,

I cannot promise any such tools in the next 6 months.
My TODO list has the two important items currently:
support for arbitrary size BLOBs and CHECK TABLE.

The right way to proceed now is to test existing tools and their speed.

Could you measure the speed of the following options in dumping
and importing a table:

1) a) SELECT INTO outfile;
b) LOAD DATA INFILE ...
2) a) ALTER TABLE yourtable TYPE = MYISAM;
b) then copy the MyISAM .frm, data, and index files to another database;
c) in the other database ALTER TABLE yourtable TYPE = INNODB
3) a) create a MyISAM table yourplaintable with the same columns but no indexes;
b) INSERT INTO yourplaintable SELECT * FROM yourtable;
c) copy the MyISAM data and .frm files to another tablespace;
d) in the other database create yourtable with the indexes defined;
e) INSERT INTO yourtable SELECT * FROM yourplaintable.

I can update the InnoDB manual if one of these methods is much faster than
the ordinary mysqldump and then mysql < ... .

When the mysql client is used in importing a table, I guess much of
the CPU goes to client server communication. Above methods might be much
faster as long as they are not disk bound.

Now I recall the following solution if you find it cumbersome to add
"set autocommit = 0; ...; commit" to your table dump files: before you start
MySQL, set

innodb_flush_log_at_trx_commit=0

in my.cnf. Then the commits will not take extra time.

Regards,

Heikki
http://www.innobase.fi