Notes from Heikki Tuuri's InnoDB Multiple Tablespaces and Compressed Tables talk at the 2004 MySQL User's Conference...
Heikki spent quite some time as an academic doing math and later computer science. He wrote the first line of code in 1994 and was trying to figure out why relational databases were so slow. Five years later he had 110,000 lines of code and could run a TPC-C benchmark. He met Monty in 2000. Work in the InnoDB/MySQL interface began shortly after and took about 6 months. First release in 2001.
Heikki owns the company (Innobase Oy) and he now has two employees. Pekka working on hot backup and Marko working on compressed table space development. A third guy will be coming on board soon as well. Innobase Oy is an OEM supplier for MySQL AB. They make money via hot backup licenses, royalty from MySQL licenses, and support contracts. The company is profitable.
Development slowed in 2003 because Heikki was dealing with new users, bug reports, and support contracts/questions. New hires will speed that up again.
Multiple tablespaces appeared in MySQL 4.1.1 (Dec 2003). Sponsored by RightNow Technologies of Montana. Each table can be stored in its own .ibd file rather than one massive tablespace.
To enable it, just add innodb_file_per_table to my.cnf and you're good to go. But you do still need one ibdata file for the data dictionary, undo logs (rollback segment), and so on.
If you later disable support for multiple tablespaces, InnoDB will continue to use the old files while putting new tables back in the single tablespace. InnoDB doesn't handle symlinked tables during an ALTER TABLE like MyISAM does. The new table will end up in the default location, not where the original table lived.
You cannot move .ibd files from one instance to another. The .ibd files contain transaction ids and log sequence numbers. You also cannot move tables around on the same machine. Instead, use RENAME TABLE (and maybe a symlinked directly) to accomplish it.
Interesting... for restoring an old version of a table, you can remove the current one using ALTER TABLE mytable DISCARD TABLESPACE and then ALTER TABLE mytable IMPORT TABLESPACE to restore the old one. But the table must be clean, meaning no uncommitted records, all buffered inserts must be done, and all delete marked records must have been purged.
Compressed table formats will help reduce the disk usage required to store data. NULLs will take no space, many of the length records have been removed, and they've added on-the-fly zip compression to further reduce space. The typical InnoDB user should see at least a 50% reduction in disk space usage.
Old tables will not be automatically converted to the new format. Pages will still be 16k, but most pages will end up as 8k on disk. They'll need some sort of compression prediction but are still hacking on that it seems.
They also are looking at in-memory compression so that more pages will fit in the buffer pool. (Nice!)
Upcoming features: linux async disk I/O (available in Linux 2.6) and on-line index generation without setting locks. Unknown ETA.
They've added automatic index generation on foreign keys (in case you forget to create them). This should be in 4.0.19, he thinks. Sounds like it'll be in the next 4.1.x release for sure.
Posted by jzawodn at April 15, 2004 08:03 AM
