what do you consider a high number of updates/sec?
I'm the DBA for a popular website in that league (well, maybe not google or
yahoo but certainly ticketmaster) and we average ~210 DMLs/sec with peaks in
excess of 1,000. we use a mixture of myisam for static (or infrequently
updated) reference tables and innodb for the high DML ones. in benchmark
tests (re: load/stability) I was able to sustain ~4,500 selects/sec against
this for eight hours without any problem. this on only a dual Opteron.
throw in a good (at least I like to think) replication implementation and
we've gone as high as 14,000 selects/sec under high site load (we have
enough slaves to do WAY more). I think the reason we have not seen the
innodb performance degredation you are describing is that we serialize all
our DML into the main replication master across eight "channels" (by type of
data being updated) - data change requests come in as XML, are parsed by
proprietary (re: homebrew) code and translated into DML which is then
executed on the replication master. this ends up looking someting like
Oracle AQ (or concurrent managers in ERP) but way more stable (we used to
get ORA-00600s like most people get 942s for the Oracle literate out
there). we never have more than 10 threads concurrently doing DML
operations.
so to answer your original ? we (a big, well-known website) use a mixture of
table handlers, serialized DML and a bunch of replication.
I know every site is different (ours happens to be ideally suited to MySQL's
replication architecture) and there are certainly sites/apps with much
higher requirements than us that but those are not trivial #s...
hope that helps...
On 1/1/07, mos <mos99@stripped> wrote:
>
> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
