<div dir="ltr">Lee,<div><br></div><div>No problem about mixing up the Mike's, there's a bunch of us out there :-). What are you are describing here is very much like a spec I wrote for Nova[1] a couple months ago and then never got back to. At the time I considered gearing the feature toward oslo.db and I can't remember exactly why I didn't. I think it probably had more to do with having folks that are familiar with the problem reviewing code in Nova than anything else. Anyway, I'd like to revisit this in Kilo or if you see a nice way to integrate this into oslo.db I'd love to see your proposal.</div>
<div><br></div><div>-Mike</div><div><br></div><div>[1] <a href="https://review.openstack.org/#/c/93466/">https://review.openstack.org/#/c/93466/</a></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Sun, Aug 10, 2014 at 10:30 PM, Li Ma <span dir="ltr"><<a href="mailto:skywalker.nick@gmail.com" target="_blank">skywalker.nick@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="">> not sure if I said that :). I know extremely little about galera.<br>
<br>
</div>Hi Mike Bayer, I'm so sorry I mistake you from Mike Wilson in the last post. :-) Also, say sorry to Mike Wilson.<br>
<div class=""><br>
> I’d totally guess that Galera would need to first have SELECTs come from a slave node, then the moment it sees any kind of DML / writing, it transparently switches the rest of the transaction over to a writer node.<br>
<br>
</div>You are totally right.<br>
<div class=""><br>
><br>
> @transaction.writer<br>
> def read_and_write_something(arg1, arg2, …):<br>
> # …<br>
><br>
> @transaction.reader<br>
> def only_read_something(arg1, arg2, …):<br>
> # …<br>
<br>
</div>The first approach that I had in mind is the decorator-based method to separates read/write ops like what you said. To some degree, it is almost the same app-level approach to the master/slave configuration, due to transparency to developers. However, as I stated before, the current approach is merely used in OpenStack. Decorator is more friendly than use_slave_flag or something like that. If ideally transparency cannot be achieved, to say the least, decorator-based app-level switching is a great improvement, compared with the current implementation.<br>
<div class=""><br>
> OK so Galera would perhaps have some way to make this happen, and that's great.<br>
<br>
</div>If any Galera expert here, please correct me. At least in my experiment, transactions work in that way.<br>
<div class=""><br>
> this (the word “integrate”, and what does that mean) is really the only thing making me nervous.<br>
<br>
</div>Mike, just feel free. What I'd like to do is to add a django-style routing method as a plus in oslo.db, like:<br>
<br>
[database]<br>
# Original master/slave configuration<br>
master_connection =<br>
slave_connection =<br>
<br>
# Only Support Synchronous Replication<br>
enable_auto_routing = True<br>
<br>
[db_cluster]<br>
master_connection =<br>
master_connection =<br>
...<br>
slave_connection =<br>
slave_connection =<br>
...<br>
<br>
HOWEVER, I think it needs more investigation, so this is why I'd like to put it in the mailing list in the early stage to raise some discussions in depth. I'm not a Galera expert. I really appreciate any challenges here.<br>
<br>
Thanks,<br>
Li Ma<br>
<div class="im HOEnZb"><br>
<br>
----- Original Message -----<br>
From: "Mike Bayer" <<a href="mailto:mbayer@redhat.com">mbayer@redhat.com</a>><br>
To: "OpenStack Development Mailing List (not for usage questions)" <<a href="mailto:openstack-dev@lists.openstack.org">openstack-dev@lists.openstack.org</a>><br>
Sent: 星期日, 2014年 8 月 10日 下午 11:57:47<br>
Subject: Re: [openstack-dev] [oslo.db]A proposal for DB read/write separation<br>
<br>
<br>
</div><div class="HOEnZb"><div class="h5">On Aug 10, 2014, at 11:17 AM, Li Ma <<a href="mailto:skywalker.nick@gmail.com">skywalker.nick@gmail.com</a>> wrote:<br>
<br>
><br>
> How about Galera multi-master cluster? As Mike Bayer said, it is virtually synchronous by default. It is still possible that outdated rows are queried that make results not stable.<br>
<br>
not sure if I said that :). I know extremely little about galera.<br>
<br>
<br>
><br>
><br>
> Let's move forward to synchronous replication, like Galera with causal-reads on. The dominant advantage is that it has consistent relational dataset support. The disadvantage are that it uses optimistic locking and its performance sucks (also said by Mike Bayer :-). For optimistic locking problem, I think it can be dealt with by retry-on-deadlock. It's not the topic here.<br>
<br>
I *really* don’t think I said that, because I like optimistic locking, and I’ve never used Galera ;).<br>
<br>
Where I am ignorant here is of what exactly occurs if you write some rows within a transaction with Galera, then do some reads in that same transaction. I’d totally guess that Galera would need to first have SELECTs come from a slave node, then the moment it sees any kind of DML / writing, it transparently switches the rest of the transaction over to a writer node. No idea, but it has to be something like that?<br>
<br>
<br>
><br>
><br>
> So, the transparent read/write separation is dependent on such an environment. SQLalchemy tutorial provides code sample for it [1]. Besides, Mike Bayer also provides a blog post for it [2].<br>
<br>
So this thing with the “django-style routers”, the way that example is, it actually would work poorly with a Session that is not in “autocommit” mode, assuming you’re working with regular old databases that are doing some simple behind-the-scenes replication. Because again, if you do a flush, those rows go to the master, if the transaction is still open, then reading from the slaves you won’t see the rows you just inserted. So in reality, that example is kind of crappy, if you’re in a transaction (which we are) you’d really need to be doing session.using_bind(“master”) all over the place, and that is already way too verbose and hardcoded. I’m wondering why I didn’t make a huge note of that in the post. The point of that article was more to show that hey, you *can* control it at this level if you want to but you need to know what you’re doing.<br>
<br>
Just to put it out there, this is what I think good high/level master/slave separation in the app level (reiterating: *if we want it in the app level at all*) should approximately look like:<br>
<br>
@transaction.writer<br>
def read_and_write_something(arg1, arg2, …):<br>
# …<br>
<br>
@transaction.reader<br>
def only_read_something(arg1, arg2, …):<br>
# …<br>
<br>
that way there is no awareness of master/slave anything, the underlying system can decide what “reader” and “writer” means. Do in-app switching between two databases, send out some magic signals to some commercial clustering service, have the “readers” work in “autocommit” mode, or do nothing, whatever. The code doesn’t decide this imperatively. But it isn’t 100% “transparent”, this small amount of declaration per-method is needed.<br>
<br>
<br>
><br>
> What I did is to re-implement it in OpenStack DB API modules in my development environment, using Galera cluster(causal-reads on). It has been running perfectly for more than a week. The routing session manager works well while maintaining data consistency.<br>
<br>
OK so Galera would perhaps have some way to make this happen, and that’s great. My understanding is that people are running Openstack already with Galera, that’s why we’re hitting issues with some of those SELECT..FOR UPDATEs that are being replaced with optimistic approaches as you mention. But beyond that this isn’t any kind of “change” to oslo.db or anything else. Run Openstack with whatever database backend you want, ideally (that is my primary agenda, sorry MySQL vendors!).<br>
<br>
<br>
> Finally, I think if we can integrate it into oslo.db, it is a perfect plus for those who would like to deploy Galera (or other similar technology) as DB backend.<br>
<br>
this (the word “integrate”, and what does that mean) is really the only thing making me nervous. If the integration here is the django blog post I have, it’s not going to work with transactions. Either the system is magical enough that a single transaction can read/write from both sources midway and there is no “integration” needed, or the transaction has to be declared up front as reader or writer. Or you don’t use transactions except for writers, which is essentially the same as “declaration up front”.<br>
<br>
><br>
> [1] <a href="http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#custom-vertical-partitioning" target="_blank">http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#custom-vertical-partitioning</a><br>
> [2] <a href="http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/" target="_blank">http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/</a><br>
> [3] Galera replication method: <a href="http://galeracluster.com/products/technology/" target="_blank">http://galeracluster.com/products/technology/</a><br>
><br>
><br>
> _______________________________________________<br>
> OpenStack-dev mailing list<br>
> <a href="mailto:OpenStack-dev@lists.openstack.org">OpenStack-dev@lists.openstack.org</a><br>
> <a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
<br>
<br>
_______________________________________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org">OpenStack-dev@lists.openstack.org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
<br>
_______________________________________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org">OpenStack-dev@lists.openstack.org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
</div></div></blockquote></div><br></div>