On Tue, Mar 23, 2010 at 8:49 AM, MARK CALLAGHAN <mdcal...@gmail.com> wrote:
> On Tue, Mar 23, 2010 at 8:15 AM, Jeremy Zawodny <jer...@zawodny.com> > wrote: > > Mark, > > > > Facebook changes are described in > http://www.facebook.com/note.php?note_id=23844338919. Alas it doesn't > use a generic memcache interface internally so it won't be useful to > others. > Gotcha. The theory I like anyway. We have too many processes the have to fetch the ids of recently changed rows from the database. In reality we need a queuing system. In practice that's harder than I'd like to get in place, so the DB is the source of truth for too many things. > >> Are your slaves IO bound? If so, have you tried mk-slave-prefetch? I > >> think it can fix the replication lag problems for IO bound slaves. It > >> needs more people to test and use it. It can be used right now. > > > > I actually did use mk-slave-prefetch a year or so ago but ended up > proving > > to myself that we were not I/O bound in the worst case. We were then > (and > > still currently are, pending a few ALTER TABLEs) bound on index updates > for > > over-indexed tables in InnoDB. > > Is the bottleneck for those index updates CPU or IO? mk-slave-prefetch > has recent changes to fetch in parallel and to fetch for all indexes > on a table. For IO bound loads both of these are significant > improvements over what it used to do. > It appears not to be I/O but it also doesn't appear to be CPU in many cases. I have not dug deeper other than to prove that as I remove the unused and bulky indexes, the problem mostly goes away. > > > > I'm still trying to fix the over-indexing nightmare that pre-dates me. I > > should show slides at a conference sometime to illustrate the effects of > > heavily indexed tables on replication (or general update) throughput. > The > > numbers shocked me at first. > > Off topic but do you use the Percona patch for SHOW INDEX_STATISTICS? > Nope. I think this pre-dates that work to some degree. Most of what I know about our problem is the result of some testing and code inspection. We actually have a pretty good idea which indexes are used and useless. Everytime a query fires up trying to use a useless one, things really slow down. As our SSD deployment continues, some of this goes away, but it's still far from optimal. Jeremy
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : drizzle-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
