[TYPO3-dev] TYPO3 4.1 INNODB

[R. van Twisk]

Dmitry Dulepov wrote:
> 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.
>
>   
Hey Dmitry,

I also have seen the same behavior of myissam table, so whatever we can 
move out of
myisam when innodb is available would be great.

If you read the thread '[TYPO3-dev]  Indexed Search Performance 
Improvement' people are finding
a way to improve indexed search using full text indexes. However full 
text search doesn't work on
innodb, something to take into consideration.

I am not sure if indexed search operates on it's own tables, or that it 
works on other tables.
But possibly we need to keep indexed search in myisam.

in any case, +1 for moving to innodb whenever we can!

Ries