Hi Robert,
On 08/05/2009, at 11:39 AM, Robert Hodges wrote:
This is an interesting idea and one that we noodled about for quite
some
time while working on Sequoia and other clustering software.
However, I
think it will not be so easy to implement. The problem is that while
recording the "beginning" commit number can help you sort out views
of the
data, it does not help with ordering locks and will lead to
deadlocks on the
slave. The problem becomes apparent when you allow multi-statement
transaction.
Well, on the good side MySQL and InnoDB adhere to the two-phase
locking protocol inside transactions, that is: once a lock has been
released, no other lock can be acquired within the same transaction.
This is simply done by not having an unlock syntax at all, all locks
are released at commit/rollback.
So, deadlocks are reduced because of this compared to some other RDBMS
(who choose possible performance over deadlock risk in that case)
Not all RDBMS are the same, so a tool that supports more than one
RDBMS has a smaller set of choices to work with.
One possible solution is to have T1 acquire all locks in advance,
which
should preserve the original serialization but eliminate deadlock.
However,
that means you can't just execute SQL commands on the slave to apply
changes.
Nah.
Another option is to apply the approach only on groups of
autocommit transactions, but that would rule out a lot of the value.
Actually I think that is really valuable and perhaps an excellent
approach to start with!
Many apps run autocommit for most stuff, and have some transactions.
So there the vast majority of writes can be done in parallel, which
would be a fantastic improvement. I'm going to guess that Jeremy
Zawodny will be cheering on for this one when he reads ;-) and he
won't be the only one.
In general it seems the easier solution is to look for application
features
that partition data into disjoint sets so you can replicate in
parallel
streams. In this case you typically need the application author to
provide
some hints. It would be interesting to think how Drizzle could make
that
process easier.
Sure.
Cheers,
Arjen.
--
Arjen Lentz, Director @ Open Query (http://openquery.com)
Affordable Training and ProActive Support for MySQL & related
technologies
Follow our blog at http://openquery.com/blog/
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
