MySQL Forums
Forum List  »  InnoDB

Re: Innodb vs. myisam storage usage *shocked*
Posted by: Heikki Tuuri
Date: February 05, 2005 02:40AM

James,

only the tables created with

ROW_FORMAT=COMPRESSED

will be zip-compressed. Of course, if the table already contains BLOBs in compressed formats, then it is no good to advise the database to try even more compression.

There are downsides to zip-compression: it may eat up to one CPU to compress pages, and if there is corruption, the whole page will be lost, not just individual rows in it.

The compression will be completely transparent: the user can update the table like any other table.

---

Looking at the original question that started this thread: the size of the table in the MyISAM format was 20 MB, and the size in the InnoDB format was 60 MB. The space usage with the 4.x InnoDB table format can indeed easily be 3 x the MyISAM size. The new InnoDB format in 5.0 may in some cases cut the space consumption more than 50 %, though typically it is 20 %, I think. The upcoming zip-compression will normally save 40 % of space.

The 17 MB 'free space' in the InnoDB data file probably came from the 'undo log' (= rollback segment). 4.0.16 runs the ALTER as one huge transaction. MySQL-4.1.8 and later do a commit in ALTER TABLE at every 10 000 rows. That would cut the undo log usage to less than 1 MB in the conversion.

Best regards,

Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php

Options: ReplyQuote


Subject
Views
Written By
Posted
11020
February 02, 2005 10:26AM
Re: Innodb vs. myisam storage usage *shocked*
7308
February 05, 2005 02:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.