MySQL Performance: MySQL => InnoDB's compact row format

Members

Help

Home

Home » Performance » MySQL » InnoDB's compact row format

Show: Today's Messages :: Show Polls :: Message Navigator

InnoDB's compact row format [message #1473]

Mon, 25 June 2007 06:12

razdaman
Messages: 26
Registered: May 2007

Junior Member

Hi!

For quite some time I've used only myisam as storage engine for my mysql tables. During my early years as a developer I though that "myisam is the simplest storage engine, therefore it is also the fastest". After reading several advanced books about mysql and general database theory I now realize that this isn't always the truth. Thus I'm now trying to master the features innodb storage engine.

Today I realized that innodb does not used "fixed"/"variable" format-type as I'm used to with MyIsam. Starting with 5.0.3 it uses the "compact" row format as default. This format uses variable row length (right??) as "variable" row format I know from myisam. As far as I have understood, variable row length means less disk space and slower seeks. Therefore myisam uses only "variable" row format when it is "forced to" (if there are any varchat, text columns).

My question is therefore: how come innodb uses a variable row format as default? Isn't that slower than allocating x bytes for each row so the disk seeks will be faster?

Please enlighten me, I'm a bit confused Smile

(I hope my English is okay, if not - just ask what I mean, please)

Report message to a moderator

Re: InnoDB's compact row format [message #1479 is a reply to message #1473 ]

Mon, 25 June 2007 18:43

sterin
Messages: 323
Registered: March 2007
Location: Sweden

Senior Member

My opinion is that the discussion about pros and cons between static and dynamic row format has grown larger then it actually deserve.

The pro with static row format like speed and simplicity is just about outweighed by the fact that dynamic rows requires less storage.

The reason is that shuffling a lot of data around is expensive and RAM that could be used for caching data is used to store unnecessary empty data structures etc.

That is why most database vendors has chosen a variable storage format.

Report message to a moderator

Re: InnoDB's compact row format [message #1482 is a reply to message #1473 ]

Tue, 26 June 2007 03:47

razdaman
Messages: 26
Registered: May 2007

Junior Member

Thank you for your reply.

Well, when I switched my user-table (myisam) from variable to fixed length I experienced a considerable performance boost. That's why I am a bit "afraid" of the "compact" row format.

That being said I can't say that you are not right - so I'll guess I'll just have to try out this row format "in action" because I really want to switch to innodb.

One more question: in order to allow the "fixed" row length, when using myisam, i moved my "profile" (TEXT) column from my users-table to a users_profiles and linked these tables with a user_id.

According to what you are saying, I don't have to do something like this when using innodb/compact? I mean - I know it's not ideal - but will the performance drop be as significant with myisam when having TEXT-columns in the user-table?

Thanks..

[Updated on: Tue, 26 June 2007 03:58]

Report message to a moderator

Re: InnoDB's compact row format [message #1484 is a reply to message #1473 ]

Tue, 26 June 2007 04:51

sterin
Messages: 323
Registered: March 2007
Location: Sweden

Senior Member

The comparison is not really correct.

If you have a TEXT/BLOB column in a table and you don't always select it then breaking it out into a separate table is good practice.

Because in these cases the search time for a table is very much reduced since the table size is much less MB's.

This alone can speed up a lot of queries quite a bit.

But if your queries use indexes at all times this usually isn't an issue.

Report message to a moderator

Re: InnoDB's compact row format [message #1496 is a reply to message #1473 ]

Thu, 28 June 2007 12:33

razdaman
Messages: 26
Registered: May 2007

Junior Member

Ah okay, thank you.

When I used MyISAM I always chose the CHAR datatype over VARCHAR as performance was much more important than disk space.

How do I decide what data type to use when using innodb? - if tailing spaces is truncated in innodb's compact row format, what is the purpose of using the CHAR data type?

Report message to a moderator

Re: InnoDB's compact row format [message #1498 is a reply to message #1473 ]

Thu, 28 June 2007 19:17

sterin
Messages: 323
Registered: March 2007
Location: Sweden

Senior Member

My rule is to always use VARCHAR.

The only exceptions are:
If I am certain that the string I'm going to store is always constant in length or very near the maximum size all the time I can choose CHAR.
For example storing an encrypted password using MD5() the string is always 32 bytes which means it's a good candidate for CHAR.

If the string is equal to or less than 4 bytes I also choose CHAR.

On a side note it is good to remember the odd behaviour with CHAR that it silently removes trailing spaces from the string. Which means that you sometimes don't get back what you stored.

Report message to a moderator

Re: InnoDB's compact row format [message #1500 is a reply to message #1473 ]

Fri, 29 June 2007 05:12

razdaman
Messages: 26
Registered: May 2007

Junior Member

Okay

Thanks

[Updated on: Fri, 29 June 2007 05:21]

Report message to a moderator

Previous Topic:	what is the purpose of ibdata1,ib_logfile0,ib_logfile1
Next Topic:	replication problem after master crash

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 8 21:31:21 EST 2009

Total time taken to generate the page: 0.06433 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.