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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
