If you cover stuff like this in your book, I'll definitely buy it. :) Ok, last question on this subject, I promise! Why not use InnoDB and replication? I was looking through the manual expecting to find a line that says "replication is only available with MyISAM tables" -- but I can't find it anywhere. It seems like this combination would be the best of both worlds. Am I missing something?
Thanks, --jeff ----- Original Message ----- From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Jeff Kilbride" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, March 02, 2002 8:29 PM Subject: Re: Re-baselining replication slaves? > On Fri, Mar 01, 2002 at 02:41:03PM -0800, Jeff Kilbride wrote: > > > > Does replication hurt MyISAM performance in the same way? If I'm > > running a master that takes all the inserts and one or more slaves > > to take all the select queries, would it be better implemented in > > MyISAM or InnoDB? > > It depends. :-) > > > I don't need the transaction ability of InnoDB, but if concurrent > > replication affects MyISAM performance in the same way as concurrent > > insert/select ops, it makes me wonder which table type I should use > > on my high insert activity tables. My assumption was MyISAM, but I'm > > not so sure now... > > It'll partly depend on the total number of reads you needed to > do--divided by the number of slaves available for the reads. Let's > assume that you need to do 500 queries per second and 50% of them are > reads, 50% are writes. Let's further assume that this is simply too > much for a single machine to handle because of MyISAM's poor > concurrency. > > One option is to simply switch to InnoDB and take advantage of the > higher concurrency it offers. This should work quite well in many > cases, but if your traffic needs to grow over time, you'll eventually > hit a bottleneck and need to throw more [expensive] hardware at the > problem. > > Another option is to use replication and stick will MyISAM. The > master can handle the 250 queries/sec of writes. Then you can add a > few slaves (2 or 3) and distribute the load equally among them (using > DNS round robin, LVS, or a load-balancer from Foundry, Cisco, Alteon, > etc). Then each slave only needs to handle a fraction of the read > queries and all the write queries, of course. The advantage here is > that you have a more scalable architecture. If things get too slow > you can choose between adding more machines, moving to InnoDB, or > both. > > In my group at Yahoo Finance, we're currently doing MyISAM with > multiple slaves. We needed the slaves anyway in case the master dies > and because we wanted copies of the data several thousand miles away. > But we'll be using InnoDB soon as well, so it'll be interesting to see > how things behave. > > Ugh, this is a really good question that I don't answer anywhere in my > book yet. I'll have to add a spot. Maybe in the replication > section. Or the "table types" section. Or both. Hmm. > > Thanks, > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > > MySQL 3.23.47-max: up 23 days, processed 766,998,461 queries (376/sec. avg) > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
