OpenStack and SQLAlchemy - Revision history

Mike Bayer: /* Transaction Demarcation */

2014-10-10T18:28:02Z

‎Transaction Demarcation

Mike Bayer: /* Fast Object Save */

2014-08-14T19:44:09Z

‎Fast Object Save

Mike Bayer: /* Improvement of ORM Patterns */

2014-07-10T17:59:23Z

‎Improvement of ORM Patterns

Mike Bayer: /* Exception Rewriting */

2014-07-10T16:57:29Z

‎Exception Rewriting

Mike Bayer at 16:50, 7 July 2014

2014-07-07T16:50:45Z

Mike Bayer: /* MySQLdb + eventlet = sad */

2014-07-03T02:06:58Z

‎MySQLdb + eventlet = sad

Mike Bayer: /* MySQLdb + eventlet = sad */

2014-07-03T01:20:26Z

‎MySQLdb + eventlet = sad

Mike Bayer: /* MySQLdb + eventlet = sad */

2014-07-03T01:20:01Z

‎MySQLdb + eventlet = sad

Mike Bayer at 01:18, 3 July 2014

2014-07-03T01:18:40Z

Mike Bayer at 01:10, 1 July 2014

2014-07-01T01:10:31Z

← Older revision		Revision as of 18:28, 10 October 2014
Line 395:		Line 395:

	I want context managers and/or decorators that are oslo.db specific to replace any kind of containing code that deals with transactions or sessions. That way the containinment logic can be maintained in once place and configured separately from the business logic contained within. Projects like Keystone are already using a form more like this; it would be nice to nail down a consistent pattern and get it into oslo.db for everyone to use.		I want context managers and/or decorators that are oslo.db specific to replace any kind of containing code that deals with transactions or sessions. That way the containinment logic can be maintained in once place and configured separately from the business logic contained within. Projects like Keystone are already using a form more like this; it would be nice to nail down a consistent pattern and get it into oslo.db for everyone to use.
		+
		+	* https://review.openstack.org/#/c/125181/ - Make EngineFacade a Facade!

	=== MySQLdb + eventlet = sad ===		=== MySQLdb + eventlet = sad ===

@@ Line 200: / Line 200: @@
 Scenario fast_save, total time for 10000 operations: 22
 </pre>
 === Baked Queries ===

@@ Line 374: / Line 374: @@
 Looking around oslo.db and other apps, I want to improve upon
 some kinds of verbosity I see, which make the code more repetetive and inconsistent, welds together application-level and business level concepts (e.g. transaction control and exception reporting melded with data manipulation) and makes it harder to reason about how the application interacts with sessions, as there are a variety of ad-hoc patterns present.
 === Transaction Demarcation ===

