[TYPO3-dev] TYPO3 4.1 INNODB

[Dmitry Dulepov]

Hi!

Steffen Müller wrote:
> Dmitry, could you please explain why "InnoDB is more stable than MyISAM
> and prevents database corruption at peaks"?
> I did not yet read about that.

Mysql does its best to prevent simultaneous writes to myisam tables. It 
relies on locks (normally file locks) when user tries to write to myisam 
table. However this does not always work as expected, especially at peak 
hours. Sometimes at least two writes still happen and table immediately 
becomes corrupted. Search English list archives, you will find several 
questions about corrupted tables. I had those too several times, when 
mysql has to process over 700qps (as mytop reports). At one point 
crashes started to appear every day. I searched inet and found various 
posts about it that generally recommended to move to innodb. I did that 
more than half a year ago and did not have *any* table corruptions on 
those tables even though load increased.

InnoDb uses row transaction internally even if user did not start it 
explicitly. While myisam locks the whole table at write, innodb locks 
only space for one row. See the advantage? You can have many clients 
writing to table at once with InnoDB, who are well separated. But you 
cannot have more than one write to myisam table (performance killer!) 
and if it happens, your table dies immediately!

InnoDb, of course, has its disadvantages. If "select count(*) from 
table" takes very little time/resources on myisam (because count is 
stored in table itself), innodb has to really count rows. But this is 
not a problem for typo3 because typo3 does not have such queries :)

But there is one more advantage for Innodb. Myisam keeps indexes 
separately from row data. Thus if row pointer is found, myisam engine 
has to do one  more seek to data. Innodb keeps indexes together with 
rows, so it does not have to seek again.

In general, I think innodb is much better for typo3 than myisam. I 
really did several days of research before switching.

-- 
Dmitry Dulepov

Web: http://typo3bloke.net/
Skype: callto:liels_bugs

"It is our choices, that show what we truly are,
far more than our abilities." (A.P.W.B.D.)