Re: How scaleable is MySQL 5's Innodb with multi-core CPU's?

[mos]

At 12:49 PM 1/1/2007, Jochem van Dieten wrote:
>On 1/1/07, mos wrote:
>>Is there a problem with InnoDb scaling with multi-processor CPU's?
>>Apparently after reading the Tweakers.net article,  with only 40
>>simultaneous users the performance of MySQL 5  will collapse.
>>
>>http://www.mysqlperformanceblog.com/2006/11/30/interesting-mysql-and-postgresql-benchmarks/
>>
>>http://tweakers.net/reviews/649/6
>>
>>Has this been fixed?
>
>As the article on the MySQL Performance Blog mentioned, a fix from
>InnoDB has been integrated into 5.30. Tweakers.net has already tested
>this fix and it does show some improvement, but it still has a long
>way to go: http://tweakers.net/reviews/661/6
>
>Jochem

Jochem,
         Yes Innodb has a long ways to go and I'm wondering if it is 
fixable so the performance is more linear. As it is, performance in the 
Tweakers' charts drop dramatically (tanks?) after 7 concurrent users even 
for version 5.03.  I know Innodb works best if the table fits into memory, 
but for me that isn't practical (at least on one machine) because the 
tables will grow over time and I don't want to crash into a wall when the 
table exceeds memory capacity of the machine. The MySQL Cluster database 
looks like it is an alternative but I hear it doesn't do well on table 
joins (I may be able to get around that). But using a Cluster database 
means I'll need to start off with 4 or 5 computers instead of just one so 
startup costs are a lot more. :(

         So I'm wondering how high traffic websites that use Innodb can 
overcome this problem? Google GMail, Craigs List, TIcket Master, Yahoo etc 
all have high number of updates per second, so there must be an InnoDb 
solution, right? They can't be using MyISAM for transactional updates, and 
InnoDb is the only engine of choice until recently (Cluster Db is too new). 
If this problem is as bad as Tweakers claim it is, then the only solution I 
see is to run multiple database servers instead of adding processors to a 
central server.  So if they were going to do it over again, I wonder if 
they would still stick with InnoDb or go with a MySQL cluster or choose 
something else like PostgreSQL that scales better?

I know these questions are pretty much rhetorical, but I thought I'd bounce 
this off of you guys to see what the best approach is for a high traffic 
transactional web site. If you were going to write one of these web sites I 
mentioned, would you still use InnoDb?

TIA
Mike