PyMySQL evaluation

2015-01-12T23:52:39Z

Mike Bayer:

2014-06-30T17:24:33Z

Mike Bayer: Mike Bayer moved page Openstack and SQLAlchemy to OpenStack and SQLAlchemy: spelling for OpenStack

#REDIRECT [[OpenStack and SQLAlchemy]]

OpenStack and SQLAlchemy

2014-06-30T17:23:58Z

Mike Bayer: Openstack -> OpenStack

= OpenStack and SQLAlchemy =

This page describes the ongoing efforts of Mike Bayer, creator/maintainer of
SQLAlchemy, Alembic Migrations, and Dogpile caching, to spearhead what is
essentially an "OpenStack SQLAlchemy 2.0" effort - as I am now full-time
employed by an OpenStack vendor (Red Hat), I've been hired to produce a strong
and sustained pass through OpenStack's relational database integration,
improving areas that have been problematic and establishing a very strong
set of practices that are specific to OpenStack's needs.

Within this effort, I'd like to stress that from my POV, everything is
on the table, including new improvements / fixes / features to
SQLAlchemy and Alembic directly, migration of projects for which
relational databases aren't suitable to non-relational systems,
improvement of relational schemas on the OpenStack side, and of course
improvement of exising relational patterns within OpenStack
applications, for which oslo.db will serve as the foundation.

As of June 30, 2014, I've been on the job for about a month, so below
is an outline of most of what I've come across and my thoughts going forward.

My goal here is to share with the community what I've seen and done so far,
so that interested parties can chime in with further advice and information,
and most importantly to help steer me towards the initial blueprints that
I should be starting on, that is, which features are most likely to be
accepted by the community as well as which are the top priority. Not to mention, if you're looking
for something to do, you can help me implement some of these things!

== OpenStack presence within SQLAlchemy's CI environment (a.k.a. "The Version that shall not be Named") ==

On Monday, June 23, I got up in the AM with two goals: get a release of SQLAlchemy
out the door, as it had been several months since the last point release, and then
do some work learning how Nova's unit tests run and function.

Release 0.9.5 of SQLAlchemy was not particularly different than any of the other
hundred or so point releases I've put out over nine years, it was a little more overdue
than some, and out it went. Later that day, as I was learning to run Nova's test suite, I
was hitting some failures within the FixedIp/FloatingIp tests for which I couldn't
find the cause, assuming that I just wasn't setting up my environment correctly,
as Nova's tests are passing for all the Jenkins servers the vast majority of the time.

As we learned within about 30 minutes of my hitting these problems, it
turns out 0.9.5 had a bad fix in it; one that didn't fail any of
SQLAlchemy's 5000 tests but hit a use case within Nova that was not
very unusual; not the kind of use I usually do myself but nonetheless
something that was pretty common. IRC began jumping that builds
were failing everywhere and it was due to the 0.9.5 release! The
bizarre coiincidence of this was that none of the thirty-odd point
releases of SQLAlchemy released under OpenStack's lifetime have ever
really had this effect, only the major jumps saw any issues.

Luckily, as I had just learned 30 minutes earlier how to run Nova's tests I was
able to drill down to exactly what was going on and SQLAlchemy 0.9.6 was out
within an hour, but we shall now forever see in requirements.txt, the Version
that shall Not Be Named (except in requirements.txt):

<code>
SQLAlchemy>=0.7.8,!=0.9.5,<=0.9.99
</code>