← Older revision		Revision as of 16:57, 10 July 2014
Line 394:		Line 394:

	Exception rewriting - I notice that the oslo.db pattern includes that DBAPI level exceptions are re-thrown as OpenStack specific; I'd like to move this into handling using SQLAlchemy events. The [http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#sqlalchemy.events.ConnectionEvents.dbapi_error dbapi_error] event goes all the way back to 0.7, and [https://bitbucket.org/zzzeek/sqlalchemy/issue/3076/support-reraise-of-custom-exceptions ticket 3076] will allow it to be used to reraise with a new exception. For older SQLAlchemy versions, I can create a wrapper that will allow it to be used in this way safely for older versions, despite the warnings stated on that document.		Exception rewriting - I notice that the oslo.db pattern includes that DBAPI level exceptions are re-thrown as OpenStack specific; I'd like to move this into handling using SQLAlchemy events. The [http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#sqlalchemy.events.ConnectionEvents.dbapi_error dbapi_error] event goes all the way back to 0.7, and [https://bitbucket.org/zzzeek/sqlalchemy/issue/3076/support-reraise-of-custom-exceptions ticket 3076] will allow it to be used to reraise with a new exception. For older SQLAlchemy versions, I can create a wrapper that will allow it to be used in this way safely for older versions, despite the warnings stated on that document.
		+
		+	* blueprint: https://review.openstack.org/#/c/105306/

	=== Deleted Object Selecting ===		=== Deleted Object Selecting ===

← Older revision		Revision as of 16:50, 7 July 2014
Line 394:		Line 394:

	Exception rewriting - I notice that the oslo.db pattern includes that DBAPI level exceptions are re-thrown as OpenStack specific; I'd like to move this into handling using SQLAlchemy events. The [http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#sqlalchemy.events.ConnectionEvents.dbapi_error dbapi_error] event goes all the way back to 0.7, and [https://bitbucket.org/zzzeek/sqlalchemy/issue/3076/support-reraise-of-custom-exceptions ticket 3076] will allow it to be used to reraise with a new exception. For older SQLAlchemy versions, I can create a wrapper that will allow it to be used in this way safely for older versions, despite the warnings stated on that document.		Exception rewriting - I notice that the oslo.db pattern includes that DBAPI level exceptions are re-thrown as OpenStack specific; I'd like to move this into handling using SQLAlchemy events. The [http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#sqlalchemy.events.ConnectionEvents.dbapi_error dbapi_error] event goes all the way back to 0.7, and [https://bitbucket.org/zzzeek/sqlalchemy/issue/3076/support-reraise-of-custom-exceptions ticket 3076] will allow it to be used to reraise with a new exception. For older SQLAlchemy versions, I can create a wrapper that will allow it to be used in this way safely for older versions, despite the warnings stated on that document.
		+
		+	=== Deleted Object Selecting ===
		+
		+	There's a need to be able to run queries that automatically de-select for certain rows, particularly rows that have a "deleted" flag set, or that otherwise include various identifiers that are given for a context. Right now oslo.db has a function `model_query()` for this purpose. I'd like to streamline `model_query()` and allow for a more flexible pattern, where instead of plainly adding WHERE clauses to a SELECT against a primary table, we can use rules to produce a SQL transformation that will apply the desired criteria to all "FROM" clauses at the point at which they are named, even within a more complex, composed query. This will allow `model_query()` to be more flexible and ultimately to allow its behavior to be more transparently integrated into the app.

	=== Pessimistic Locking - SELECT FOR UPDATE ===		=== Pessimistic Locking - SELECT FOR UPDATE ===

@@ Line 389: / Line 389: @@
 === MySQLdb + eventlet = sad ===
-This is the basic thing that happens with these two actors.   We're in an eventlet "thread".   The code uses MySQLdb to do something ordinary that locks a row in a table - this can happen with a SELECT FOR UPDATE but it really can happen just on any old UPDATE too depending on isolation level (see https://review.openstack.org/#/c/104111/).   If the eventlet "thread" then does *anything* that causes it to defer out, another eventlet thread might run, and if that thread tries to work with MySQLdb and lock the same row, you're done, it's a deadlock.   The simple reason is, MySQLdb doesn't integrate with eventlet, so a simple thing like blocking on a transactional lock, it doesn't defer.   MySQL times out on these so we're getting exceptions instead of permanent freezes, but IMHO this is a really silly problem.  Eventlet and MySQLdb do not work together, we're done.   MySQLdb 1000% either has to go, or be fixed, even if that means forking it.    A good blog post that illustrates both scenarios is [http://www.diamondtin.com/2014/sqlalchemy-gevent-mysql-python-drivers-comparison/ here] - in this post, the author has used "greenify" to patch MySQLdb's blocking, but without this change (which I'm told isn't compatible with eventlet?), MySQLdb is a nonstarter.    Because it is in pure C, it is faster than MySQL-connector when it's been patched, but if it hasn't, it's dog slow.  MySQL-connector IMHO is probably the driver we should get behind [http://dev.mysql.com/doc/connector-python/en/index.html because it's the official MySQL driver]. The various workarounds and hacks I've seen proposed are all technically viable but IMHO totally unnecessary.   OpenStack should be serious about this, it's a totally solvable problem, let's just solve it.
+This is the basic thing that happens with these two actors.   We're in an eventlet "thread".   The code uses MySQLdb to do something ordinary that locks a row in a table - this can happen with a SELECT FOR UPDATE but it really can happen just on any old UPDATE too depending on isolation level (see https://review.openstack.org/#/c/104111/).   If the eventlet "thread" then does *anything* that causes it to defer out, another eventlet thread might run, and if that thread tries to work with MySQLdb and lock the same row, you're done, it's a deadlock.   The simple reason is, MySQLdb doesn't integrate with eventlet, so a simple thing like blocking on a transactional lock, it doesn't defer.   MySQL times out on these so we're getting exceptions instead of permanent freezes, but IMHO this is a really silly problem.  Eventlet and MySQLdb do not work together, we're done.   MySQLdb 1000% either has to go, or be fixed, even if that means forking it.    A good blog post that illustrates both scenarios is [http://www.diamondtin.com/2014/sqlalchemy-gevent-mysql-python-drivers-comparison/ here] - in this post, the author has used "greenify" to patch MySQLdb's blocking, but without this change (which I'm told isn't compatible with eventlet?), MySQLdb is a nonstarter.    Because it is in pure C, it is faster than MySQL-connector when it's been patched, but if it hasn't, it's dog slow.  MySQL-connector IMHO is probably the driver we should get behind [http://dev.mysql.com/doc/connector-python/en/index.html because it's the official MySQL driver]. The various workarounds and hacks (which I guess is why this is under "ORM patterns") I've seen proposed are all technically viable but IMHO totally unnecessary.   OpenStack should be serious about this, it's a totally solvable problem, let's just solve it.
 === Exception Rewriting ===

