1. Python is Very , Very Slow compared to your database

Update – redditor Riddlerforce found valid issues with this section,
in that I was not testing over a network connection. Results here
are updated. The conclusion is the same, but not as hyperbolically
amusing as it was before.

Let’s first review asynchronous programming’s
sweet spot,
the I/O Bound application:

I/O Bound refers to a
condition in which the time it takes to complete a computation is
determined principally by the period spent waiting for
input/output operations to be completed. This circumstance
arises when the rate at which data is requested is slower than
the rate it is consumed or, in other words, more time is spent
requesting data than processing it.

A great misconception I seem to encounter often is the notion that communication
with the database takes up a majority of the time spent in a database-centric
Python application. This perhaps is a common wisdom in compiled languages
such as C or maybe even Java, but generally not in Python. Python is
very slow, compared to such systems; and while Pypy is certainly
a big help, the speed of Python is not nearly as fast as your database,
when dealing in terms of standard CRUD-style applications
(meaning: not running large OLAP-style queries, and of course assuming
relatively low network latencies).
As I worked up in my PyMySQL Evaluation
for Openstack, whether a database driver (DBAPI) is written in pure Python
or in C will incur significant additional Python-level overhead.
For just the DBAPI alone, this can be as much as an order of magnitude
slower. While network overhead will cause more balanced proportions
between CPU and IO, just the CPU time spent by Python driver itself still takes
up twice the time as the network IO, and that is without any additional database abstraction
libraries, business logic, or presentation logic in place.

This script, adapted from the
Openstack entry, illustrates a pretty straightforward set of INSERT and SELECT statements,
and virtually no Python code other than the barebones explicit calls into
the DBAPI.

MySQL-Python, a pure C DBAPI, runs it like the following over a network:

DBAPI (cProfile):  <module 'MySQLdb'>
     47503 function calls in 14.863 seconds
DBAPI (straight time):  <module 'MySQLdb'>, total seconds 12.962214

With PyMySQL, a pure-Python DBAPI,and a network connection we’re about 30%
slower:

DBAPI (cProfile):  <module 'pymysql'>
     23807673 function calls in 21.269 seconds
DBAPI (straight time):  <module 'pymysql'>, total seconds 17.699732

Running against a local database, PyMySQL is an order of magnitude slower
than MySQLdb:

DBAPI:  <module 'pymysql'>, total seconds 9.121727

DBAPI:  <module 'MySQLdb'>, total seconds 1.025674

To highlight the actual proportion of these runs that’s spent in IO,
the following two RunSnakeRun
displays illustrate how much time is actually for IO within the PyMySQL
run, both for local database as well as over a network connection.
The proportion is not as dramatic over a network connection, but in
that case network calls still only take 1/3rd of the total time; the other
2/3rds is spent in Python crunching the results.
Keep in mind this is just the DBAPI alone;
a real world application would have database abstraction layers, business
and presentation logic surrounding these calls as well:

Local connection – clearly not IO bound.

Network connection – not as dramatic, but still not IO bound
(8.7 sec of socket time vs. 24 sec for the overall execute)

Let’s be clear here, that when using Python, calls to your database, unless
you’re trying to make lots of complex analytical calls with enormous
result sets that you would normally not be doing in a high performing
application, or unless you have a very slow network, do not typically produce an
IO bound effect. When we talk to databases, we are almost always using some form of connection
pooling, so the overhead of connecting is already mitigated to a large extent;
the database itself can select and insert small numbers of rows very fast
on a reasonable network. The overhead of Python itself, just to marshal
messages over the wire and produce result sets, gives the CPU plenty
of work to do which removes any unique throughput advantages to be had
with non-blocking IO. With real-world activities based around database
operations, the proportion spent in CPU only increases.

2. AsyncIO uses appealing, but relatively inefficient Python paradigms

At the core of asyncio is that we are using the @asyncio.coroutine
decorator, which does some generator tricks in order to have your otherwise
synchronous looking function defer to other coroutines. Central to this
is the yield from technique, which causes the function to stop its
execution at that point, while other things go on until the event loop
comes back to that point.
This is a great idea, and it can also be done using the more common yield
statement as well. However, using yield from, we are able to maintain
at least the appearance of the presence of return values:

@asyncio.coroutine
def some_coroutine():
    conn = yield from db.connect()
    return conn

That syntax is fantastic, I like it a lot, but unfortunately, the mechanism
of that return conn statement is necessarily that it raises a StopIteration
exception. This, combined with the fact that each yield from call
more or less adds up to the overhead of an individual function call separately.
I tweeted a simple
demonstration of this, which I include here in abbreviated form:

def return_with_normal():
    """One function calls another normal function, which returns a value."""

    def foo():
        return 5

    def bar():
        f1 = foo()
        return f1

    return bar

def return_with_generator():
    """One function calls another coroutine-like function,
    which returns a value."""

    def decorate_to_return(fn):
        def decorate():
            it = fn()
            try:
                x = next(it)
            except StopIteration as y:
                return y.args[0]
        return decorate

    @decorate_to_return
    def foo():
        yield from range(0)
        return 5

    def bar():
        f1 = foo()
        return f1

    return bar

return_with_normal = return_with_normal()
return_with_generator = return_with_generator()

import timeit

