On Mar 23, 2010, at 8:15 AM, Jeremy Zawodny wrote: > 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.
I wonder if what you're seeing has to do with the innodb index lock Vadim from Percona recently wrote about... http://www.mysqlperformanceblog.com/2010/02/25/index-lock-and-adaptive-search-next-two-biggest-innodb-problems/ It would seem to me that if you have a huge concurrency level, with tight, fast queries using indexes, and your replication has to slam an exclusive lock on each of them every time there's an update, it would make sense that you'd get shockingly bad latency given many indexes that are all in use. If you have 32 queries running that are all using different indexes from table1, and you need to insert a record into table1, you have to wait for those 32 queries (plus any that have started using later indexes while waiting for the locks on the earlier indexes) to finish before your insert can go forward, then while doing it queries are going to back up on the exclusive lock, then you're going to have to deal with the backlogged queries *and* the usual concurrency on your next exclusive lock. Seems like the index lock problem is ripe for a read-copy-update implementation for slaves (that probably wouldn't work well for masters because of concurrent index updates). _______________________________________________ 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