← Older revision		Revision as of 01:18, 3 July 2014
Line 386:		Line 386:

	I want context managers and/or decorators that are oslo.db specific to replace any kind of containing code that deals with transactions or sessions. That way the containinment logic can be maintained in once place and configured separately from the business logic contained within. Projects like Keystone are already using a form more like this; it would be nice to nail down a consistent pattern and get it into oslo.db for everyone to use.		I want context managers and/or decorators that are oslo.db specific to replace any kind of containing code that deals with transactions or sessions. That way the containinment logic can be maintained in once place and configured separately from the business logic contained within. Projects like Keystone are already using a form more like this; it would be nice to nail down a consistent pattern and get it into oslo.db for everyone to use.
		+
		+	=== MySQLdb + eventlet = sad ===
		+
		+	This is the basic thing that happens with these two actors. We're in an eventlet "thread". The code uses MySQLdb to do something ordinary that locks a row in a table - this can happen with a SELECT FOR UPDATE but it really can happen just on any old UPDATE too depending on isolation level. If the eventlet "thread" then does anything that causes it to defer out, another eventlet thread might run, and if that thread tries to work with MySQLdb and lock the same row, you're done, it's a deadlock. The simple reason is, MySQLdb doesn't integrate with eventlet, so a simple thing like blocking on a table, it doesn't defer. MySQL times out on these so we're getting exceptions instead of permanent freezes, but IMHO this is a really silly problem. Eventlet and MySQLdb do not work together, we're done. MySQLdb 1000% either has to go, or be fixed, even if that means forking it. A good blog post that illustrates both scenarios is [http://www.diamondtin.com/2014/sqlalchemy-gevent-mysql-python-drivers-comparison/ here] - in this post, the author has used "greenify" to patch MySQLdb's blocking, but without this change (which I'm told isn't compatible with eventlet?), MySQLdb is a nonstarter. Because it is in pure C, it is faster than MySQL-connector when it's been patched, but if it hasn't, it's dog slow. MySQL-connector IMHO is probably the driver we should get behind [http://dev.mysql.com/doc/connector-python/en/index.html because it's the official MySQL driver]. The various workarounds and hacks I've seen proposed are all technically viable but IMHO totally unnecessary. OpenStack should be serious about this, it's a totally solvable problem, let's just solve it.

	=== Exception Rewriting ===		=== Exception Rewriting ===

@@ Line 88: / Line 88: @@
 . I agree that a lot of the patterns that I've seen in Nova in particular are not well-suited to the approach used by SQLAlchemy's ORM, however I have observed that the applications still rely a lot on very sophisticated and mature features provided by SQLAlchemy (namely <code>relationship()</code> and it's integration with eager loading and the unit of work features), and throwing it away entirely will incur a lot of reinvention, not just of the features that were lost but also of a totally new crop of stability and performance issues to solve.
-To that degree, I'd like to propose a twist to the "we want to write our own ORM" idea and say, "let's write our own ORM that's based on the SQLAlchemy ORM primitives", e.g. a hybrid approach where existing ORM logic that is fine can stay that way, ORM logic that has performance or behavioral issues can be reorganized using systems, some of them new, some of them just underutilized, that bypass unneeded SQLAlchemy features on a case-by-case basis.
+To that degree, I'd like to propose a twist to the "we want to write our own ORM" idea (edit: OK nova.objects is really an ORM-like thing on top of their API on top of relational DB, but that's an issue for another day) and say, "let's write our own ORM that's based on the SQLAlchemy ORM primitives", e.g. a hybrid approach where existing ORM logic that is fine can stay that way, ORM logic that has performance or behavioral issues can be reorganized using systems, some of them new, some of them just underutilized, that bypass unneeded SQLAlchemy features on a case-by-case basis.
 As a secondary issue, some devs I've spoken to have referred to the nature