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
