MySQL Forums
Forum List  »  InnoDB

Innodb vs. myisam storage usage *shocked*
Posted by: Consensus
Date: February 02, 2005 10:26AM

Short introduction : (:-)
I was shocked by the myisam repair (i created a automated repair using myisamchk which tries from softest to hardest repair until it works)
After a power outage during write the chance is 1: about 50 that the repair will not only repair the table it almost all records gets deleted. i had results from 1 records up to 300,000 records which got removed (and those were not buggy)

On freenode i got the hint to use Innodb, it got doublewrite buffers etc, and should handle a crash much better.

I did a few tests and i am "stopped" by the result that innodb required up to 8 times of the storage myisam needs!!

I got a table with 2 date fields 1 datetime and integers (tiny,small,medium,normal),
the primary key (no other index) is on 2 INT columns, the last integer is autoincremental.
total of 32 columns (most can be null)
Rowformat is dynamic.

I filled it with dummy data (300,000 rows, few numbers and NULLs).

Result :

Datafile = 13mb
Index = 7mb

i converted it now using alter table type=innodb

Datafile = 90mb (!!!!!)
Datalength = 53mb
Indexlength = 6.6mb
Freespace = 17.5mb (?)

I use mysqld 4.0.16

Maybe someone here got a few spare minutes and can help me a bit
Is this really normal behaviour of innodb?
Is there a way to make the storage use more efficient?
Is there any other option for me, i do not need transactions or ultra performance.
I just need something like myisam but it needs to handle a crash (power loss) better
than myisam (removing 90% of the table because of 1 corrupted row is way too much)

Please respond

Options: ReplyQuote

Written By
Innodb vs. myisam storage usage *shocked*
February 02, 2005 10:26AM

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.