Skip to main content

Python: Asynchronous Networking APIs and MySQL

In my talk on Python concurrency, I talked about the multiple different approaches to asynchronous network programming in the Python world. I showed Twisted and Tornado Web, and then I compared them to stackless Python and gevent. Toward the end of the talk, I covered how MySQL plays into all this.

In the Python world, we usually use a MySQL driver that is a Python binding to a low-level driver written in C. The low-level driver written in C isn't written to use Python's socket module in any way. In fact, it has nothing to do with Python. Hence, it's impossible to take Twisted or Tornado Web and have them make the low-level driver behave asynchronously.

That's a problem. If you go to all the trouble of making your code asynchronous, it sucks to have one request that is held up on a long query block all the requests in your process. People handle this in different ways.

The Twisted guys can put the MySQL driver on its own thread. You might have 100 requests being handled by Twisted, and 10 threads devoted to MySQL connections.

The Tornado Web guys (from what I can tell) handle it differently. They allow MySQL queries to block the entire process. However, they compensate in two ways. They lean heavily on their asynchronous web client wherever possible. They also make use of multiple Python processes. Hence, if you're handling 500 simultaneous request, you might use nginx to split them among 10 different Tornado Web processes. Each process is handling 50 simultaneous requests. If one of those requests needs to make a call to the database, only 50 (instead of 500) requests are blocked.

At lolapps, they use Tornado Web too (at least for certain things). Their approach is to lean heavily on memcached and avoid talking to MySQL in real time whenever possible.

At IronPort, Sam Rushing (who's now working on a cool project called Irken) wrote a custom MySQL driver in Python. It was built with his proprietary version of stackless Python in mind, and hence it was asynchronous.

I think the same thing should be possible in the gevent world. MySQL Connector/Python is a MySQL driver written in pure Python. Since gevent permits you to use asynchronous networking APIs without forcing you to use continuation-passing style, it's possible that gevent could be used with MySQL Connector/Python to make your MySQL queries asynchronous too. Thank goodness for greenlet!

This is a topic I feel strongly about. I remember in the early days of Java, the JVM supported kernel threads as well as green threads. Some people would use green threads and then do multi-threaded programming with blocking socket APIs. What a lot of them didn't realize is that with green threads, if you call a synchronous networking API, you're blocking all your threads, not just one of them. Similarly, I feel strongly that if you're going to all the trouble of using an asynchronous web framework, your MySQL queries should be asynchronous too.

Comments

jjinux said…
turingpup said...

Hey JJ,

Last night I managed to accomplish what I set out to do: I strapped gevent onto the side of my tornado project and replaced the underlying db drivers with pg8000 (which is 100% python code) and upgraded sqlalchemy to the latest version to support it. I now have a fully feature web stack on top of gevent :) It ran some unit tests and it seems to be solid, but I still need to confirm that I'm getting the speed/concurrency from gevent and there aren't any blocking calls.
Anonymous said…
Have you considered DBSlayer? It's an HTTP/JSON interface to MySQL - you can set it up and then use the async HTTP client in an event-based framework to make your DB queries.
Adrian said…
Also: libpq for postgresql has an asynchronous interface, exposed by psycopg2. I don't think I've seen it actually used in anything though.
bsdemon said…
I have the same idea about using gevent with MySQL in async way. Moreover, I want to put in production system based on gevent + myconnpy + sqlalchemy. But I think I should rewrite some code from myconnpy in C/Cython — I speak about data marshaling... but I need to profile first.
Unknown said…
Concurrence (http://opensource.hyves.org/concurrence) contains a fully asynchronous driver for mysql.
jjinux said…
Henk, that's huge! Thanks! I'm so glad to see these ideas out in the wild ;)
Anonymous said…
Concurrence also integrates easily with SQLAlchemy because it can provide the normal python dbapi interface (e.g. all the async stuff is not visible for consumers of the api). There is a (currently undocumented class SqlAlchemyPoolAdapter in concurrence.database.ext that can be used as a drop in replacement for the normal sql alchemy pool stuff).
jjinux said…
Very nice :)
libor michalek said…
FYI: the python mysql connection we use in the slide/egroups coroutine environment is now on github. Likewise there's a project in there to implement some wire decode in C for performance. (The mysql wire protocol has almost no encode to speak of, SQL is sent directly to the server)

http://github.com/slideinc/gogreen/blob/master/gogreen/coromysql.py
jjinux said…
Libor, that's fantastic!
Jonas Tarnstrom said…
We've written a MySQL driver for Python with our without gevent in pure C/C++.

Performance is about 3-7 times faster than our Python written alternatives.

http://pushingtheweb.com/2011/05/a-faster-mysql-driver-for-python-and-gevent/

or

https://github.com/esnme/amysql

Would love feedback and benchmarks!
jjinux said…
Cool!

Popular posts from this blog

Ubuntu 20.04 on a 2015 15" MacBook Pro

I decided to give Ubuntu 20.04 a try on my 2015 15" MacBook Pro. I didn't actually install it; I just live booted from a USB thumb drive which was enough to try out everything I wanted. In summary, it's not perfect, and issues with my camera would prevent me from switching, but given the right hardware, I think it's a really viable option. The first thing I wanted to try was what would happen if I plugged in a non-HiDPI screen given that my laptop has a HiDPI screen. Without sub-pixel scaling, whatever scale rate I picked for one screen would apply to the other. However, once I turned on sub-pixel scaling, I was able to pick different scale rates for the internal and external displays. That looked ok. I tried plugging in and unplugging multiple times, and it didn't crash. I doubt it'd work with my Thunderbolt display at work, but it worked fine for my HDMI displays at home. I even plugged it into my TV, and it stuck to the 100% scaling I picked for the othe

ERNOS: Erlang Networked Operating System

I've been reading Dreaming in Code lately, and I really like it. If you're not a dreamer, you may safely skip the rest of this post ;) In Chapter 10, "Engineers and Artists", Alan Kay, John Backus, and Jaron Lanier really got me thinking. I've also been thinking a lot about Minix 3 , Erlang , and the original Lisp machine . The ideas are beginning to synthesize into something cohesive--more than just the sum of their parts. Now, I'm sure that many of these ideas have already been envisioned within Tunes.org , LLVM , Microsoft's Singularity project, or in some other place that I haven't managed to discover or fully read, but I'm going to blog them anyway. Rather than wax philosophical, let me just dump out some ideas: Start with Minix 3. It's a new microkernel, and it's meant for real use, unlike the original Minix. "This new OS is extremely small, with the part that runs in kernel mode under 4000 lines of executable code.&quo

Haskell or Erlang?

I've coded in both Erlang and Haskell. Erlang is practical, efficient, and useful. It's got a wonderful niche in the distributed world, and it has some real success stories such as CouchDB and jabber.org. Haskell is elegant and beautiful. It's been successful in various programming language competitions. I have some experience in both, but I'm thinking it's time to really commit to learning one of them on a professional level. They both have good books out now, and it's probably time I read one of those books cover to cover. My question is which? Back in 2000, Perl had established a real niche for systems administration, CGI, and text processing. The syntax wasn't exactly beautiful (unless you're into that sort of thing), but it was popular and mature. Python hadn't really become popular, nor did it really have a strong niche (at least as far as I could see). I went with Python because of its elegance, but since then, I've coded both p