print(timeit.timeit("return_with_generator()",
    "from __main__ import return_with_generator", number=10000000))
print(timeit.timeit("return_with_normal()",
    "from __main__ import return_with_normal", number=10000000))

The results we get are that the do-nothing yield from + StopIteration
take about six times longer:

yield from: 12.52761328802444
normal: 2.110536064952612

To which many people said to me, “so what? Your database call is much
more of the time spent”. Never minding that we’re not talking here about
an approach to optimize existing code, but to prevent making perfectly
fine code more slow than it already is. The PyMySQL example
should illustrate that Python overhead adds up very fast, even just within
a pure Python driver, and in the overall profile dwarfs the time spent
within the database itself. However, this argument still may not be
convincing enough.

So, I will here present
a comprehensive test suite which illustrates
traditional threads in Python against asyncio, as well as gevent style
nonblocking IO. We will use psycopg2 which
is currently the only production DBAPI that even supports async, in
conjunction with aiopg which adapts
psycopg2’s async support to asyncio and psycogreen
which adapts it to gevent.

The purpose of the test suite is to
load a few million rows into a Postgresql database as fast
as possible, while using the same general set of SQL instructions, such that
we can see if in fact the GIL slows us down so much that asyncio blows
right past us with ease. The suite can use any number of connections simultaneously;
at the highest I boosted it up to using 350 concurrent connections, which trust me,
will not make your DBA happy at all.

The results of several runs on different machines under different conditions
are summarized at the bottom of the README.
The best performance I could get was running the Python code on one laptop
interfacing to the Postgresql database on another, but in virtually every test
I ran, whether I ran just 15 threads/coroutines on my Mac, or 350 (!) threads/coroutines
on my Linux laptop, threaded code got the job done much faster than asyncio
in every case (including the 350 threads case, to my surprise), and usually
faster than gevent as well. Below are the results from running
120 threads/processes/connections on the Linux laptop networked to
the Postgresql database on a Mac laptop:

Python2.7.8 threads (22k r/sec, 22k r/sec)
Python3.4.1 threads (10k r/sec, 21k r/sec)
Python2.7.8 gevent (18k r/sec, 19k r/sec)
Python3.4.1 asyncio (8k r/sec, 10k r/sec)

Above, we see asyncio significantly slower for the first part of the
run (Python 3.4 seemed to have some issue here in both threaded and asyncio),
and for the second part, fully twice as slow compared to both Python2.7 and
Python3.4 interpreters using threads. Even running 350 concurrent
connections, which is way more than you’d usually ever want a single process
to run, asyncio could hardly approach the efficiency of threads. Even with
the very fast and pure C-code psycopg2 driver, just the overhead of the
aiopg library on top combined with the need for in-Python receipt of
polling results with psycopg2’s asynchronous library added more than enough
Python overhead to slow the script right down.

Remember, I wasn’t even trying to prove that asyncio is significantly slower
than threads; only that it wasn’t any faster. The results I got were more
dramatic than I expected. We see also that an extremely low-latency async approach,
e.g. that of gevent, is also slower than threads, but not by much, which
confirms first that async IO is definitely not faster in this scenario,
but also because asyncio is so much slower than gevent,
that it is in fact the in-Python overhead of asyncio’s coroutines
and other Python constructs that are likely adding up to very significant
additional latency on top of the latency of less efficient IO-based context
switching.

Database Code Handles Concurrency through ACID, Not In-Process Synchronization

Whether or not we’ve managed to use threaded code or coroutines with implicit
or explicit IO and find all the race conditions that would occur in our
process, that matters not at all if the thing we’re talking to is a relational
database, especially in today’s world where everything runs in clustered / horizontal /
distributed ways – the handwringing of academic theorists regarding the
non-deterministic nature of threads is just the tip of the iceberg; we need
to deal with entirely distinct processes, and regardless of what’s said,
non-determinism is here to stay.

For database code, you have exactly one technique
to use in order to assure correct concurrency, and that is by using ACID-oriented
constructs and techniques. These unfortunately don’t come magically
or via any known silver bullet, though there are great tools
that are designed to help steer you in the right direction.

All of the example transfer() functions above are incorrect from a
database perspective. Here is the correct one:

def transfer(amount, payer, payee, server):
    with transaction.begin():
        if not payer.sufficient_funds_for_withdrawal(amount, lock=True):
            raise InsufficientFunds()
        log("{payer} has sufficient funds.", payer=payer)
        payee.deposit(amount)
        log("{payee} received payment", payee=payee)
        payer.withdraw(amount)
        log("{payer} made payment", payer=payer)
        server.update_balances([payer, payee])

See the difference? Above, we use a transaction. To call upon the SELECT of the payer
funds and then modify them using autocommit would be totally wrong.
We then must ensure that we retrieve this value using some appropriate
system of locking, so that from the time that we read it, to the time that
we write it, it is not possible to change the value based on a stale
assumption. We’d probably use a SELECT .. FOR UPDATE to lock the row
we intend to update. Or, we might use “read committed” isolation in conjunction with a
version counter
for an optimistic approach, so that our function fails if a race condition
occurs. But in no way does the fact that we’re using
threads, greenlets, or whatever concurrency mechanism in our single process
have any impact on what strategy we use here; our concurrency concerns involve
the interaction of entirely separate processes.