In response to this I have now
[http://jenkins.sqlalchemy.org/job/openstack_sqla_master/ added openstack builds to our Jenkins CI system],
so far running tests for oslo.db and the SQLAlchemy tests within Nova; I spent
a couple of days figuring out how to get everything to build and run against
specific versions of SQLAlchemy and Alembic, even versions above the 0.9.99 in
all the requirements files as we are set for 1.0 hopefully within the next year.
I will be adding in as many other OpenStack projects as I can here so that we will no
longer have a SQLAlchemy release that hasn't been run against a battery of
basic OpenStack tests.

== ORM Woes ==

After chatting with a decent amount of folks for a month, I can break up
the take on OpenStack / SQLAlchemy ORM into three camps:

1. We love SQLAlchemy ORM, we know that we can improve how we use it and we're looking forward to getting there (oslo.db, Keystone)

2. Our app uses a relational database but the SQLAlchemy ORM slows us down, we already have our own "orm-like" system (e.g. APIs that shuttle rows into "values" dictionaries) so we want to ultimately get rid of the ORM part, and probably continue using SQLAlchemy Core to some degree (some Nova developers I've spoken to).

3. Our application has a SQLAlchemy backend that almost nobody uses in production, most users use MongoDB, and the SQLAlchemy backend is only there because MongoDB is AGPL (Ceilometer, Marconi). We'd like to get a better NoSQL backend without Mongo's licensing issue and do away with relational storage entirely ([http://lists.openstack.org/pipermail/openstack-dev/2014-June/038778.html Marconi]).

Here's my observations / thoughts about these three camps:

1. Great! Read on, I have some things we can start with.

2. 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.

As a secondary issue, some devs I've spoken to have referred to the nature
of OpenStack API-based applications being a part of the problem.
This refers to the fact that OpenStack apps like Nova
and Neutron expose fine-grained APIs which end up
breaking out composed use cases into dozens or hundreds of small API
operations, each of which invokes within it's own database transaction and
ORM session state. This makes it impossible, without introducing some elaborate
offline state management system, for a larger operation to be
performed so that larger groups of objects can be loaded and manipulated
at once, greatly decreasing the number of database round trips present.
No matter what ORM you use or don't use, the pattern of many small round trips
is difficult to scale on relational databases.

There are under way projects which aim to apply detailed profiling
to OpenStack applications so that a breakdown of performance issues due
to API use and/or database
access might be plainly reasoned about; see [https://github.com/stackforge/rally Rally]
and [https://github.com/stackforge/osprofiler OS Profiler] for examples.

On my end, I have no intention of focusing on this second issue for the time
being, I'm going to stick with leaving OS applications as much as they are right now as possible,
applying iterative changes to database access code which I hope can optimize
to a significant degree within the current usage patterns.

3. Projects with alternate MongoDB / MySQL backends (since after all, MySQL is the database here being rejected, SQLAlchemy is just a Python API) should probably do what they're doing and go to an all key/value model. It is not at all fun to maintain the same datamodel and API against both a KV store as well as relational tables. I had a detailed look at Ceilometer with its team members early on, and while I'm pretty confident that if it were desireable, I could get it's relational backend to compete with the Mongo backend performance-wise, there's no reason to get into this if the Mongo licensing issue can be solved alone.

== ORM Quick Wins Proof of Concept ==

I've built up a proof of concept at [https://github.com/zzzeek/nova_poc nova_poc] which seeks to demonstrate
the range of database and ORM performance that can be achieved within
one particular method of the Nova API. Naturally, I picked an example that
is very juicy in this regard; it has a very easily fixable issue with the
kind of query it emits that has an enormous 10x impact on its performance
(which I feel is worth illustrating just for how dramatic it is),
and it then illustrates some new SQLAlchemy extensions that more or less
may be going into SQLAlchemy directly, which allow the remaining operation
to complete in less than half the time. These extensions can be ported to
oslo.db to work against all supported SQLAlchemy versions prior to the official
release of these features.

To summarize, we're looking at the API method <code>floating_ip_update()</code>, and
I run a series of profiles against a database table 10K rows (e.g. <code>FloatingIp</code>
objects), where each <code>FloatingIp</code> refers to a single <code>FixedIp</code> that then
refers to an <code>Instance</code>. The profiling focuses on the nature of this
method as an API feature all it's own, and simulates the case where the API
method is called thousands of times, not unlike other API methods that seek
to add some small amount of data each time they are called.

=== Eager load and Column load tuning ===

Turning off an eagerload in the <code>_floating_ip_get_by_address()</code> function
is the most dramatic win; while removing this method seems like a reasonable
fix to remove some unneeded overhead, in profiling it shows itself to
be enormous:

<pre>
Running scenario default
Scenario default, total calls for 1000 operations: 24543047
Scenario default, total time for 10000 operations: 222

Running scenario default_optimized
Scenario default_optimized, total calls for 1000 operations: 1881626
Scenario default_optimized, total time for 10000 operations: 25
</pre>

While not illustrated in the POC code itself, talk with various OpenStack
devs has revealed that we could probably do a lot more individual column
fetching as well, that is instead of:

<pre>
session.query(FixedIp).all()
</pre>

We say:

<pre>
session.query(FixedIp.address, FixedIp.host, FixedIp.reserved).all()
</pre>

By loading indivdual columns, we get read-only objects that are not identity
managed, bypassing a huge amount of work that is done on instances both
in instantiating them, populating relationships and doing bookkeeping.
Any API feature that only returns a simple read-only object or a "values"
dictionary that does not rely upon relationship loading should be using
this pattern, or similar, by using a
[http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#column-bundles Column Bundle].
I will be exploring adding the following feature to oslo.db:

<pre>
session.query(FixedIp.bundle).all()
</pre>

The above will return a fast-loading, read-only Bundle object that otherwise
expresses the full interface of <code>FixedIp</code>. Relationships might be tricky
here but I can at least get them on board as regular lazy loads (just like
any other ORM would do anyway).

=== Fast Object Save ===

We explore replacing the unit of work <code>flush()</code> call used by <code>object.save()</code> within oslo.db-style applications with a more direct "flush single object" call; this grants us only a 12% savings so far, though it is very simple to use:

<pre>
Running scenario default_optimized
Scenario default_optimized, total calls for 1000 operations: 1881626
Scenario default_optimized, total time for 10000 operations: 25

Running scenario fast_save
Scenario fast_save, total calls for 1000 operations: 1685221
Scenario fast_save, total time for 10000 operations: 22
</pre>

=== Baked Queries ===

Something that has been in the works for a long time and has
recently seen lots of work in the past months is the "baked query"
feature; this pattern is ideal for OpenStack's "many short queries"
pattern, and allows caching of the generation of SQL. Recent
versions of this pattern have gotten very slick, and can cache
virtually everything that happens Python-wise from the construction
of the <code>Query</code> object, to calling all the methods on the
query, to the query-objects construction of a Core SQL statement,
to the compilation of that statement as a string - all of these steps
are removed from the call-graph after the first such call. In
SQLAlchemy 1.0 I've also thrown in the construction of column
metadata from the result set too. The pattern involves a bit more
verbosity to that of constructing a query, where here I've built
off of some of the ideas of the Pony ORM to use Python function
information as the source of a cache key. A query such as:

<pre>
result = model_query(
context, models.FloatingIp, session=session).\
filter_by(address=address)
</pre>

would be expressed in "baked" form as:

<pre>
# note model_query is using the "baked" process internally as well
result = model_query(context, models.FloatingIp, session=session)

result.bake(lambda query:
query.filter_by(
address=bindparam('address'))).params(address=address)
</pre>

In the above form, everything within each lambda is invoked only once,
the result of which becomes part of a cached value.

For this slight increase in verbosity, we get an improvement like this:

<pre>
Running scenario default_optimized
Scenario default_optimized, total calls for 1000 operations: 1881626
Scenario default_optimized, total time for 10000 operations: 25

Running scenario baked
Scenario baked, total calls for 1000 operations: 1052935
Scenario baked, total time for 10000 operations: 16
</pre>

That is, around a 40% improvement.

Putting together both "fast save" plus "baked" we get down to a full 50%
improvement vs. the plain optimized version:

<pre>
Running scenario fast_save_plus_baked
Scenario fast_save_plus_baked, total calls for 1000 operations: 856035
Scenario fast_save_plus_baked, total time for 10000 operations: 13
</pre>

13 seconds for 10K operations vs. 222 seconds in the current code, I hope
I've established that there is hope!

== Migrations ==

Migrations are a huge deal. Here's where it looks like this is going.

Alembic - "Alembic is the defacto standard going forward" - ultimately
we want to get everything SQLAlchemy-Migrate onto Alembic. And in talking
with Nova devs, they really like that they can test their migrations against
SQLite. So from my POV, SQLite migrations on Alembic, we're doing that!
Unless folks think otherwise.

=== SQLite Support ===

SQLite has extremely limited support for ALTER TABLE, essentially only being able
to add new columns, and that's about it. In order to provide the full suite
of ALTER operations that all other databases provide, tools such as
SQLAlchemy-Migrate will create a copy of the target table with changes applied,
copy data from old table to new, then drop the old table and rename the new one.
I've always been peeved at SQLite's insistence on never providing some better
pathway to ALTER built in. But it has been on the Alembic roadmap to add SQLite migrations
in a style similar to that of Migrate for a long time, emulating this same
approach in some way. Alembic will have this!

However in talking with some folks, it appears like some people might actually
like this "make a new table and switch it" approach for other databases too,
as a way to work around locking of tables. I don't know that I agree with this
long term, the "ALTER locks the database!" issue in my experience has always
been kind of a MySQL problem alone, but it can be present for other backends
as well.

So the API I'm looking towards here is going to try to solve a few problems;
one is that, copying the whole table to a new one each time for *every single
column* is crazy, and also that the technique we use on SQLite might be nice
for other databases too, so here's the API:

<pre>
with op.batch_alter("mytable", recreate=True) as batch_op:
batch_op.drop_column("some_column")
batch_op.alter_column("q", type=Integer)
batch_op.add_column(Column('y', String(50), nullable=False))
</pre>

That is, we assign a whole series of column operations under the umbrella of
one table, and they are created at once.

The <code>batch_alter()</code> call can even be conditional, dropping into traditional
migration behavior for all backends except SQLite and whatever else:

<pre>
with op.batch_alter("mytable", recreate_on=('sqlite',)) as batch_op:
batch_op.drop_column("some_column")
batch_op.alter_column("q", type=Integer)
batch_op.add_column(Column('y', String(50), nullable=False))
</pre>

SQlite-compatible alterations is [https://bitbucket.org/zzzeek/alembic/issue/21/column-renames-not-supported-on-sqlite issue #21].

=== Finishing up Autogenerate ===

I started writing Alembic long before SQLAlchemy-Migrate had autogenerate. But by the time
I got Alembic released and later autogenerate working, Migrate already had it.
Here's what we need to finish up in autogenerate:

1. <code>ForeignKeyConstraint</code> change detection - this is an entirely straightforward feature add and has long been on the todo list.

2. MySQL implicit indexes - this is actually done; recent versions of Alembic can navigate around MySQLs goofy production of indexes automatically on foreign key columns, and not accidentally spit them out in autogenerates. It was seriously tough to get index autogen mostly working on all backends, so new issues will continue to be fixed as they are reported.

3. Type comparison - Alembic balks on type comparison by default, because ultimately SQLAlchemy should add comparision features to its type objects natively. However, on Alembic backends like that of MySQL, we can add explicit rules for the very common and annoying ones, namely the detection of MySQL's TINYINT as a SQLAlchemy Boolean. Alembic already allows user-specified rules in this regard, so they can be part of oslo.db in any case, however Alembic can also add this rule and others in directly.

4. Table order - Alembic's autogenerate should be spitting out table creates and drops in the order of foreign key dependency, though we don't have good test coverage for this yet and it might not be working. We'll fix that, no biggie! Alternatively, Alembic could include a mode of operation that includes all the ForeignKeyConstraint objects after all the tables. Input on how we'd like to see this work would be welcome.

=== New Migration Features ===

There's a lot of features I'd like to add to Alembic, and if OpenStack has
a need for them, that would justify the effort:

1. Full branch support - [https://bitbucket.org/zzzeek/alembic/issue/167/multiple-heads-branch-resolution-support issue 167 multiple heads / branch resolution support] has a detailed architecture laid out for this feature. Lots of people are looking for it. This would turn Alembic's current "linked list" version model into a full directed acyclic graph (DAG). Any particular migration can be dependent on any other group of migrations, or none at all; individual branches can be maintained and upgraded along their path, or automatically merged.

2. [https://bitbucket.org/zzzeek/alembic/issue/124/multiple-versions-directories Multiple version directories] - This would allow migration files to be present in more than one place for a single migrations environment. Currently, you can get this approach by using multiple base directories, but that requires separate <code>env.py</code> files for each. With this issue, cross-dependent migration files can live in multiple places, working nicely with the multiple heads support of #167.

== Testing ==

I spent quite a bit looking into testing. The main thing we can see
in testing is that there may be a desire for "transactional testing";
run tests within a fixed schema, that is the schema is created
once, many tests are run, each one rolling back their work, the
schema is only dropped once all tests for that schema are complete.

Transactional testing is described in the SQLAlchemy docs and in some of
my talks; for OpenStack, we need the version that supports graceful handling
of tests that actually do a rollback. This is doable and the
recipe for that is currently [https://gist.github.com/zzzeek/8443477 a github gist].

In order to accommodate parallelized runs, the system will create N
temporary schemas for N concurrent runs.

I really want to get into this feature but as I talk to people, I'm
getting mixed signals on how important this feature is. So far I've
only worked with unit tests, which seem to be entirely against SQLite
in-memory databases, and I haven't gotten my head around to what
extent other databases besides SQLite might be desireable in unit
tests, nor have I gotten to fully understand how integration tests
run, so input is welcome here.

== Improvement of ORM Patterns ==

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 ===

For the oft-repeated "get_session(); with session.begin(subtransactions=True)" phrase and other variants, I'd like to move that into something more abstracted:

<pre>
def my_api_thing(x):
with transactional() as session:
# do stuff
</pre>

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.

=== Exception Rewriting ===

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.

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

This issue has been coming up for some projects, that they make sometimes great use of SQLAlchemy's <code>with_lockmode()</code> method, which ultimately emits SELECT..FOR UPDATE, so that selected rows are aggressively locked within a transaction. The issue is that backends like Galera don't support FOR UPDATE, meaning the locking that is desired is not actually provided.

While I'm not sure that there's a generalized solution to this, in some cases I've been helping developers move to more of an "optimistic" approach, where a particular UPDATE statement targets some kind of timestamp or value counter in the row, then counts the number of rows matched. If the number of rows matched is not what's expected, it is assumed that the UPDATE failed to match the row, and the operation can either try again or can bail out. This approach might not be suitable in all cases. In cases where a large stream of data is affecting a relatively small set of rows, it can perhaps be feasible to serialize those updates by sending them all to a queue, so that they are invoked in isolation without the need for up-front locking. For very high throughput situations, queues can be constructed to use multiple, concurrent UPDATE streams separated via hashing, or can batch together updates for particular rows over a period of time and emit fewer UPDATE statements than if each source of data did it individually.

=== Encouraging Bundle / Baked Query Use ===

Encouraging Bundle use - as stated earlier, I want to look into bringing the very efficient [http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#column-bundles Column Bundle] pattern into the fold for mainstream use, not to mention the baked query pattern illustrated previously.

=== Transparent Retries of Transactions ===

I have ready to go an [https://bitbucket.org/zzzeek/sqlalchemy/issue/3104/transaction-replay-extension already in production extension for transaction replaying], developed for a client with the agreement that the extension is open source. This extension installs as an event handler using a new series of hooks that were added in 0.9, or alternatively using a series of compatibility hooks that were backported to 0.7 and 0.8. This extension allows logging of SQL statements as they proceed within a transaction, and allows them to be transparently replayed when a database disconnect error occurs. This means that SQLAlchemy's usual behavior of raising an exception and forcing you to re-try explicitly can be hidden so that it occurs implicitly, without any interruption or special error handling required in application code. The extension includes a lot of configuration options, such as what statements should be replayed and which shouldn't, as well as what exceptions should be handled this way. Transaction replaying is an elaborate feature which might introduce lots of new edge cases, namely that the state of the database may have changed since the last transaction failed, so to that extent I'm a little wary of this feature myself. However I have seen lots of messages alluding to this kind of thing. The mechanics to pull this into SQLAlchemy as a transparent feature are ready to go, if this is a feature people are looking for (I'm not sure if people are looking for this).

== Contributors ==

I would be remiss if I didn't mention that SQLAlchemy, Alembic, Dogpile and everything else I do are collaborative projects, and while SQLAlchemy in particular might present a daunting codebase, we would love to get new collaborators on board! Any of the features above are fair game and I can walk someone through the steps to be taken, and additionally everything we add features for always needs lots of tests and review, if you're looking for a way to get into the project I can bring you right in, there's lots of (often very tedious and boring) work to be done. I'm on all the IRC channels now as zzzeek so ping away.

== Coffee and Cake ==

So! I hope you've enjoyed reading this document, and whether you're a fan or a hater, please know that I'm on board now and I'm here to help, with whatever database kind of thing you want to do - it's all part of my job now. If we wanted to use/build a whole new ORM, I'd be doing that too right alongside. OpenStack's lessons in database integration are now my lessons too. So let me know where I should dig in first! Have a save drive home.