« MySQL and the ANSI SQL Standards | Main | MySQL Attendees: Thanks for the iTunes »
April 15, 2004
InnoDB Multiple Tablespaces and Compressed Tables
Heikki Turri, creator of InnoDB, is delivering a presentation on the latest and greatest with InnoDB, multiple tablespaces and compressed tables.
Heikki got a PhD in Mathematics in Helsinki, Finland. Innobase Oy founded in 1995, goal was originally to make the worlds fastest disk-based relational database. In 1999 InnoDB complete with 110,000 lines of code. Did some marketing in 1999-2000 to find buyers, ran into Monty in August 2000. Wrote the interface to MySQL in 6 months and released InnoDB as a storage engine in March, 2001.
Main source or revenue (is profitable):
- license for hot backup tool
- royalty from MySQL pro license
- technical support contracts
Multiple tablespaces requested feature in 4.1.1, allows you to put each InnoDB into it's own .ibd file. Feature sponsored by RightNow Technologies, in Montana of all places.
Enabled by the innodb_file_per_table option in my.cnf. Will not move existing databases, but all newly created ones will go into a separate file. As before, the .ibd file contains both the data and index file (open to changing that in the future). If you remove the innodb_file_per_table option it will start creating new tables in one file, but will sill see the separate files.
Be extremely careful when downgrading from 4.1.1 to 4.0.18, read the downgrade instructions.
You can use symbolic links. Alter table used to recreate the table, removing the link and creating a new file in it's place. It sounds like that is not the case anymore and symbolic links can be used (not clear exactly).
The reason to have multiple tablespaces is to add flexibility. Cannot move .idb files between MySQL instances because they contain transaction ids specific for that instance.
It is possible to restore it to the database with DISCARD and IMPORT TABLESPACE. The .ibd file must be clean, meaning no uncommitted transactions, no unmerged insert buffer records, purged all delete-marked index records and .idb file flushed.
Table size on disk has been reduced by 20% in MySQL 5.0, also adding a zip-compression option to reduce disk space by another 50%.
Upcoming features
- Linux async disk i/o
- online index generation without locks
It's Heiki's birthday, Monty brought in a cake, and a large hourglass for "measuring database performance." Classy.
Posted by mike at April 15, 2004 11:20 AM
Hard Drive Recovery Group offers hard disk data recovery services for RAID, laptops and servers. Complete clean room and hard drive repair service.Trackback Pings
TrackBack URL for this entry:
http://mike.kruckenberg.com/mt/mt-tb.cgi/549